32 lines
1.1 KiB
SQL
32 lines
1.1 KiB
SQL
CREATE TABLE admin_security (
|
|
admin_id uuid PRIMARY KEY REFERENCES admins(id) ON DELETE CASCADE,
|
|
email text NOT NULL DEFAULT '',
|
|
login_notifications boolean NOT NULL DEFAULT false,
|
|
totp_secret text,
|
|
pending_secret text,
|
|
pending_expires timestamptz,
|
|
last_counter bigint NOT NULL DEFAULT -1,
|
|
recovery_hashes jsonb NOT NULL DEFAULT '[]'
|
|
);
|
|
CREATE TABLE login_challenges (
|
|
token text PRIMARY KEY,
|
|
admin_id uuid NOT NULL REFERENCES admins(id) ON DELETE CASCADE,
|
|
expires timestamptz NOT NULL,
|
|
attempts integer NOT NULL DEFAULT 0,
|
|
context jsonb NOT NULL
|
|
);
|
|
CREATE TABLE mail_config (
|
|
id integer PRIMARY KEY CHECK (id = 1),
|
|
data jsonb NOT NULL
|
|
);
|
|
CREATE TABLE login_events (
|
|
id bigserial PRIMARY KEY,
|
|
admin_id uuid NOT NULL REFERENCES admins(id) ON DELETE CASCADE,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
context jsonb NOT NULL,
|
|
factor text NOT NULL,
|
|
mail_status text NOT NULL DEFAULT 'disabled'
|
|
);
|
|
CREATE INDEX login_events_admin_time ON login_events(admin_id, created_at DESC);
|
|
CREATE INDEX login_challenges_expires ON login_challenges(expires);
|