Build Django-owned PostgreSQL search and cross-source graph activation

Parent epic: #4

Normative authority:

• Spec 01 — application boundaries and content refresh
• Spec 02 — exact URL, link, fragment, and SEO compatibility
• Spec 03 — search/graph and activation safety
• Spec 06 — Studio/admin API parity
• Spec 07 — graceful degradation and safe diagnostics
• Spec 10 — search/graph verification
• Database portability boundary
• closed owner decision #24: deploy Django-owned search on PostgreSQL, preserve the FAQ and Wiki query/filter/graph contracts, and retain the current Lambda until measured relevance parity and rollback evidence pass

Product outcome

Replace the checked/static and Lambda-backed search path with one deterministic Django-owned search and graph projection over the accepted public Main, Docs, FAQ, and Wiki records. Deployed development/production persist and query it in RDS PostgreSQL. Its document, token, ranking, lifecycle, and query contracts remain backend-portable and pass ordinary SQLite CI; this issue introduces no PostgreSQL-only application branch, raw SQL, extension, trigger, vendor skip, or separate PostgreSQL test suite.

A complete candidate is built and validated before it can become active. Activation swaps the search/graph pointer with the matching public content identity atomically, while a failed build or swap leaves the prior projection and all source pages available. Content operators receive safe, permissioned Studio/admin API status, diagnostics, rebuild, activation, and rollback controls over the same application services.

Current baseline and disposition

Current authoritative website baseline is origin/main@face8e4808d65afbf0374d1ced7a88079950d663.

• content/public_projection/wiki_search.json is a checked 2,998-document corpus (1,024 page and 1,974 section records) with SHA-256 74d6373df63386aead5108e5addd5ecf2a83a885e891338ca72022e6bdcd3fef.
• content/public_projection/wiki_graph.json is a checked 1,072-node / 13,006-link graph with SHA-256 f6ec4fb29e71dd179cdaa79526bfb4174ad25ee4991dfcba7f2fe3c5b0e99a13.
• Django's current /wiki?q= adapter performs an unranked case-folded substring scan, ignores the accepted type filters, drops every non-/wiki/ record, and returns at most 100 unique URLs. The pinned Podwiki Lambda instead uses Porter stemming, weighted title/section/body retrieval, deterministic reranking bonuses, the accepted level and document_type filters, and at most 20 results.
• ContentRelease.search_build_id and graph_build_id are nullable opaque seams only. There is no persisted build, input snapshot, validated digest, active projection pointer, atomic projection activation, rollback service, or matching management capability.
• There is no #44 engineer handoff, independent tester report, PM acceptance, commit, merge, deployment, or Lambda-retirement evidence. Old comments tied to 1db4882, open #24, or Depends on #37–#43 are historical only and are superseded by this body.

#44 is groomed but dependency-blocked. No engineer may start until every hard prerequisite in the final section is accepted, integrated, and revalidated on a clean green current base.

Ownership boundary

• #292 owns the offline Docs parser/parity census. Its later focused #41 adoption slice owns a reproducible public Docs projection and section/fragment identities, but no search index.
• #293 owns the offline FAQ adapter/feed projection. Its later focused #42 adoption slice owns the accepted public FAQ projection and ten-character question fragments, but no search index.
• #294 owns route-neutral Podwiki source pages, typed occurrences, and graph/search seeds.
• #43 consumes #294 and the exact cross-source resolvers and owns the deterministic public /wiki page/fragment/entity identities and presentation. #43 does not build or activate search or graph projections.
• #44 consumes accepted public identities and source seeds. It alone owns search-document construction, tokenization, ranking, query/filter execution, deterministic graph/link projection, cross-source projection validation, build records/digests, activation/fallback/rollback, parity measurement, management diagnostics, and the Lambda retirement gate.
• #38 and its children later call the accepted #44 services during direct sync. They own source ingestion, source authority, jobs, reconciliation, and public-reader cutover; they are not prerequisites for this checked-projection search lane, and #44 does not introduce a second source-sync system.

Search document and ranking contract

Build one versioned immutable document projection from public-safe DTOs only. Each record binds a stable namespaced identity, source family (main, docs, faq, or wiki), public kind, page or section document type, exact canonical URL plus optional validated fragment, title/page title/section title, normalized public text, bounded public related terms, active input identity/checksum, schema/ranker version, and one canonical record digest. Raw Markdown, raw frontmatter, source paths, edit URLs, private/unpublished records, structured errors, credentials, account data, and arbitrary exception text never enter the index or public result.

Tokenization and ordering are code-owned and deterministic:

