43 lines
1.2 KiB
PL/PgSQL
43 lines
1.2 KiB
PL/PgSQL
BEGIN;
|
|
|
|
CREATE TABLE project_accounts (
|
|
project text NOT NULL,
|
|
id uuid NOT NULL,
|
|
google_subject text NOT NULL,
|
|
email text,
|
|
PRIMARY KEY (project, id),
|
|
UNIQUE (project, google_subject)
|
|
);
|
|
|
|
CREATE TABLE oauth_attempts (
|
|
state_hash text PRIMARY KEY,
|
|
browser_hash text NOT NULL,
|
|
project text NOT NULL,
|
|
frontend_challenge text NOT NULL,
|
|
google_verifier text NOT NULL,
|
|
expires_at timestamptz NOT NULL
|
|
);
|
|
|
|
CREATE TABLE login_grants (
|
|
token_hash text PRIMARY KEY,
|
|
project text NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
frontend_challenge text NOT NULL,
|
|
expires_at timestamptz NOT NULL,
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts (project, id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE project_sessions (
|
|
token_hash text PRIMARY KEY,
|
|
project text NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
expires_at timestamptz NOT NULL,
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts (project, id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX oauth_attempts_expiry ON oauth_attempts (expires_at);
|
|
CREATE INDEX login_grants_expiry ON login_grants (expires_at);
|
|
CREATE INDEX project_sessions_expiry ON project_sessions (expires_at);
|
|
|
|
COMMIT;
|