spb/wp-spboucher-ai
Public
1-- wp.spboucher.ai — initial schema (PostgreSQL 17, pgvector, pg_trgm, unaccent, pgcrypto)2CREATE EXTENSION IF NOT EXISTS vector;3CREATE EXTENSION IF NOT EXISTS pg_trgm;4CREATE EXTENSION IF NOT EXISTS unaccent;5CREATE EXTENSION IF NOT EXISTS pgcrypto;67-- Immutable helpers so that generated tsvector columns can use them.8CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT9 AS $$ SELECT public.unaccent('public.unaccent', $1) $$;10CREATE OR REPLACE FUNCTION jsonb_strings(j jsonb) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$11 WITH RECURSIVE walk(v) AS (12 SELECT j13 UNION ALL14 SELECT CASE WHEN jsonb_typeof(v) = 'array' THEN e.value WHEN jsonb_typeof(v) = 'object' THEN o.value END15 FROM walk LEFT JOIN LATERAL jsonb_array_elements(CASE WHEN jsonb_typeof(v) = 'array' THEN v ELSE '[]'::jsonb END) e ON true16 LEFT JOIN LATERAL jsonb_each(CASE WHEN jsonb_typeof(v) = 'object' THEN v ELSE '{}'::jsonb END) o ON true17 WHERE jsonb_typeof(v) IN ('array', 'object')18 )19 SELECT coalesce(string_agg(v #>> '{}', ' '), '') FROM walk WHERE jsonb_typeof(v) = 'string'20$$;21CREATE OR REPLACE FUNCTION arr_text(a text[]) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$ SELECT coalesce(array_to_string(a, ' '), '') $$;2223CREATE TABLE settings (key text PRIMARY KEY, value jsonb NOT NULL, updated_at timestamptz NOT NULL DEFAULT now());2425CREATE TABLE users (26 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),27 email text NOT NULL UNIQUE,28 display_name text NOT NULL,29 password_hash text NOT NULL,30 role text NOT NULL DEFAULT 'owner' CHECK (role IN ('owner', 'editor')),31 totp_secret text,32 totp_enabled boolean NOT NULL DEFAULT false,33 must_change_password boolean NOT NULL DEFAULT true,34 created_at timestamptz NOT NULL DEFAULT now(),35 last_login_at timestamptz36);37CREATE TABLE sessions (38 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),39 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,40 token_hash text NOT NULL UNIQUE,41 csrf_secret text NOT NULL,42 user_agent text, ip text,43 created_at timestamptz NOT NULL DEFAULT now(),44 last_seen_at timestamptz NOT NULL DEFAULT now(),45 expires_at timestamptz NOT NULL,46 absolute_expires_at timestamptz NOT NULL,47 rotated_at timestamptz NOT NULL DEFAULT now(),48 revoked_at timestamptz,49 mfa_passed boolean NOT NULL DEFAULT false50);51CREATE INDEX sessions_user_idx ON sessions(user_id);52CREATE TABLE login_attempts (id bigserial PRIMARY KEY, ip text NOT NULL, email text, success boolean NOT NULL, created_at timestamptz NOT NULL DEFAULT now());53CREATE INDEX login_attempts_ip_idx ON login_attempts(ip, created_at);54CREATE INDEX login_attempts_email_idx ON login_attempts(email, created_at);5556CREATE TABLE audit_log (57 id bigserial PRIMARY KEY,58 actor_id uuid, actor_label text NOT NULL,59 action text NOT NULL, entity_type text NOT NULL, entity_id text,60 before jsonb, after jsonb, ip text,61 at timestamptz NOT NULL DEFAULT now()62);63CREATE INDEX audit_log_at_idx ON audit_log(at DESC);64CREATE INDEX audit_log_entity_idx ON audit_log(entity_type, entity_id);6566CREATE TABLE series (67 id serial PRIMARY KEY, slug text NOT NULL UNIQUE, code text NOT NULL UNIQUE,68 name_en text NOT NULL, name_fr text NOT NULL, description_en text, description_fr text,69 issn text, sort int NOT NULL DEFAULT 0, is_active boolean NOT NULL DEFAULT true70);71CREATE TABLE authors (72 id serial PRIMARY KEY, slug text NOT NULL UNIQUE,73 given_name text NOT NULL, family_name text NOT NULL, display_name text NOT NULL,74 orcid text, email text, website text, github text, google_scholar text, ssrn_url text, repec text,75 affiliation_en text, affiliation_fr text, department_en text, department_fr text,76 bio_en text, bio_fr text, is_primary boolean NOT NULL DEFAULT false, sort int NOT NULL DEFAULT 0,77 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()78);79CREATE TABLE topics (80 id serial PRIMARY KEY, slug text NOT NULL UNIQUE,81 name_en text NOT NULL, name_fr text NOT NULL, description_en text, description_fr text,82 kind text NOT NULL DEFAULT 'topic' CHECK (kind IN ('domain', 'topic')),83 parent_id int REFERENCES topics(id) ON DELETE SET NULL,84 accent text, sort int NOT NULL DEFAULT 0, is_active boolean NOT NULL DEFAULT true85);86CREATE TABLE methods (87 id serial PRIMARY KEY, slug text NOT NULL UNIQUE,88 name_en text NOT NULL, name_fr text NOT NULL, description_en text, description_fr text,89 category text NOT NULL DEFAULT 'general', sort int NOT NULL DEFAULT 090);91CREATE TABLE datasets (92 id serial PRIMARY KEY, slug text NOT NULL UNIQUE,93 name_en text NOT NULL, name_fr text NOT NULL, provider text, coverage text, frequency text,94 access_conditions text, license text, url text, description_en text, description_fr text,95 is_public boolean NOT NULL DEFAULT false, sort int NOT NULL DEFAULT 096);9798CREATE TABLE files (99 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),100 kind text NOT NULL CHECK (kind IN ('manuscript', 'supplement', 'preview', 'other')),101 storage_key text NOT NULL UNIQUE,102 original_name text, mime text NOT NULL, bytes bigint NOT NULL, sha256 text NOT NULL,103 pages int, pdf_meta jsonb, integrity_ok boolean, text_status text NOT NULL DEFAULT 'pending' CHECK (text_status IN ('pending', 'done', 'failed', 'skipped')),104 created_at timestamptz NOT NULL DEFAULT now()105);106CREATE INDEX files_sha_idx ON files(sha256);107108CREATE TABLE papers (109 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),110 series_id int NOT NULL REFERENCES series(id),111 number int NOT NULL,112 identifier text NOT NULL UNIQUE, -- permanent public identifier, e.g. WP38, FIRC0002113 slug text NOT NULL UNIQUE, -- URL slug, e.g. wp38114 internal_code text, -- internal project number / repository name (not proof of release)115 title text NOT NULL, subtitle text, title_fr text, subtitle_fr text,116 abstract jsonb NOT NULL DEFAULT '[]'::jsonb, -- array of paragraphs117 abstract_fr jsonb, structured_abstract jsonb,118 language text NOT NULL DEFAULT 'en',119 research_question text, research_question_fr text,120 hero_headline text, hero_subheadline text,121 contributions jsonb NOT NULL DEFAULT '[]'::jsonb, -- [{kind?, title, description}]122 methodology jsonb NOT NULL DEFAULT '[]'::jsonb, -- [{title, description}] (verified fields only)123 data_sections jsonb NOT NULL DEFAULT '[]'::jsonb, -- [{title, description}]124 findings jsonb NOT NULL DEFAULT '[]'::jsonb, -- [{value, label}]125 reproducibility jsonb NOT NULL DEFAULT '[]'::jsonb, -- [string]126 artifacts jsonb NOT NULL DEFAULT '[]'::jsonb, -- [{kind, label, url}]127 notice text, notice_fr text,128 keywords text[] NOT NULL DEFAULT '{}',129 jel text[] NOT NULL DEFAULT '{}',130 status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'reserved', 'published', 'withdrawn', 'superseded')),131 publication_status text NOT NULL DEFAULT 'working_paper' CHECK (publication_status IN ('working_paper', 'under_review', 'forthcoming', 'published')),132 journal_ref text, doi text, ssrn_id text, external_links jsonb NOT NULL DEFAULT '[]'::jsonb,133 year int, first_released_on date, first_released_precision text NOT NULL DEFAULT 'month' CHECK (first_released_precision IN ('day', 'month', 'year')),134 last_revised_on date, last_revised_precision text NOT NULL DEFAULT 'month' CHECK (last_revised_precision IN ('day', 'month', 'year')),135 current_version_id uuid,136 featured boolean NOT NULL DEFAULT false, featured_rank int,137 primary_domain_id int REFERENCES topics(id) ON DELETE SET NULL,138 view_count bigint NOT NULL DEFAULT 0, download_count bigint NOT NULL DEFAULT 0,139 embedding vector(1536), lexvec vector(512), embedding_model text,140 fts tsvector GENERATED ALWAYS AS (141 setweight(to_tsvector('english', f_unaccent(coalesce(title, ''))), 'A') ||142 setweight(to_tsvector('english', f_unaccent(arr_text(keywords))), 'A') ||143 setweight(to_tsvector('english', f_unaccent(coalesce(subtitle, ''))), 'B') ||144 setweight(to_tsvector('english', f_unaccent(coalesce(research_question, ''))), 'B') ||145 setweight(to_tsvector('english', f_unaccent(jsonb_strings(abstract))), 'B') ||146 setweight(to_tsvector('english', f_unaccent(jsonb_strings(findings))), 'C') ||147 setweight(to_tsvector('english', f_unaccent(jsonb_strings(contributions))), 'C') ||148 setweight(to_tsvector('english', f_unaccent(jsonb_strings(methodology))), 'C') ||149 setweight(to_tsvector('english', f_unaccent(jsonb_strings(data_sections))), 'C') ||150 setweight(to_tsvector('english', f_unaccent(coalesce(identifier, '') || ' ' || arr_text(jel))), 'D')151 ) STORED,152 search_blob text GENERATED ALWAYS AS (f_unaccent(lower(coalesce(title, '') || ' ' || coalesce(subtitle, '') || ' ' || arr_text(keywords)))) STORED,153 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), published_at timestamptz,154 UNIQUE (series_id, number)155);156CREATE INDEX papers_fts_idx ON papers USING gin(fts);157CREATE INDEX papers_blob_trgm_idx ON papers USING gin(search_blob gin_trgm_ops);158CREATE INDEX papers_status_idx ON papers(status, year DESC, number DESC);159CREATE INDEX papers_keywords_idx ON papers USING gin(keywords);160161CREATE TABLE paper_authors (162 paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,163 author_id int NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,164 position int NOT NULL DEFAULT 1, affiliation_note text,165 PRIMARY KEY (paper_id, author_id)166);167CREATE INDEX paper_authors_author_idx ON paper_authors(author_id);168CREATE TABLE paper_topics (169 paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,170 topic_id int NOT NULL REFERENCES topics(id) ON DELETE CASCADE,171 assigned_by text NOT NULL DEFAULT 'editor' CHECK (assigned_by IN ('editor', 'author', 'inferred')),172 PRIMARY KEY (paper_id, topic_id)173);174CREATE INDEX paper_topics_topic_idx ON paper_topics(topic_id);175CREATE TABLE paper_methods (176 paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,177 method_id int NOT NULL REFERENCES methods(id) ON DELETE CASCADE,178 note text, assigned_by text NOT NULL DEFAULT 'editor' CHECK (assigned_by IN ('editor', 'author', 'inferred')),179 PRIMARY KEY (paper_id, method_id)180);181CREATE INDEX paper_methods_method_idx ON paper_methods(method_id);182CREATE TABLE paper_datasets (183 paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,184 dataset_id int NOT NULL REFERENCES datasets(id) ON DELETE CASCADE,185 role text, note text, assigned_by text NOT NULL DEFAULT 'editor' CHECK (assigned_by IN ('editor', 'author', 'inferred')),186 PRIMARY KEY (paper_id, dataset_id)187);188CREATE INDEX paper_datasets_dataset_idx ON paper_datasets(dataset_id);189190CREATE TABLE paper_versions (191 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),192 paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,193 version_no int NOT NULL, label text NOT NULL,194 released_on date, released_precision text NOT NULL DEFAULT 'month' CHECK (released_precision IN ('day', 'month', 'year')),195 description text, description_fr text,196 file_id uuid REFERENCES files(id) ON DELETE SET NULL,197 pages int, sha256 text, bytes bigint,198 is_current boolean NOT NULL DEFAULT false,199 withdrawn boolean NOT NULL DEFAULT false, withdrawn_reason text,200 created_at timestamptz NOT NULL DEFAULT now(),201 UNIQUE (paper_id, version_no)202);203CREATE INDEX paper_versions_paper_idx ON paper_versions(paper_id, version_no DESC);204ALTER TABLE papers ADD CONSTRAINT papers_current_version_fk FOREIGN KEY (current_version_id) REFERENCES paper_versions(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;205206CREATE TABLE paper_chunks (207 id bigserial PRIMARY KEY,208 paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,209 version_id uuid REFERENCES paper_versions(id) ON DELETE CASCADE,210 chunk_index int NOT NULL, page_from int, page_to int,211 text text NOT NULL,212 fts tsvector GENERATED ALWAYS AS (to_tsvector('english', f_unaccent(text))) STORED,213 embedding vector(1536)214);215CREATE INDEX paper_chunks_paper_idx ON paper_chunks(paper_id, chunk_index);216CREATE INDEX paper_chunks_fts_idx ON paper_chunks USING gin(fts);217218CREATE TABLE paper_relations (219 id bigserial PRIMARY KEY,220 from_paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,221 to_paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,222 kind text NOT NULL CHECK (kind IN ('topic', 'method', 'dataset', 'author', 'semantic', 'lexical', 'curated')),223 weight real NOT NULL DEFAULT 1, explanation text, explanation_fr text,224 inferred boolean NOT NULL DEFAULT false,225 created_at timestamptz NOT NULL DEFAULT now(),226 UNIQUE (from_paper_id, to_paper_id, kind), CHECK (from_paper_id <> to_paper_id)227);228CREATE INDEX paper_relations_from_idx ON paper_relations(from_paper_id, weight DESC);229230CREATE TABLE redirects (from_path text PRIMARY KEY, to_path text NOT NULL, status int NOT NULL DEFAULT 301, note text, created_at timestamptz NOT NULL DEFAULT now());231232CREATE TABLE events (233 id bigserial PRIMARY KEY,234 paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE,235 version_id uuid, kind text NOT NULL CHECK (kind IN ('view', 'download')),236 occurred_at timestamptz NOT NULL DEFAULT now(), day date NOT NULL DEFAULT current_date,237 visitor_hash text NOT NULL, referrer_host text, locale text, is_bot boolean NOT NULL DEFAULT false238);239CREATE INDEX events_day_idx ON events(day, paper_id);240CREATE UNIQUE INDEX events_dedupe_idx ON events(paper_id, kind, day, visitor_hash);241CREATE TABLE paper_stats_daily (paper_id uuid NOT NULL REFERENCES papers(id) ON DELETE CASCADE, day date NOT NULL, views int NOT NULL DEFAULT 0, downloads int NOT NULL DEFAULT 0, PRIMARY KEY (paper_id, day));242243CREATE TABLE search_log (id bigserial PRIMARY KEY, query text NOT NULL, mode text NOT NULL, results int NOT NULL, locale text, took_ms int, at timestamptz NOT NULL DEFAULT now());244CREATE INDEX search_log_at_idx ON search_log(at DESC);245246CREATE TABLE ingest_candidates (247 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),248 source_path text NOT NULL, file_id uuid REFERENCES files(id) ON DELETE SET NULL, sha256 text NOT NULL,249 proposal jsonb NOT NULL DEFAULT '{}'::jsonb, flags jsonb NOT NULL DEFAULT '[]'::jsonb, duplicates jsonb NOT NULL DEFAULT '[]'::jsonb,250 status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'processing', 'ready', 'approved', 'rejected', 'merged')),251 paper_id uuid REFERENCES papers(id) ON DELETE SET NULL,252 reviewed_by uuid, reviewed_at timestamptz, review_note text,253 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()254);255CREATE UNIQUE INDEX ingest_candidates_sha_idx ON ingest_candidates(sha256) WHERE status IN ('queued', 'processing', 'ready');256257CREATE TABLE jobs (258 id bigserial PRIMARY KEY, kind text NOT NULL, payload jsonb NOT NULL DEFAULT '{}'::jsonb,259 status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'done', 'failed')),260 attempts int NOT NULL DEFAULT 0, max_attempts int NOT NULL DEFAULT 3,261 run_after timestamptz NOT NULL DEFAULT now(), locked_at timestamptz, last_error text,262 created_at timestamptz NOT NULL DEFAULT now(), finished_at timestamptz263);264CREATE INDEX jobs_queue_idx ON jobs(status, run_after) WHERE status IN ('queued', 'running');265266CREATE TABLE backups (id bigserial PRIMARY KEY, kind text NOT NULL, path text NOT NULL, bytes bigint, sha256 text, ok boolean NOT NULL, log text, remote_ok boolean, created_at timestamptz NOT NULL DEFAULT now());267268-- Public views: everything the read-only role may see.269CREATE VIEW public_papers AS SELECT * FROM papers WHERE status IN ('published', 'withdrawn', 'superseded');270CREATE VIEW public_paper_versions AS SELECT v.* FROM paper_versions v JOIN papers p ON p.id = v.paper_id WHERE p.status IN ('published', 'withdrawn', 'superseded');271CREATE VIEW public_paper_chunks AS SELECT c.id, c.paper_id, c.version_id, c.chunk_index, c.page_from, c.page_to, c.text, c.fts, c.embedding FROM paper_chunks c JOIN papers p ON p.id = c.paper_id WHERE p.status = 'published';272CREATE VIEW public_paper_relations AS SELECT r.* FROM paper_relations r JOIN papers a ON a.id = r.from_paper_id JOIN papers b ON b.id = r.to_paper_id WHERE a.status = 'published' AND b.status = 'published';273CREATE VIEW public_settings AS SELECT key, value FROM settings WHERE key NOT LIKE 'private.%';274CREATE VIEW public_files AS SELECT f.id, f.kind, f.storage_key, f.original_name, f.mime, f.bytes, f.sha256, f.pages, f.integrity_ok FROM files f WHERE EXISTS (SELECT 1 FROM paper_versions v JOIN papers p ON p.id = v.paper_id WHERE v.file_id = f.id AND p.status IN ('published', 'withdrawn', 'superseded') AND NOT v.withdrawn) OR f.kind = 'preview';275276CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END $$;277CREATE TRIGGER papers_touch BEFORE UPDATE ON papers FOR EACH ROW EXECUTE FUNCTION touch_updated_at();278CREATE TRIGGER authors_touch BEFORE UPDATE ON authors FOR EACH ROW EXECUTE FUNCTION touch_updated_at();279CREATE TRIGGER ingest_touch BEFORE UPDATE ON ingest_candidates FOR EACH ROW EXECUTE FUNCTION touch_updated_at();280