225 lines
9.6 KiB
PL/PgSQL
225 lines
9.6 KiB
PL/PgSQL
BEGIN;
|
|
|
|
-- Keep existing Google accounts and sessions valid during the expansion.
|
|
ALTER TABLE project_accounts ALTER COLUMN google_subject DROP NOT NULL;
|
|
ALTER TABLE project_accounts
|
|
ADD COLUMN username text,
|
|
ADD COLUMN display_name text NOT NULL DEFAULT '',
|
|
ADD COLUMN avatar_url text,
|
|
ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(),
|
|
ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();
|
|
CREATE UNIQUE INDEX project_account_username ON project_accounts(project, lower(username))
|
|
WHERE username IS NOT NULL;
|
|
|
|
ALTER TABLE oauth_attempts
|
|
ADD COLUMN provider text NOT NULL DEFAULT 'google' CHECK (provider IN ('google','slack')),
|
|
ADD COLUMN oidc_nonce text;
|
|
|
|
CREATE TABLE account_identities (
|
|
project text NOT NULL,
|
|
provider text NOT NULL CHECK (provider IN ('google', 'slack')),
|
|
subject text NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
PRIMARY KEY (project, provider, subject),
|
|
UNIQUE (project, provider, account_id),
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
|
|
);
|
|
INSERT INTO account_identities(project, provider, subject, account_id)
|
|
SELECT project, 'google', google_subject, id FROM project_accounts WHERE google_subject IS NOT NULL;
|
|
|
|
CREATE TABLE email_credentials (
|
|
project text NOT NULL,
|
|
email text NOT NULL CHECK (email = lower(email)),
|
|
account_id uuid NOT NULL,
|
|
password_hash text NOT NULL,
|
|
verified_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, email),
|
|
UNIQUE (project, account_id),
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
|
|
);
|
|
-- Registration is promoted to an account only after the single-use email check.
|
|
CREATE TABLE email_registrations (
|
|
project text NOT NULL,
|
|
email text NOT NULL,
|
|
password_hash text NOT NULL,
|
|
token_hash text NOT NULL UNIQUE,
|
|
expires_at timestamptz NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, email)
|
|
);
|
|
CREATE TABLE email_outbox (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
project text NOT NULL,
|
|
recipient text NOT NULL,
|
|
subject text NOT NULL,
|
|
body text NOT NULL,
|
|
attempts integer NOT NULL DEFAULT 0,
|
|
available_at timestamptz NOT NULL DEFAULT now(),
|
|
lease_until timestamptz,
|
|
lease_id uuid,
|
|
expires_at timestamptz NOT NULL,
|
|
sent_at timestamptz,
|
|
created_at timestamptz NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX email_outbox_pending ON email_outbox(available_at) WHERE sent_at IS NULL;
|
|
CREATE TABLE auth_rate_limits (
|
|
key_hash text PRIMARY KEY,
|
|
attempts integer NOT NULL CHECK (attempts > 0),
|
|
expires_at timestamptz NOT NULL
|
|
);
|
|
|
|
CREATE TABLE onboarding_responses (
|
|
project text NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
answers jsonb NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(answers) = 'object'),
|
|
result jsonb NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(result) = 'object'),
|
|
completed_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, account_id),
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE workspaces (
|
|
project text NOT NULL,
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL CHECK (length(trim(name)) BETWEEN 1 AND 160),
|
|
owner_id uuid NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, id),
|
|
FOREIGN KEY (project, owner_id) REFERENCES project_accounts
|
|
);
|
|
CREATE TABLE workspace_members (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
role text NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
|
|
joined_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, workspace_id, account_id),
|
|
FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE,
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX workspace_members_account ON workspace_members(project, account_id);
|
|
|
|
CREATE TABLE materials (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
kind text NOT NULL CHECK (kind IN ('text', 'article', 'image', 'board', 'card')),
|
|
title text NOT NULL CHECK (length(title) <= 300),
|
|
description text NOT NULL DEFAULT '' CHECK (length(description) <= 10000),
|
|
content jsonb NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(content) = 'object'),
|
|
version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
|
|
created_by uuid NOT NULL,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
modified_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, workspace_id, id),
|
|
FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE,
|
|
FOREIGN KEY (project, created_by) REFERENCES project_accounts
|
|
);
|
|
CREATE INDEX material_modified ON materials(project, workspace_id, modified_at DESC, id);
|
|
CREATE INDEX material_created ON materials(project, workspace_id, created_at DESC, id);
|
|
CREATE INDEX material_alphabetical ON materials(project, workspace_id, lower(title), id);
|
|
CREATE TABLE material_opens (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
material_id uuid NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
opened_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, workspace_id, material_id, account_id),
|
|
FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX material_opens_account ON material_opens(project, workspace_id, account_id, opened_at DESC);
|
|
|
|
CREATE TABLE tags (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
name text NOT NULL CHECK (length(trim(name)) BETWEEN 1 AND 80),
|
|
color text NOT NULL DEFAULT '',
|
|
PRIMARY KEY (project, workspace_id, id),
|
|
FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE
|
|
);
|
|
CREATE UNIQUE INDEX tag_name ON tags(project, workspace_id, lower(name));
|
|
CREATE TABLE material_tags (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
material_id uuid NOT NULL,
|
|
tag_id uuid NOT NULL,
|
|
PRIMARY KEY (project, workspace_id, material_id, tag_id),
|
|
FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
|
|
FOREIGN KEY (project, workspace_id, tag_id) REFERENCES tags ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX material_tags_filter ON material_tags(project, workspace_id, tag_id, material_id);
|
|
|
|
-- A board placement references a reusable material, independently of its content.
|
|
CREATE TABLE board_layouts (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
board_id uuid NOT NULL,
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
material_id uuid NOT NULL,
|
|
x double precision NOT NULL CHECK (x BETWEEN -1000000 AND 1000000),
|
|
y double precision NOT NULL CHECK (y BETWEEN -1000000 AND 1000000),
|
|
width double precision NOT NULL CHECK (width BETWEEN 1 AND 100000),
|
|
height double precision NOT NULL CHECK (height BETWEEN 1 AND 100000),
|
|
z_index integer NOT NULL DEFAULT 0,
|
|
version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
|
|
PRIMARY KEY (project, workspace_id, board_id, id),
|
|
FOREIGN KEY (project, workspace_id, board_id) REFERENCES materials ON DELETE CASCADE,
|
|
FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
|
|
CHECK (board_id <> material_id)
|
|
);
|
|
CREATE TABLE material_grants (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
material_id uuid NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
role text NOT NULL CHECK (role IN ('editor', 'viewer')),
|
|
PRIMARY KEY (project, workspace_id, material_id, account_id),
|
|
FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE share_links (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
id uuid NOT NULL DEFAULT gen_random_uuid(),
|
|
material_id uuid,
|
|
token_hash text NOT NULL UNIQUE,
|
|
role text NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
|
|
created_by uuid NOT NULL,
|
|
expires_at timestamptz NOT NULL,
|
|
revoked_at timestamptz,
|
|
max_uses integer CHECK (max_uses BETWEEN 1 AND 100000),
|
|
use_count integer NOT NULL DEFAULT 0 CHECK (use_count >= 0),
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, workspace_id, id),
|
|
FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE,
|
|
FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
|
|
FOREIGN KEY (project, created_by) REFERENCES project_accounts,
|
|
CHECK (material_id IS NULL OR role <> 'admin')
|
|
);
|
|
CREATE TABLE share_redemptions (
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
link_id uuid NOT NULL,
|
|
account_id uuid NOT NULL,
|
|
redeemed_at timestamptz NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (project, workspace_id, link_id, account_id),
|
|
FOREIGN KEY (project, workspace_id, link_id) REFERENCES share_links ON DELETE CASCADE,
|
|
FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
|
|
);
|
|
CREATE TABLE link_clicks (
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
|
project text NOT NULL,
|
|
workspace_id uuid NOT NULL,
|
|
link_id uuid NOT NULL,
|
|
clicked_at timestamptz NOT NULL DEFAULT now(),
|
|
-- Store the referring origin only, without URL paths, queries or user tokens.
|
|
referrer_origin text NOT NULL DEFAULT '',
|
|
FOREIGN KEY (project, workspace_id, link_id) REFERENCES share_links ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX link_clicks_summary ON link_clicks(project, workspace_id, link_id, clicked_at);
|
|
|
|
COMMIT;
|