spb/tools-spboucher-ai
Public
1-- tools.spboucher.ai — initial schema. Applied by scripts/migrate.ts (tracks drizzle/*.sql in schema_migrations).2CREATE EXTENSION IF NOT EXISTS pgcrypto;3CREATE EXTENSION IF NOT EXISTS pg_trgm;4CREATE EXTENSION IF NOT EXISTS unaccent;5DO $$ BEGIN CREATE EXTENSION IF NOT EXISTS vector; EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'pgvector unavailable: %', SQLERRM; END $$;67-- immutable unaccent wrapper usable in generated columns / indexes8CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS $$ SELECT public.unaccent('public.unaccent', $1) $$;9-- array_to_string is only STABLE; wrap it for use in generated columns10CREATE OR REPLACE FUNCTION f_join(text[]) RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS $$ SELECT array_to_string($1, ' ') $$;1112-- ---------- identity ----------13CREATE TABLE users (14 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),15 email text NOT NULL UNIQUE,16 display_name text NOT NULL,17 password_hash text NOT NULL,18 role text NOT NULL DEFAULT 'owner' CHECK (role IN ('owner','editor')),19 totp_secret text,20 totp_enabled boolean NOT NULL DEFAULT false,21 must_change_password boolean NOT NULL DEFAULT false,22 created_at timestamptz NOT NULL DEFAULT now(),23 updated_at timestamptz NOT NULL DEFAULT now()24);25CREATE TABLE sessions (26 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),27 user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,28 token_hash text NOT NULL UNIQUE,29 csrf_secret text NOT NULL,30 mfa_passed boolean NOT NULL DEFAULT false,31 user_agent text, ip text,32 created_at timestamptz NOT NULL DEFAULT now(),33 last_seen_at timestamptz NOT NULL DEFAULT now(),34 rotated_at timestamptz NOT NULL DEFAULT now(),35 expires_at timestamptz NOT NULL,36 absolute_expires_at timestamptz NOT NULL,37 revoked_at timestamptz38);39CREATE INDEX sessions_user_idx ON sessions(user_id);40CREATE TABLE login_attempts (41 id bigserial PRIMARY KEY, ip text NOT NULL, email text, success boolean NOT NULL, created_at timestamptz NOT NULL DEFAULT now()42);43CREATE INDEX login_attempts_ip_idx ON login_attempts(ip, created_at);44CREATE INDEX login_attempts_email_idx ON login_attempts(email, created_at);45CREATE TABLE webauthn_credentials (46 id text PRIMARY KEY, user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,47 public_key text NOT NULL, counter bigint NOT NULL DEFAULT 0, transports text[] NOT NULL DEFAULT '{}',48 device_type text, backed_up boolean NOT NULL DEFAULT false, label text NOT NULL DEFAULT 'Passkey',49 created_at timestamptz NOT NULL DEFAULT now(), last_used_at timestamptz50);51CREATE TABLE webauthn_challenges (52 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), user_id uuid REFERENCES users(id) ON DELETE CASCADE,53 challenge text NOT NULL, kind text NOT NULL CHECK (kind IN ('register','auth')), created_at timestamptz NOT NULL DEFAULT now()54);5556-- ---------- taxonomy & reference ----------57CREATE TABLE vendors (58 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, name text NOT NULL, website_url text,59 description text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()60);61CREATE TABLE categories (62 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, name text NOT NULL, description text NOT NULL DEFAULT '',63 parent_id uuid REFERENCES categories(id) ON DELETE RESTRICT, sort_order int NOT NULL DEFAULT 0,64 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()65);66CREATE INDEX categories_parent_idx ON categories(parent_id);67CREATE TABLE tags (68 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL, name text NOT NULL,69 kind text NOT NULL DEFAULT 'tag' CHECK (kind IN ('tag','use_case')), UNIQUE (slug, kind)70);7172-- ---------- tools ----------73CREATE TABLE tools (74 id uuid PRIMARY KEY DEFAULT gen_random_uuid(),75 slug text NOT NULL UNIQUE,76 name text NOT NULL,77 aliases text[] NOT NULL DEFAULT '{}',78 vendor_id uuid REFERENCES vendors(id) ON DELETE SET NULL,79 homepage_url text NOT NULL,80 docs_url text, repo_url text, pricing_url text, license_url text, privacy_url text, security_url text,81 logo_url text, logo_attribution text,82 product_type text NOT NULL,83 product_status text NOT NULL DEFAULT 'active' CHECK (product_status IN ('active','maintenance','deprecated','discontinued','renamed','unknown')),84 summary text NOT NULL,85 description text NOT NULL,86 capabilities text[] NOT NULL DEFAULT '{}', workflows text[] NOT NULL DEFAULT '{}', advantages text[] NOT NULL DEFAULT '{}',87 limitations text[] NOT NULL DEFAULT '{}', prerequisites text[] NOT NULL DEFAULT '{}', audiences text[] NOT NULL DEFAULT '{}',88 comparison_notes text,89 operating_systems text[] NOT NULL DEFAULT '{}', execution_modes text[] NOT NULL DEFAULT '{}', interfaces text[] NOT NULL DEFAULT '{}',90 deployment_models text[] NOT NULL DEFAULT '{}', integrations text[] NOT NULL DEFAULT '{}', data_formats text[] NOT NULL DEFAULT '{}', languages text[] NOT NULL DEFAULT '{}',91 source_model text NOT NULL CHECK (source_model IN ('open-source','proprietary','source-available','mixed')),92 license_spdx text,93 pricing_model text[] NOT NULL DEFAULT '{}',94 pricing_checked_at date,95 privacy_posture text, data_residency text,96 self_hostable boolean,97 -- provenance / editorial98 confidence text NOT NULL DEFAULT 'medium' CHECK (confidence IN ('high','medium','low')),99 uncertainty_note text,100 discovery_method text NOT NULL DEFAULT 'editorial-research',101 depth text NOT NULL DEFAULT 'standard' CHECK (depth IN ('deep','standard')),102 editorial_status text NOT NULL DEFAULT 'draft' CHECK (editorial_status IN ('draft','reviewed','published','deprecated')),103 visibility text NOT NULL DEFAULT 'public' CHECK (visibility IN ('public','private')),104 quality_score int NOT NULL DEFAULT 0 CHECK (quality_score BETWEEN 0 AND 100),105 duplicate_of uuid REFERENCES tools(id) ON DELETE SET NULL,106 link_state text NOT NULL DEFAULT 'unchecked' CHECK (link_state IN ('unchecked','ok','redirected','broken')),107 featured boolean NOT NULL DEFAULT false,108 author text NOT NULL DEFAULT 'editorial',109 reviewed_at date, reviewed_by text, checked_at timestamptz, published_at timestamptz,110 content_hash text,111 embedding_text text,112 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(),113 search_vector tsvector GENERATED ALWAYS AS (114 setweight(to_tsvector('english', f_unaccent(coalesce(name,''))), 'A') ||115 setweight(to_tsvector('english', f_unaccent(f_join(aliases))), 'A') ||116 setweight(to_tsvector('english', f_unaccent(coalesce(summary,''))), 'B') ||117 setweight(to_tsvector('english', f_unaccent(f_join(capabilities))), 'C') ||118 setweight(to_tsvector('english', f_unaccent(coalesce(description,''))), 'D')119 ) STORED120);121CREATE INDEX tools_search_idx ON tools USING gin(search_vector);122CREATE INDEX tools_name_trgm_idx ON tools USING gin (f_unaccent(lower(name)) gin_trgm_ops);123CREATE INDEX tools_public_idx ON tools(visibility, editorial_status) WHERE visibility='public' AND editorial_status='published';124CREATE INDEX tools_vendor_idx ON tools(vendor_id);125CREATE INDEX tools_status_idx ON tools(editorial_status);126DO $$ BEGIN127 IF EXISTS (SELECT 1 FROM pg_extension WHERE extname='vector') THEN128 EXECUTE 'ALTER TABLE tools ADD COLUMN embedding vector(1536)';129 EXECUTE 'ALTER TABLE tools ADD COLUMN embedding_model text';130 EXECUTE 'CREATE INDEX tools_embedding_idx ON tools USING hnsw (embedding vector_cosine_ops)';131 ELSE132 EXECUTE 'ALTER TABLE tools ADD COLUMN embedding jsonb';133 EXECUTE 'ALTER TABLE tools ADD COLUMN embedding_model text';134 END IF;135END $$;136137CREATE TABLE tool_categories (138 tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, category_id uuid NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,139 is_primary boolean NOT NULL DEFAULT false, label text, PRIMARY KEY (tool_id, category_id)140);141CREATE INDEX tool_categories_cat_idx ON tool_categories(category_id);142CREATE UNIQUE INDEX tool_categories_one_primary ON tool_categories(tool_id) WHERE is_primary;143CREATE TABLE tool_tags (144 tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (tool_id, tag_id)145);146CREATE INDEX tool_tags_tag_idx ON tool_tags(tag_id);147CREATE TABLE tool_alternatives (148 tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, alternative_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE,149 note text, PRIMARY KEY (tool_id, alternative_id), CHECK (tool_id <> alternative_id)150);151CREATE TABLE sources (152 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), url text NOT NULL UNIQUE, canonical_url text, title text,153 source_type text NOT NULL, http_status int, final_url text, content_hash text, fetched_at timestamptz, fetch_error text,154 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()155);156CREATE TABLE tool_sources (157 tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, source_id uuid NOT NULL REFERENCES sources(id) ON DELETE CASCADE,158 supports text[] NOT NULL DEFAULT '{}', checked_at date NOT NULL, note text, PRIMARY KEY (tool_id, source_id)159);160CREATE TABLE collections (161 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, title text NOT NULL, description text NOT NULL,162 published boolean NOT NULL DEFAULT false, sort_order int NOT NULL DEFAULT 0,163 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()164);165CREATE TABLE collection_items (166 collection_id uuid NOT NULL REFERENCES collections(id) ON DELETE CASCADE, tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE,167 position int NOT NULL DEFAULT 0, blurb text, PRIMARY KEY (collection_id, tool_id)168);169170-- ---------- owner-private ----------171CREATE TABLE projects (172 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), slug text NOT NULL UNIQUE, name text NOT NULL, description text,173 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()174);175CREATE TABLE owner_notes (176 tool_id uuid PRIMARY KEY REFERENCES tools(id) ON DELETE CASCADE,177 use_state text NOT NULL DEFAULT 'candidate' CHECK (use_state IN ('active','testing','candidate','archived','rejected')),178 rating smallint CHECK (rating BETWEEN 1 AND 5), notes text, cluster_compat text NOT NULL DEFAULT 'unknown' CHECK (cluster_compat IN ('yes','partial','no','unknown')),179 priority text NOT NULL DEFAULT 'medium' CHECK (priority IN ('high','medium','low')), subscription_status text,180 deployment_urls jsonb NOT NULL DEFAULT '[]', custom_tags text[] NOT NULL DEFAULT '{}', review_at date,181 created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()182);183CREATE TABLE project_tools (184 project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE, tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE,185 role text, PRIMARY KEY (project_id, tool_id)186);187188-- ---------- editorial history, audit, jobs ----------189CREATE TABLE tool_revisions (190 id bigserial PRIMARY KEY, tool_id uuid NOT NULL REFERENCES tools(id) ON DELETE CASCADE, actor text NOT NULL, reason text,191 snapshot jsonb NOT NULL, created_at timestamptz NOT NULL DEFAULT now()192);193CREATE INDEX tool_revisions_tool_idx ON tool_revisions(tool_id, created_at DESC);194CREATE TABLE audit_log (195 id bigserial PRIMARY KEY, actor_id uuid, actor text NOT NULL, action text NOT NULL, entity_type text, entity_id text,196 details jsonb NOT NULL DEFAULT '{}', ip text, created_at timestamptz NOT NULL DEFAULT now()197);198CREATE INDEX audit_log_created_idx ON audit_log(created_at DESC);199CREATE OR REPLACE FUNCTION audit_log_immutable() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'audit_log is append-only'; END $$;200CREATE TRIGGER audit_log_no_update BEFORE UPDATE OR DELETE ON audit_log FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();201CREATE TRIGGER tool_revisions_no_update BEFORE UPDATE OR DELETE ON tool_revisions FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();202203CREATE TABLE jobs (204 id bigserial PRIMARY KEY, kind text NOT NULL, payload jsonb NOT NULL DEFAULT '{}', dedupe_key text UNIQUE,205 status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','running','succeeded','failed','dead','cancelled')),206 priority int NOT NULL DEFAULT 5, attempts int NOT NULL DEFAULT 0, max_attempts int NOT NULL DEFAULT 5,207 run_at timestamptz NOT NULL DEFAULT now(), locked_at timestamptz, locked_by text, started_at timestamptz, finished_at timestamptz,208 last_error text, result jsonb, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now()209);210CREATE INDEX jobs_ready_idx ON jobs(status, run_at, priority) WHERE status='queued';211CREATE TABLE link_checks (212 id bigserial PRIMARY KEY, tool_id uuid REFERENCES tools(id) ON DELETE CASCADE, field text NOT NULL, url text NOT NULL,213 ok boolean NOT NULL, status_code int, final_url text, redirected boolean NOT NULL DEFAULT false, error text, duration_ms int,214 checked_at timestamptz NOT NULL DEFAULT now()215);216CREATE INDEX link_checks_tool_idx ON link_checks(tool_id, checked_at DESC);217CREATE TABLE error_reports (218 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), tool_id uuid REFERENCES tools(id) ON DELETE SET NULL, tool_slug text, message text NOT NULL,219 contact text, ip_hash text, status text NOT NULL DEFAULT 'open' CHECK (status IN ('open','resolved','dismissed')), created_at timestamptz NOT NULL DEFAULT now()220);221CREATE TABLE search_events (222 id bigserial PRIMARY KEY, query text NOT NULL, result_count int NOT NULL, mode text NOT NULL, duration_ms int, created_at timestamptz NOT NULL DEFAULT now()223);224CREATE TABLE settings (225 key text PRIMARY KEY, value jsonb NOT NULL, updated_at timestamptz NOT NULL DEFAULT now()226);227228-- ---------- public boundary: read-only views exposing only publishable columns ----------229CREATE VIEW public_tools AS230 SELECT id, slug, name, aliases, vendor_id, homepage_url, docs_url, repo_url, pricing_url, license_url, privacy_url, security_url,231 logo_url, logo_attribution, product_type, product_status, summary, description, capabilities, workflows, advantages, limitations,232 prerequisites, audiences, comparison_notes, operating_systems, execution_modes, interfaces, deployment_models, integrations,233 data_formats, languages, source_model, license_spdx, pricing_model, pricing_checked_at, privacy_posture, data_residency,234 self_hostable, confidence, uncertainty_note, depth, quality_score, link_state, featured, reviewed_at, checked_at, published_at,235 updated_at, created_at, search_vector, embedding236 FROM tools WHERE visibility = 'public' AND editorial_status = 'published' AND duplicate_of IS NULL;237CREATE VIEW public_collections AS SELECT id, slug, title, description, sort_order, updated_at FROM collections WHERE published;238CREATE VIEW public_link_checks AS SELECT tool_id, field, url, ok, status_code, redirected, checked_at FROM link_checks;239