spb/tools-spboucher-ai

1# Ingestion and maintenance pipeline23## Stages4```5discover → fetch → extract → normalize → entity-resolve → evidence-check → score → editorial-review → publish6```7| Stage | Implementation | Durable state |8|---|---|---|9| discover | Editorial research (batch briefs in `docs/_agent-brief.md` style), admin "New record", bulk import (JSON/CSV) | `content/`, `tools.editorial_status=draft/reviewed` |10| fetch | `safeFetch` (`src/lib/url.ts`): SSRF guard, manual redirects, caps | `sources.http_status/final_url/fetched_at` |11| extract | `extractHtmlMeta` (title, canonical, description) ; optional AI suggestion with evidence quotes (`/api/admin/ai/suggest`, human-reviewed) | not persisted until the editor saves |12| normalize | `normalizeUrl`, controlled vocabularies (zod), `keywords`, `embedding_text` | `tools` |13| entity-resolve | slug as identity; `aliases`; duplicate candidates by trigram/homepage/alias (`/admin/duplicates`), merge keeps target and redirects relations | `tools.duplicate_of` |14| evidence-check | `link_check` job per record (every URL field); `source_rescan` hashes the official page; validator rules (sources per depth, policy sources for claims) | `link_checks`, `sources.content_hash`, flags in `settings` |15| score | `qualityScore` 0–100 (completeness × evidence × freshness) | `tools.quality_score` |16| editorial-review | Admin → Review queue: reviewed records, automated flags, public error reports | `audit_log`, `tool_revisions` |17| publish | `publish` action (quality ≥ 40, no broken links) or `content:import --publish` | `published_at`, public view |1819## Jobs (PostgreSQL queue, `src/lib/jobs`)20`jobs(kind, payload, dedupe_key, status queued|running|succeeded|failed|dead|cancelled, attempts/max_attempts, run_at, locked_by)`. Claim with `FOR UPDATE SKIP LOCKED`; failures requeue with exponential backoff (30 s · 2^attempt, cap 6 h); after `max_attempts` → `dead` (retry from admin); stale `running` jobs (lease 20 min) are requeued.2122| Kind | Trigger | Effect |23|---|---|---|24| link_check {toolId} | save, publish, sweep, admin | checks homepage/docs/repo/pricing/license/privacy/security; 401/403/405/429 = unverifiable (ok, soft); off-site redirect → `redirected`; 404/5xx/timeout → `broken` |25| link_sweep | daily 03:10 | fan-out link_check for records not checked in 6 days, 3 s apart |26| source_rescan {toolId} | admin | fetch official page, compare content hash, flag `review:<slug>` / canonical move |27| freshness_sweep | daily 03:40 | flags `stale:<slug>` when pricing > 90 d, review > 365 d, oldest source > 180 d |28| embed_tool / embed_sweep | save / daily 04:00 | embeddings when `AI_PROVIDER` configured (budget-guarded) |29| backup | daily 04:20 | encrypted pg_dump, retention 14, optional rsync |3031Schedule is wall-clock America/Toronto inside the worker (`src/worker/index.ts`), deduped per day. Manual: Admin → Jobs, or `pnpm links:check [--sample N]`.3233## Freshness policy by field34| Field | Re-check | Where surfaced |35|---|---|---|36| URLs | weekly | link_state badge, Admin → Links |37| pricing_model | 90 days | `stale:` flag |38| license | 365 days (and on repo change) | `stale:`/`review:` flag |39| description / editorial review | 365 days | `stale:` flag |40| official page content | on demand (rescan) | `review:` flag with new title/canonical |4142## Failure recovery43- Worker crash: PM2 restarts; running jobs are reaped after 20 min.44- Network outage: jobs fail → backoff → dead after 5 attempts; nothing is unpublished automatically (an outage is not evidence of discontinuation).45- Bad import: validator blocks malformed records; `--partial` writes valid rows only; every change has a revision snapshot to restore from (`tool_revisions.snapshot` can be re-imported through the admin editor's Raw JSON tab).4647## Verification rules enforced by code48`scripts/content-validate.ts`: schema, unique slug/name, file name = slug, leaf categories, one primary, description word counts per depth, minimum sources per depth, official site present, price figures and certification claims rejected, privacy claims require a policy source, marketing words warned. `pnpm eval:ranking` guards search quality (MRR ≥ 0.7).49