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
text
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 readPublic / 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) andcontent/private/*.json(private) validated with zod (toolRecordSchema), categories fromcontent/categories.json, collections, owner relevance. Upsert by slug; relations replaced; revision written only whencontent_hashchanges;--publishpromotesreviewedpublic records;--forcerewrites rows without revisions. - Export:
pnpm content:export→exports/tools-public.{json,csv}(same schema, re-importable); admin UI export is role-scoped (scope=publicvsscope=all). - CSV columns: scalar fields as-is; lists joined with
|;categories= slugs (first primary);sources=url^type;vendor= name.