47 lines
2.3 KiB
SQL
47 lines
2.3 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
-- Emailed, pre-provisioned invitations: a single-use capability token bound
|
||
|
|
-- to one email address, carrying the access decisions the admin made at send
|
||
|
|
-- time. Deliberately creates no users row until accepted, so a mistyped
|
||
|
|
-- address cannot squat a username or leave a ghost account.
|
||
|
|
-- Spec: docs/superpowers/specs/2026-07-27-invitations-and-onboarding-design.md
|
||
|
|
CREATE TABLE public.invitations (
|
||
|
|
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
|
||
|
|
email citext NOT NULL,
|
||
|
|
-- SHA-256 hex of the claim token. The raw token exists only in the sent
|
||
|
|
-- email; a database dump yields no usable links.
|
||
|
|
token_hash text NOT NULL UNIQUE,
|
||
|
|
-- Pre-bound access, applied verbatim at accept time.
|
||
|
|
role text NOT NULL DEFAULT 'user'
|
||
|
|
CONSTRAINT invitations_role_check CHECK (role IN ('user', 'admin')),
|
||
|
|
access_group_id bigint REFERENCES public.access_groups(id) ON DELETE SET NULL,
|
||
|
|
library_ids integer[], -- NULL = all libraries
|
||
|
|
create_profile boolean NOT NULL DEFAULT true,
|
||
|
|
show_tour boolean NOT NULL DEFAULT true,
|
||
|
|
note text NOT NULL DEFAULT '',
|
||
|
|
-- Lifecycle. Status (pending/accepted/expired/revoked) is derived from
|
||
|
|
-- these timestamps at read time; there is no status column to drift.
|
||
|
|
invited_by bigint NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
|
||
|
|
expires_at timestamptz NOT NULL,
|
||
|
|
accepted_at timestamptz,
|
||
|
|
accepted_user_id bigint REFERENCES public.users(id) ON DELETE SET NULL,
|
||
|
|
revoked_at timestamptz,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now()
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX invitations_email_idx ON public.invitations (email);
|
||
|
|
|
||
|
|
-- At most one live invitation per address: re-inviting supersedes rather
|
||
|
|
-- than accumulating parallel valid tokens for the same person. Expired rows
|
||
|
|
-- still match this predicate on purpose — creating a replacement revokes
|
||
|
|
-- the stale pending row first, keeping "resend supersedes" auditable.
|
||
|
|
CREATE UNIQUE INDEX invitations_one_pending_idx ON public.invitations (email)
|
||
|
|
WHERE accepted_at IS NULL AND revoked_at IS NULL;
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP TABLE public.invitations;
|
||
|
|
-- +goose StatementEnd
|