• normalize Unicode to NFKC, case-fold, split on non-alphanumeric boundaries, retain Unicode alphanumeric tokens, and apply the pinned Lambda-compatible English Porter stem only to ASCII alphabetic tokens; there is no language detection or locale-dependent ordering;
• trim the public query and bound it to the current 200-character contract; empty-after-normalization returns the accepted empty state without scanning all documents;
• every effective query token must match at least one indexed public field;
• base score is the sum of distinct-token matches weighted title=4, segment_title=3, and text=1;
• preserve the pinned rerank bonuses: normalized phrase in title +200, section title +120, related terms +80; every token in title +70, every token in related terms +35, and a page record with every token in title plus related terms +20;
• order by descending total score, then descending matched-token count, then page before section, then canonical URL and stable identity by code-point order. Duplicate result identities or canonical URLs fail the build; the public Wiki adapter returns at most 20 results.

The unified service indexes accepted Main pages, Docs pages/sections, FAQ questions at their stable fragments, and Wiki topics/special types/sections plus accepted mirrored Podcast/Person/Book entries. No new global public search page is introduced here. Existing owning surfaces consume bounded adapters; /wiki?q= exposes the preserved Wiki family/filter contract, while the complete internal projection and operator diagnostics may cover all four source families.

Preserved public contracts

