Files

50 lines
1.5 KiB
SQL

-- +goose Up
CREATE TABLE accounts (
identity_id text PRIMARY KEY REFERENCES identities(id),
email text NOT NULL UNIQUE CHECK (email = lower(email)),
password_hash text NOT NULL,
recovery_hash text,
failed_attempts integer NOT NULL DEFAULT 0,
locked_until timestamptz
);
CREATE TABLE account_sessions (
token_hash text PRIMARY KEY,
identity_id text NOT NULL REFERENCES identities(id) ON DELETE CASCADE,
expires_at timestamptz NOT NULL,
issued_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX account_sessions_identity ON account_sessions(identity_id);
CREATE INDEX account_sessions_expiry ON account_sessions(expires_at);
CREATE TABLE authentication_settings (
singleton boolean PRIMARY KEY DEFAULT true CHECK (singleton),
public_url text NOT NULL,
oidc_issuer text NOT NULL DEFAULT '',
oidc_audience text NOT NULL DEFAULT '',
oidc_client_id text NOT NULL DEFAULT ''
);
CREATE TABLE passkeys (
credential_id text PRIMARY KEY,
identity_id text NOT NULL REFERENCES identities(id),
name text NOT NULL,
credential jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX passkeys_identity ON passkeys(identity_id);
CREATE TABLE passkey_ceremonies (
token_hash text PRIMARY KEY,
identity_id text NOT NULL,
registration boolean NOT NULL,
session_data jsonb NOT NULL,
expires_at timestamptz NOT NULL
);
CREATE INDEX passkey_ceremonies_expiry ON passkey_ceremonies(expires_at);
-- +goose Down
DROP TABLE passkey_ceremonies;
DROP TABLE passkeys;
DROP TABLE authentication_settings;
DROP TABLE account_sessions;
DROP TABLE accounts;