29 lines
1.3 KiB
SQL
29 lines
1.3 KiB
SQL
CREATE EXTENSION IF NOT EXISTS btree_gist;
|
|
|
|
CREATE TABLE token_bindings (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
token_hash VARCHAR(64) NOT NULL UNIQUE,
|
|
token_display VARCHAR(20) NOT NULL,
|
|
bound_ip TEXT NOT NULL,
|
|
binding_mode VARCHAR(16) NOT NULL DEFAULT 'single',
|
|
allowed_ips JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
first_used_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
last_used_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX idx_token_bindings_hash ON token_bindings(token_hash);
|
|
CREATE INDEX idx_token_bindings_ip ON token_bindings(bound_ip);
|
|
|
|
CREATE TABLE intercept_logs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
token_hash VARCHAR(64) NOT NULL,
|
|
token_display VARCHAR(20) NOT NULL,
|
|
bound_ip TEXT NOT NULL,
|
|
attempt_ip INET NOT NULL,
|
|
alerted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
intercepted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
CREATE INDEX idx_intercept_logs_hash ON intercept_logs(token_hash);
|
|
CREATE INDEX idx_intercept_logs_time ON intercept_logs(intercepted_at DESC);
|