# Data model

Source of truth: `drizzle/*.sql` (applied in order by `scripts/migrate.ts`, tracked in `schema_migrations` with checksums). `src/db/schema.ts` mirrors the SQL for typed queries. Content files in `content/` are the deterministic seed; `pnpm content:import` upserts them by slug.

## Entity–relationship overview
```
vendors 1─∞ tools ∞─∞ categories (tool_categories: is_primary, label)
                 ∞─∞ tags (tool_tags; tags.kind = tag | use_case)
                 ∞─∞ tools (tool_alternatives, symmetric at read time)
                 ∞─∞ sources (tool_sources: supports[], checked_at, note)
                 ∞─∞ collections (collection_items: position, blurb)
                 1─∞ tool_revisions (append-only snapshots)
                 1─∞ link_checks
                 1─1 owner_notes            ← PRIVATE
                 ∞─∞ projects (project_tools) ← PRIVATE
users 1─∞ sessions, webauthn_credentials; login_attempts; webauthn_challenges
audit_log (append-only), jobs, error_reports, search_events, settings
VIEWS: public_tools, public_collections, public_link_checks   ← the only tool data the public role can read
```

## Public / private boundary
| Layer | Mechanism |
|---|---|
| Database | Role `tools_reader` has SELECT only on `public_tools`, `public_collections`, `public_link_checks`, `vendors`, `categories`, `tags`, `tool_categories`, `tool_tags`, `tool_alternatives`, `sources`, `tool_sources`, `collection_items`, plus INSERT on `error_reports`, `search_events`. It cannot read `tools`, `owner_notes`, `projects`, `users`, `sessions`, `audit_log`, `jobs`. `statement_timeout = 5s`; no table-level write privileges except the two public insert-only tables. |
| View | `public_tools` filters `visibility='public' AND editorial_status='published' AND duplicate_of IS NULL` and omits `visibility`, `editorial_status`, `author`, `duplicate_of`, `discovery_method`, `reviewed_by`, `content_hash`, `embedding_text`, `embedding_model`. |
| Application | `src/lib/public/*` imports only `sqlPublic`; `src/lib/admin/*` and `/api/admin/*` require a session (`requireUser`) + CSRF for mutations. Owner notes additionally require `role='owner'`. |
| Build | No private data is read at build time; static params come from `public_tools`. |

## Field dictionary — `tools`
| Field | Type | Notes |
|---|---|---|
| id | uuid | stable identity |
| slug | text unique | kebab-case, stable public URL `/tools/<slug>` |
| name, aliases[] | text | aliases include former names; searched at weight A |
| vendor_id | fk vendors | publisher/maintainer |
| homepage_url (required), docs_url, repo_url, pricing_url, license_url, privacy_url, security_url | text | normalized (`normalizeUrl`): https preferred, lower-case host, trackers removed, no fragment |
| logo_url, logo_attribution | text | optional; UI uses neutral monograms by default |
| product_type | enum | application, library, framework, api-service, platform, cli-tool, language, database, model, data-service, extension, specification, service |
| product_status | enum | active, maintenance, deprecated, discontinued, renamed, unknown |
| summary | text 40–220 chars | one sentence |
| description | markdown | original prose; deep 100–300 words |
| capabilities[], workflows[], advantages[], limitations[], prerequisites[], audiences[] | text[] | short noun phrases |
| comparison_notes | text | documented differences, no benchmark claims |
| operating_systems[], execution_modes[], interfaces[], deployment_models[] | enum[] | see `src/lib/content/schema.ts` |
| integrations[], data_formats[], languages[] | text[] | free text, displayed as chips |
| source_model | enum | open-source, proprietary, source-available, mixed |
| license_spdx, license_url | text | only when verified on a license page/repository |
| pricing_model[] | enum[] | free, freemium, paid, enterprise, usage-based, open-source, academic, unknown — **never prices** |
| pricing_checked_at | date | freshness policy 90 days |
| privacy_posture, data_residency | text | require a `policy_page` source |
| self_hostable | bool? | null = unknown |
| confidence | enum | high, medium, low |
| uncertainty_note | text | what could not be verified |
| discovery_method | text | editorial-research, owner-inventory, … |
| depth | enum | deep, standard |
| editorial_status | enum | draft, reviewed, published, deprecated |
| visibility | enum | public, private |
| quality_score | 0–100 | deterministic (`src/lib/quality.ts`); publish gate ≥ 40 |
| duplicate_of | fk tools | set by merge; excluded from public view |
| link_state | enum | unchecked, ok, redirected, broken |
| featured | bool | homepage selection |
| author, reviewed_at, reviewed_by, checked_at, published_at | | provenance |
| content_hash | sha256 | import idempotency |
| keywords | text | tags + use cases + categories + vendor, weight B in search |
| embedding (vector 1536), embedding_model, embedding_text | | optional semantic search |
| search_vector | tsvector generated | A: name/aliases · B: summary/keywords · C: capabilities · D: description (unaccented, english) |

## Owner-private tables
`owner_notes(tool_id, use_state ∈ active|testing|candidate|archived|rejected, rating 1–5, notes, cluster_compat, priority, subscription_status, deployment_urls jsonb, custom_tags[], review_at)`; `projects(slug, name, description)`; `project_tools`.

## Provenance
`sources(url unique, source_type, title, canonical_url, http_status, final_url, content_hash, fetched_at)` × `tool_sources(supports[] = fields the source backs, checked_at, note)`. Link checks per field in `link_checks`. Editorial history in `tool_revisions` (full JSON snapshot per change) and `audit_log` (actor, action, entity, details, ip) — both protected by a trigger that raises on UPDATE/DELETE.

## Import / export
- Import: `content/tools/*.json` (public) and `content/private/*.json` (private) validated with zod (`toolRecordSchema`), categories from `content/categories.json`, collections, owner relevance. Upsert by slug; relations replaced; revision written only when `content_hash` changes; `--publish` promotes `reviewed` public records; `--force` rewrites rows without revisions.
- Export: `pnpm content:export` → `exports/tools-public.{json,csv}` (same schema, re-importable); admin UI export is role-scoped (`scope=public` vs `scope=all`).
- CSV columns: scalar fields as-is; lists joined with `|`; `categories` = slugs (first primary); `sources` = `url^type`; `vendor` = name.