spb/tools-spboucher-ai

1# Data model23Source 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.45## Entity–relationship overview6```7vendors 1─∞ tools ∞─∞ categories (tool_categories: is_primary, label)8                 ∞─∞ tags (tool_tags; tags.kind = tag | use_case)9                 ∞─∞ tools (tool_alternatives, symmetric at read time)10                 ∞─∞ sources (tool_sources: supports[], checked_at, note)11                 ∞─∞ collections (collection_items: position, blurb)12                 1─∞ tool_revisions (append-only snapshots)13                 1─∞ link_checks14                 1─1 owner_notes            ← PRIVATE15                 ∞─∞ projects (project_tools) ← PRIVATE16users 1─∞ sessions, webauthn_credentials; login_attempts; webauthn_challenges17audit_log (append-only), jobs, error_reports, search_events, settings18VIEWS: public_tools, public_collections, public_link_checks   ← the only tool data the public role can read19```2021## Public / private boundary22| Layer | Mechanism |23|---|---|24| 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. |25| 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`. |26| 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'`. |27| Build | No private data is read at build time; static params come from `public_tools`. |2829## Field dictionary — `tools`30| Field | Type | Notes |31|---|---|---|32| id | uuid | stable identity |33| slug | text unique | kebab-case, stable public URL `/tools/<slug>` |34| name, aliases[] | text | aliases include former names; searched at weight A |35| vendor_id | fk vendors | publisher/maintainer |36| 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 |37| logo_url, logo_attribution | text | optional; UI uses neutral monograms by default |38| product_type | enum | application, library, framework, api-service, platform, cli-tool, language, database, model, data-service, extension, specification, service |39| product_status | enum | active, maintenance, deprecated, discontinued, renamed, unknown |40| summary | text 40–220 chars | one sentence |41| description | markdown | original prose; deep 100–300 words |42| capabilities[], workflows[], advantages[], limitations[], prerequisites[], audiences[] | text[] | short noun phrases |43| comparison_notes | text | documented differences, no benchmark claims |44| operating_systems[], execution_modes[], interfaces[], deployment_models[] | enum[] | see `src/lib/content/schema.ts` |45| integrations[], data_formats[], languages[] | text[] | free text, displayed as chips |46| source_model | enum | open-source, proprietary, source-available, mixed |47| license_spdx, license_url | text | only when verified on a license page/repository |48| pricing_model[] | enum[] | free, freemium, paid, enterprise, usage-based, open-source, academic, unknown — **never prices** |49| pricing_checked_at | date | freshness policy 90 days |50| privacy_posture, data_residency | text | require a `policy_page` source |51| self_hostable | bool? | null = unknown |52| confidence | enum | high, medium, low |53| uncertainty_note | text | what could not be verified |54| discovery_method | text | editorial-research, owner-inventory, … |55| depth | enum | deep, standard |56| editorial_status | enum | draft, reviewed, published, deprecated |57| visibility | enum | public, private |58| quality_score | 0–100 | deterministic (`src/lib/quality.ts`); publish gate ≥ 40 |59| duplicate_of | fk tools | set by merge; excluded from public view |60| link_state | enum | unchecked, ok, redirected, broken |61| featured | bool | homepage selection |62| author, reviewed_at, reviewed_by, checked_at, published_at | | provenance |63| content_hash | sha256 | import idempotency |64| keywords | text | tags + use cases + categories + vendor, weight B in search |65| embedding (vector 1536), embedding_model, embedding_text | | optional semantic search |66| search_vector | tsvector generated | A: name/aliases · B: summary/keywords · C: capabilities · D: description (unaccented, english) |6768## Owner-private tables69`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`.7071## Provenance72`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.7374## Import / export75- 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.76- Export: `pnpm content:export` → `exports/tools-public.{json,csv}` (same schema, re-importable); admin UI export is role-scoped (`scope=public` vs `scope=all`).77- CSV columns: scalar fields as-is; lists joined with `|`; `categories` = slugs (first primary); `sources` = `url^type`; `vendor` = name.78