Files

213 lines
10 KiB
SQL
Raw Permalink Normal View History

-- Users, login, birth charts, synastries, text analyses, credits, sharing.
-- Identifiers are UUIDv7 generated by the application (time-ordered).
-- --- users and login ----------------------------------------------------------
CREATE TABLE users (
id uuid PRIMARY KEY,
-- Always stored lower-case; unique.
email text NOT NULL,
email_verified_at timestamptz,
display_name text,
-- UI and analysis language (BCP 47 primary tag, e.g. 'hu', 'en').
locale text NOT NULL DEFAULT 'hu',
roles text[] NOT NULL DEFAULT ARRAY['user']::text[],
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
last_login_at timestamptz,
CONSTRAINT users_email_lower CHECK (email = lower(email)),
CONSTRAINT users_roles_valid CHECK (cardinality(roles) > 0 AND roles <@ ARRAY['admin', 'creator', 'user']::text[])
);
CREATE UNIQUE INDEX users_email_key ON users (email);
-- One row per login method of a user. 'password' keeps the Argon2id hash;
-- external providers keep their stable subject id (Google 'sub').
CREATE TABLE auth_identities (
id uuid PRIMARY KEY,
user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
provider text NOT NULL CHECK (provider IN ('password', 'google', 'facebook', 'apple')),
subject text NOT NULL,
password_hash text,
-- E-mail reported by the provider when the identity was linked.
email text,
created_at timestamptz NOT NULL DEFAULT now(),
last_used_at timestamptz,
UNIQUE (provider, subject),
UNIQUE (user_id, provider),
CONSTRAINT auth_identities_password CHECK ((provider = 'password') = (password_hash IS NOT NULL))
);
-- Opaque session tokens; only their SHA-256 hash is stored.
CREATE TABLE sessions (
id uuid PRIMARY KEY,
user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
token_hash bytea NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now(),
expires_at timestamptz NOT NULL,
user_agent text
);
CREATE INDEX sessions_user_idx ON sessions (user_id);
CREATE INDEX sessions_expiry_idx ON sessions (expires_at);
-- One-time tokens sent by e-mail (verification, password reset).
CREATE TABLE auth_tokens (
id uuid PRIMARY KEY,
user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
purpose text NOT NULL CHECK (purpose IN ('verify_email', 'reset_password')),
token_hash bytea NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now(),
expires_at timestamptz NOT NULL,
used_at timestamptz
);
CREATE INDEX auth_tokens_user_idx ON auth_tokens (user_id, purpose);
-- --- charts and synastries -------------------------------------------------------
-- A calculated natal chart. `birth_data` is the request sent to the
-- horoscope service, `result` its full response.
CREATE TABLE charts (
id uuid PRIMARY KEY,
owner_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
label text NOT NULL,
-- The owner's own chart (at most one per user).
is_primary boolean NOT NULL DEFAULT false,
birth_data jsonb NOT NULL,
result jsonb NOT NULL,
-- Hash of the resolved birth moment, place and options: same hash, same chart.
chart_hash text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX charts_one_primary ON charts (owner_id) WHERE is_primary;
CREATE INDEX charts_owner_idx ON charts (owner_id, created_at DESC);
-- Comparison of two charts. Visible to `owner_id` and to the owners of both
-- charts (an accepted invitation links two users this way).
CREATE TABLE synastries (
id uuid PRIMARY KEY,
owner_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
chart1_id uuid NOT NULL REFERENCES charts (id) ON DELETE CASCADE,
chart2_id uuid NOT NULL REFERENCES charts (id) ON DELETE CASCADE,
-- Synastry part of the horoscope service response (without the two charts).
result jsonb NOT NULL,
synastry_hash text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT synastries_distinct CHECK (chart1_id <> chart2_id),
UNIQUE (chart1_id, chart2_id)
);
CREATE INDEX synastries_chart2_idx ON synastries (chart2_id);
CREATE INDEX synastries_owner_idx ON synastries (owner_id, created_at DESC);
-- "How compatible are we?" invitation link.
CREATE TABLE synastry_invites (
id uuid PRIMARY KEY,
inviter_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
inviter_chart_id uuid NOT NULL REFERENCES charts (id) ON DELETE CASCADE,
token_hash bytea NOT NULL UNIQUE,
message text,
created_at timestamptz NOT NULL DEFAULT now(),
expires_at timestamptz NOT NULL,
accepted_at timestamptz,
accepted_by uuid REFERENCES users (id) ON DELETE SET NULL,
synastry_id uuid REFERENCES synastries (id) ON DELETE SET NULL
);
CREATE INDEX synastry_invites_inviter_idx ON synastry_invites (inviter_id, created_at DESC);
-- --- text analyses (LLM) ---------------------------------------------------------
CREATE TABLE analyses (
id uuid PRIMARY KEY,
kind text NOT NULL CHECK (kind IN ('natal', 'synastry')),
chart_id uuid REFERENCES charts (id) ON DELETE CASCADE,
synastry_id uuid REFERENCES synastries (id) ON DELETE CASCADE,
requested_by uuid REFERENCES users (id) ON DELETE SET NULL,
language text NOT NULL,
model text NOT NULL,
prompt_version text NOT NULL,
-- sha256(kind | chart/synastry hash | prompt version | model | language):
-- a finished analysis with the same key is reused instead of a new LLM call.
cache_key text NOT NULL,
status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'running', 'done', 'failed')),
-- Structured result: {"summary", "strengths": [], "challenges": [], "body"}.
content jsonb,
error text,
input_tokens integer,
output_tokens integer,
credits_charged integer NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
started_at timestamptz,
completed_at timestamptz,
CONSTRAINT analyses_target CHECK (
(kind = 'natal' AND chart_id IS NOT NULL AND synastry_id IS NULL)
OR (kind = 'synastry' AND synastry_id IS NOT NULL AND chart_id IS NULL)
),
CONSTRAINT analyses_done_has_content CHECK (status <> 'done' OR content IS NOT NULL)
);
CREATE INDEX analyses_chart_idx ON analyses (chart_id, created_at DESC) WHERE chart_id IS NOT NULL;
CREATE INDEX analyses_synastry_idx ON analyses (synastry_id, created_at DESC) WHERE synastry_id IS NOT NULL;
CREATE INDEX analyses_cache_idx ON analyses (cache_key) WHERE status = 'done';
-- Work queue of the worker (SELECT ... FOR UPDATE SKIP LOCKED).
CREATE INDEX analyses_pending_idx ON analyses (created_at) WHERE status = 'pending';
-- --- credits -----------------------------------------------------------------------
-- Append-only ledger; the balance is SUM(delta).
CREATE TABLE credit_ledger (
id uuid PRIMARY KEY,
user_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
delta integer NOT NULL CHECK (delta <> 0),
reason text NOT NULL CHECK (reason IN ('monthly_grant', 'analysis', 'chat', 'refund', 'admin_adjust', 'purchase')),
-- 'YYYY-MM' of a monthly grant.
period text,
analysis_id uuid REFERENCES analyses (id) ON DELETE SET NULL,
note text,
created_by uuid REFERENCES users (id) ON DELETE SET NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT credit_ledger_grant_period CHECK ((reason = 'monthly_grant') = (period IS NOT NULL))
);
CREATE UNIQUE INDEX credit_ledger_monthly_grant_once ON credit_ledger (user_id, period) WHERE reason = 'monthly_grant';
CREATE INDEX credit_ledger_user_idx ON credit_ledger (user_id, created_at DESC);
-- --- public sharing -----------------------------------------------------------------
-- A public link (and OG card) to a chart, synastry or analysis.
CREATE TABLE shares (
id uuid PRIMARY KEY,
owner_id uuid NOT NULL REFERENCES users (id) ON DELETE CASCADE,
-- Unguessable public identifier used in the URL.
slug text NOT NULL UNIQUE,
kind text NOT NULL CHECK (kind IN ('chart', 'synastry', 'analysis')),
chart_id uuid REFERENCES charts (id) ON DELETE CASCADE,
synastry_id uuid REFERENCES synastries (id) ON DELETE CASCADE,
analysis_id uuid REFERENCES analyses (id) ON DELETE CASCADE,
title text,
-- Birth date, time and place are hidden unless the owner allows them.
show_birth_data boolean NOT NULL DEFAULT false,
view_count bigint NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
revoked_at timestamptz,
CONSTRAINT shares_target CHECK (
(kind = 'chart' AND chart_id IS NOT NULL AND synastry_id IS NULL AND analysis_id IS NULL)
OR (kind = 'synastry' AND synastry_id IS NOT NULL AND chart_id IS NULL AND analysis_id IS NULL)
OR (kind = 'analysis' AND analysis_id IS NOT NULL AND chart_id IS NULL AND synastry_id IS NULL)
)
);
CREATE INDEX shares_owner_idx ON shares (owner_id, created_at DESC);
-- --- outgoing e-mail -----------------------------------------------------------------
-- Written by the backend, sent by the worker.
CREATE TABLE email_outbox (
id uuid PRIMARY KEY,
user_id uuid REFERENCES users (id) ON DELETE CASCADE,
to_email text NOT NULL,
template text NOT NULL,
locale text NOT NULL,
payload jsonb NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
sent_at timestamptz,
attempts integer NOT NULL DEFAULT 0,
last_error text
);
CREATE INDEX email_outbox_unsent_idx ON email_outbox (created_at) WHERE sent_at IS NULL;