• GET and HEAD /wiki?q=<term> remain the canonical server-rendered Wiki search surface. Presence of q, including q=, selects search mode; /wiki/search remains the editorial detail page and is never captured by query search.
• Preserve level=wiki|guide|comparison|roadmap|transition|how_to|podcast_summary|person|book, the historical level=section shorthand, and document_type=page|section. level=section selects section records. Otherwise a recognized level and recognized document type combine. Missing/unknown filter values retain the current all/page behavior and never widen beyond the public Wiki family.
• Valid searches return at most 20 safe results with the accepted title, section label, kind, canonical URL/fragment, and no source/diagnostic fields. q= renders the search form without a false no-results claim; zero results use the existing safe empty copy. Queries and result pages are private/no-store/noindex and absent from shared caches, feeds, and sitemaps.
• /wiki/search-corpus.json remains a deterministic, schema-validated public compatibility payload for the active Wiki subset. It contains no Main/Docs/FAQ records or management provenance. /faq/json/* remains a separate exact FAQ contract and is never replaced by search output.
• /wiki/graph/graph.json, /wiki/graph, node IDs/types, link endpoints/kinds/weights, URL localization, hash/deep links, and deterministic ordering remain compatible. No /podwiki public destination is generated.
• Search or graph unavailability never makes a Main, Docs, FAQ, Wiki, Podcast, Person, or Book page unavailable.

Graph and cross-source validation

Construct the graph from #294's validated route-neutral seeds plus #43's public Wiki identities and accepted exact Podcast, Person, and Book resolvers. Bind every node and edge to the same input snapshot as the search build. Preserve the accepted initial 1,072 nodes / 13,006 links and public JSON schema unless a separately accepted upstream public-input change supplies a reviewed deterministic delta.

Every required internal route, entity key, asset, and heading/fragment must resolve exactly once in the candidate public route/fragment manifest. Missing, duplicate, ambiguous, case-folded, label-inferred, unsafe, cross-release, or wrong-family targets block the complete candidate. Only explicitly reviewed optional historical external targets may remain as bounded warnings; they are never fetched synchronously and never become internal claims.

Two clean builds from the same ordered input snapshot must produce byte-identical search documents, graph JSON, counts, diagnostics, and canonical digests even when input enumeration order changes.

Build, activation, fallback, and rollback

Persist immutable search/graph build records with state, schema/ranker/builder versions, exact input source/release or checked-projection identities and digests, document/node/edge counts, candidate digests, bounded warnings/errors, actor/request provenance, and lifecycle timestamps. One build covers one complete cross-source snapshot; partial source-family activation is forbidden.

• Rebuild creates a candidate without changing public readers.
• Validation completes document/schema/ranking fixtures, graph/link/fragment checks, public payload generation, and input-revision rechecks before ready.
• In one transaction, activation locks/rechecks the candidate and current input/pointer revisions, installs the matching search and graph active pointer/build identifiers with the content identity, retains the prior active build, and writes one redacted audit event. Concurrent stale contenders fail; exactly one valid swap wins.
• Build, validation, audit, constraint, revision, or injected pre-swap failure rolls back completely. The prior active database build remains selected. Before final Lambda retirement, the checked Wiki corpus/Lambda comparison target remains available as the compatibility rollback envelope; Django public content does not perform a request-time provider write or source fetch.
• Explicit rollback selects a retained previously active, still-valid complete build only after revalidating its exact input/public-route envelope; it never mixes a prior graph with a newer search build.
• If the selected search adapter encounters a known projection-read failure, return a bounded unavailable/empty search state with no-store and keep content pages serving. A graph-read failure uses the accepted unavailable/no-data presentation and never leaks diagnostics. Unknown corruption fails closed and alerts operators.

Relevance parity and Lambda retirement gate

Freeze a versioned, repository-owned golden query set covering exact title, partial phrase, heading/section, every accepted level, both document types, FAQ inclusion, Docs section inclusion, Main content, stemming, Unicode, punctuation, empty/zero-result, overlong, encoded, and malicious input. It contains public query/expected-result identities only, no production query logs or personal data.

Before activation, all safety/schema/filter/status cases must match exactly. Against the immutable pinned Lambda/index baseline, the Wiki result set must achieve mean NDCG@20 of at least 0.90, no query below 0.80, and 100% preservation of every fixture's required top-1 and top-5 canonical result. Any threshold miss blocks activation; results may differ only where the accepted canonical /wiki identity or an explicitly reviewed upstream content delta requires it.

Lambda retirement is not part of the implementation handoff. It occurs only after #44's accepted build is deployed, the same parity suite passes against the live release identity, rollback to the retained build is exercised, and the product owner records the retirement approval. Until then the Lambda and its immutable comparison artifacts remain intact. A later provider operation must follow the repository lifecycle and may not be inferred from this grooming pass.

Management services and adapters

Add one shared read service and idempotent command services, then register matching Studio and /api/v1/admin/ adapters:

• status/detail: active/candidate build identity, bound public input identities, versions/digests/counts, safe state/timestamps, warning/error codes, fallback/rollback readiness, and parity summary;
• rebuild/validate: create or reuse one candidate for the exact input plus idempotency key, with explicit dry-run and bounded result;
• activate and rollback: explicit confirmation, idempotency, revision/If-Match, current-input recheck, high-risk audit, and one complete search+graph build only.

Use distinct Django permissions content.view_search_graph and content.change_search_graph. The content_operator role receives both through the normal role manifest; all other principals fail before object lookup or diagnostic disclosure. Studio and admin API must have service, permission, validation, result, idempotency, concurrency, audit, and redaction parity. Every response is private/no-store/noindex with zero edge TTL. Diagnostics expose no source body, raw query history, checkout/storage path, credential, account/PII, arbitrary exception text, or provider secret.

Explicit non-goals

No source parser or token grammar owned by #292/#293/#294; no public Wiki page projection or canonical resolver owned by #43; no source sync/webhook/reconciliation or public-reader authority cutover owned by #38; no PostgreSQL-specific application behavior; no vector/semantic/external search service; no personalization, analytics/query logging, spelling correction, autocomplete, search UX redesign, FAQ feed change, new public global-search route, /podwiki route/redirect, public provenance/edit link, partial projection activation, source repository write, provider/production access during implementation, or unmeasured Lambda retirement.

Acceptance criteria

• One backend-portable immutable build represents the complete accepted Main/Docs/FAQ/Wiki input snapshot and produces deterministic search, Wiki compatibility corpus, graph, diagnostics, counts, versions, and digests twice byte-for-byte.
• Public-safe document construction covers pages and sections/questions/fragments for all four source families and excludes every private, unpublished, raw-source, management, credential, account, and diagnostic field.
• Tokenization, Porter behavior, exact score/bonus/tie-break ordering, 20-result cap, deduplication, Unicode, bounds, and safe malicious-query behavior match the contract above on SQLite and deployed PostgreSQL without backend branches or skips.
• /wiki?q=, q=, all accepted levels/document types, unknown filters, /wiki/search, zero results, /wiki/search-corpus.json, and FAQ JSON separation preserve exact status/schema/cache/noindex/URL/fragment behavior.
• The graph preserves the accepted schema/IDs/types/edges/deep links and resolves every required route/entity/asset/fragment exactly once; unsafe or ambiguous cross-source input blocks the whole candidate with bounded diagnostics.
• Build/validation failure, interrupted rebuild, stale input, concurrent contender, injected transaction/audit failure, read failure, activation, and explicit rollback prove no mixed or partial state and preserve source-page availability and the prior active build.
• Golden fixtures pass exact safety/filter contracts, mean NDCG@20 >= 0.90, every-query NDCG@20 >= 0.80, and all required top-1/top-5 identities against the pinned Lambda baseline before activation.
• Studio/admin API status, rebuild, validate, activate, and rollback adapters share services and pass exact permission, denial-before-lookup, CSRF/scope, idempotency, If-Match, confirmation, audit, redaction, private-cache, OpenAPI, and result-parity checks.
• The database-portability gate, migration drift, formatting, lint, type checking, focused Django/integration/security tests, deterministic PostgreSQL deployment compatibility checks, and current change-selective verification plan pass with no required skip.
• A separate tester verifies the frozen candidate and all plan-selected suites, captures/reads every graph-required desktop/mobile screenshot, and posts PASS; PM then accepts before any commit.
• [HUMAN] After the accepted build is deployed, the exact live identity passes parity/rollback observation and the product owner explicitly approves Lambda retirement; until then no Lambda resource is changed or removed.

Required Django/integration scenarios

1. Build twice from the same accepted cross-source snapshot under reversed/shuffled enumeration; compare every row/payload/diagnostic/digest and prove input tampering invalidates the build.
2. Query exact/partial/title/heading/body/stem/Unicode/punctuation and every source family, level, and document type; assert formula scores, ties, stable URLs/fragments, 20 cap, deduplication, empty/zero/overlong/encoded/malicious behavior, and no raw-source leakage.
3. Mutate one route, fragment, entity key, graph endpoint, asset, source identity, count, checksum, schema, ranker version, or optional/required classification at a time; assert a deterministic block and no public pointer change.
4. Inject build, validation, persistence, audit, stale-revision, concurrent-activation, and rollback failures; prove the complete prior search+graph pair and all public content pages remain available.
5. Exercise anonymous, wrong-role, read-only, content-operator, disabled/revoked, stale-token, missing/wrong idempotency, stale If-Match, and repeated-command cases through both Studio and admin API; assert parity and bounded diagnostics.
6. Replay the frozen golden suite against the pinned Lambda artifacts and the candidate, calculate the exact parity metrics, and prove any result below a threshold blocks activation and retirement.

Browser and screenshot scenarios

The independent tester uses the graph-selected full Playwright tier because public templates/query behavior and browser-visible states change. At minimum, capture and inspect desktop and mobile evidence for:

1. /wiki?q=data+engineering, a section result with a working deep link, and each visible filter family, with keyboard-only operation, focus visibility, live/count labeling, light/dark mode, reduced motion, long Unicode input, and no horizontal overflow;
2. q=, zero-result, unknown-filter, overlong/safe-malicious input, and known search-unavailable states, confirming no debug/error/diagnostic leak and that ordinary Wiki pages still load;
3. /wiki/graph and a representative graph hash deep link plus the graph unavailable/no-data state, with JavaScript on/off where selected; and
4. representative Main, Docs, FAQ-fragment, Wiki, Podcast, Person, and Book result destinations, confirming canonical URLs, headings, and no /podwiki or source-provenance leak.

Dependencies and exact order

Satisfied authority/foundations: #24, #35, and #37.

Hard prerequisites before engineering:

1. accepted/integrated #253 supplies a clean reproducible Main/Podcast/Book/public-projection source envelope;
2. accepted/integrated #40 supplies the exact canonical Person resolver;
3. accepted/integrated #294 supplies typed route-neutral Podwiki pages/occurrences and graph/search seeds;
4. accepted/integrated #43, after #253/#40/#294, supplies the complete canonical /wiki public page/entity/fragment projection and compatibility fixtures;
5. accepted/integrated #292 followed by the separately groomed pre-search #41 source-selection/public-projection adoption slice supplies reproducible Docs pages, sections, paths, and fragments without waiting for #44 implementation; and
6. accepted/integrated #293 followed by the separately groomed pre-search #42 checked-projection adoption slice supplies the selected reproducible FAQ questions/pages/ten-character fragments without waiting for #44 implementation.

Thus the acyclic convergence is #294 + #40 + #253 → #43, #292 → focused #41 pre-search projection, and #293 → focused #42 pre-search projection, then those accepted public inputs converge on #44. #41/#42 parent closure, #38, and its direct-sync children are not prerequisites. After #44, final #41/#42 search integration and #38 direct-sync call the accepted #44 interface; they do not move search/graph ownership back into source adapters.

No additional #44 child is filed in this grooming pass. #292, #293, and #294 already provide the independently testable source-only lanes; the remaining search, graph, validation, lifecycle, and management behavior shares one atomic projection contract and cannot be split safely without a new owner decision.

Any source pin/tree, accepted public DTO/schema, canonical resolver, route/fragment manifest, checked compatibility payload, ranker/golden fixture, schema, or required verification input change after the engineer freezes its plan invalidates the handoff and returns #44 to PM. Engineering begins only from a clean current origin/main with green required baseline gates.

Start by reading _docs/specs/01-platform-architecture.md, _docs/specs/03-github-content-and-people.md, _docs/specs/10-verification-strategy.md, and architecture/database-portability.md, then confirm prerequisites #292–#294 and #41–#43 are accepted. The work is complete only when deterministic search and graph projections, validation, activation, fallback, rollback, diagnostics, and parity evidence satisfy the stated contracts.