Type-ahead search backed by native database indexes

The SDL is the demand side: "a search surface exists here and returns hits of these types."

The index is the supply side: what matching and ranking the backing can honestly deliver.

Graphitron is the type-checker between them, which is already its job between SDL and the jOOQ catalog. The shipped precedent in miniature is @order(index:): the SDL names an index it does not define, catalog.findIndexColumns supplies the facts (its columns), and a failed lookup is a rejection.

Coverage: which columns feed the vector, hence what a match honestly means. Extending coverage is a consumer migration; the SDL reflects it.

Capability set: whether the backing can boolean-match, rank, rank in the index (trigram <->) versus materialize-then-sort (ts_rank), tolerate typos. Gates what the demand side may ask for.

Analyzer semantics: language configuration, stemming. Opaque to graphitron by design; it changes what matches, not the contract.

Entity multiplicity: single-entity backing (a tsvector column on one table) versus multi-entity backing (a search table with a discriminator).

Freshness: trigger-fed or materialized backings lag their sources, and Oracle Text CONTEXT indexes are stale-by-default between SYNC runs (the canonical Oracle migration pins SYNC (ON COMMIT), approximating the Postgres transactional story; consumers who choose SYNC (EVERY ...) accept the window). Consequence: hits may reference dead rows, so the hit-to-entity join-back is an inner join that drops them, and "asked for 20, got 18" is documented behaviour, not a bug.

directives.graphqls: @typeahead (definition above; no enum, the mechanism is dialect-derived).

New model/TypeaheadSpec.java, model/TypeaheadBacking.java; a 0..1 slot on QueryTableField; GraphitronType.TypeaheadHitType implementing CarriesObjectForm + an arm in the SDL emitter.

ArgumentRef.java: new TypeaheadArgRef arm; FieldBuilder.classifyArguments recognizes the synthesized names on @typeahead carriers.

ConnectionPromoter.java: @typeahead arm in synthesiseForField (hit-type synthesis + carrier rewrite); rebuildAssembledForConnections pins the hit types (the walk is shared; whether the class gets a broader name is the implementer’s call).

GraphitronSchemaBuilder.java: rejectTypeaheadMisuse (matrix above); R333’s directive-coverage table gains a @typeahead row in the same commit.

Emitters: the ranked query parts in the root rows-method path (TypeFetcherGenerator + the query-part emitters), per-kind templates; hit node passthrough registration in FetcherRegistrationsEmitter; the Oracle query compiler in the emitted runtime (beside `ConnectionHelper’s generator).

graphitron-sakila-db/init.sql (phase 2): CREATE EXTENSION pg_trgm; the threshold pin (ALTER DATABASE …​ SET pg_trgm.word_similarity_threshold = 0.4, via a format(…​, current_database()) DO block); both authored binding shapes, so the fixtures exercise what the how-to teaches: film.title bound directly with a GiST trigram index (the zero-new-column simple case), and an actor.search_name stored generated column (first_name || ' ' || last_name) + GiST index (the concatenation case). The earlier film.search_text (title || description) sketch is dropped: folding long description text into a trigram column is exactly what the how-to warns against, and the fixture must not model the anti-pattern (long prose is prose search’s demand, out of v1). Bump <jooq.codegen.schema.version> per landing.

The boot-time supply probe in the emitted runtime (beside the ConnectionHelper generator): always on, and on failure it marks the binding so the affected field short-circuits to a client-visible error while the rest of the schema serves.

graphitron-sakila-example: a filmSearch field in the example schema (phase 2, when the emitter exists; see Phasing).

docs/: the Postgres and Oracle how-tos (phase-gated below).

Unit-tier (invariants the type system can’t pin): TypeaheadSpec / TypeaheadBacking compact-constructor rules; the Oracle query compiler’s syntactic invariants, pinned as exact input-to-compiled-string cases (a pure String → String runtime helper, so this is legitimate unit-tier I/O pinning, not a code-string assertion on emitted generator output): tokenization drops every operator character, reserved words compile brace-escaped, the last token gets the prefix-or-fuzzy arm, tokenless input compiles to no query, and no input can produce a DRG- parse error by construction.

Pipeline-tier (primary behavioural tier): classification test (TypeaheadSpec populated with the backing arm the build dialect selects, hit type synthesized and registered, arguments appended); the rejection matrix, one case per arm above; emit-surface test for the generated fetcher (method-surface assertions, no code-string assertions, per the R13 rework lesson); the exact ColumnFacts.sqlType literals the text-type check reads pinned against the real fixture catalog.

Compilation-tier: the sakila example compiles with the search field (consumer javac covers the generated hit type, fetcher, and escaper).

Execution-tier (the proof, Postgres). Trigram: a partial word matches (mat finds "Matrix"), a typo surfaces the intended row in the top hits (not necessarily first: "matirx" can rank a same-prefix title ahead, verified 2026-07-16, so the assertion is membership-in-top-N, not rank-1), a nonsense query returns [] (the threshold), a word-interior match stays out (ma does not find "Amadeus"), a single-accent mismatch matches and ranks below the exact spelling ("flaklypa" finds "Flåklypa"; the accent dividend of the 0.4 threshold, verified 2026-07-16), distance ties order deterministically by PK, and against a database missing pg_trgm the boot probe logs a named error and the filmSearch field short-circuits with a generic field-unavailable client error carrying no backing internals (extension/index names appear in the startup log, not the response) while an ordinary (non-search) field on the same schema still serves. Shared: authored filter argument ANDs with the match; an omitted required first is a validation error; first over maxFirst errors client-visibly with the bound named in the message, and the emitted schema’s first argument description names the 1..maxFirst range; empty search string returns []; the DML pinning test (insert/update on film never writes the backing columns, which stay consumer-invisible). The execution tier runs against the repo’s pinned PostgreSQL image (18 today), which is what makes the supported floor (>= 17) enforced rather than asserted: every wire-behaviour claim drafted against the sandbox’s PostgreSQL 16 gets re-pinned on the supported family the moment phase 2 lands.

Oracle: emit shape pinned at pipeline tier (the SQL templates render without an Oracle connection); execution-tier runs in Sikt’s internal GitLab pipelines with the licensed jOOQ, or in this repo’s CI if the license question resolves that way. Stated plainly for the reviewer, per invariant (the compiler makes a wider behavioural claim than the escaper it replaced, so the honesty note is enumerated, not singular): never-throws on user input is unit-pinned in this repo (the one invariant that lives without an Oracle); prefix matching from two characters, fuzzy typo rescue, conjunctive narrowing, empty-input [], the compiler-to-PREFIX_INDEX/empty-stoplist migration alignment, and the boot probe’s attribute report have no public-CI enforcer and are owed to the internal pipeline as the named execution list in the Oracle how-to’s "What still needs live verification" section, which mirrors the Postgres trigram execution list item for item. The arm ships without the Postgres-grade execution proof until then.

Prose search (the second demand type; vocabulary pinned in decision 9): weighted document relevance over longer text: tsvector
setweight + ts_rank on PostgreSQL, section weighting / ABOUT / query-relaxation templates on Oracle Text. Requester-gated: nobody has asked for it (#512 is type-ahead), and building it now would mean inheriting the combobox contract instead of designing from information-retrieval demand (paging? highlighting? surfaced weights? a documented query language for the search string?). When a requester appears it lands as a sibling directive, expected name @search (deliberately left unclaimed), with its own synthesized surface and its own backing model; the tsvector supply research in this spec, the meta-probe findings, and the extension-landscape section carry over as its groundwork. It is not a mode argument on @typeahead: a mode that reinterprets which other arguments are meaningful is the enum-with-shared-field-set smell at the directive layer (principles-architect consult, 2026-07-16, superseding the earlier demand-axis-argument sketch from the Oracle round).

Quick-filter (the third demand, named 2026-07-16 during the split revisit): a free-text input over a paginated table that narrows rows while preserving the connection’s declared order and keyset cursors; match-only, rank-free. Recognized here so the future "why can’t I just search my connection?" question has a recorded answer: it is not type-ahead (no rank, no bound, no hit wrapper, different quality contract) and merging it into @typeahead or into connections would re-fuse demands the grain round separated. Zero requesters today (#512’s requester explicitly serves table pages with ordinary keyset connections), and its contract questions are open: the pinned 0.4 threshold is tuned for rank-and-bound and admits noise a rank-free filter would surface, typo tolerance may not belong in a filter at all, and unranked fuzzy filtering may not be honest UX. If a requester appears it is designed as its own surface (plausibly an argument-level directive, since it genuinely is a filter contribution), never by bending @typeahead into connections.

BM25-class relevance (pg_search/ParadeDB, pg_textsearch/ TigerData): genuinely better ranking than ts_rank on long-text corpora (corpus statistics, length normalization); a prose-search concern, so it belongs to that future feature’s backing model, not to TypeaheadBacking. Neither extension is available on Amazon RDS today anyway, so there is nothing to bind; see The Postgres extension landscape and RDS.

Multi-type search (a consumer-owned search table with an entity-type discriminator): a recognized backing shape, deferred until demanded; a future backing model grows a SearchTable arm (on @typeahead or the prose sibling, whichever the demand names) and reuses the row-domain discrimination and node-key machinery.

Child-field search, relevance score exposure, highlighting (ts_headline), deep ranked paging, and external engines (Elasticsearch and kin): all recorded in the decisions and dead-ends sections above with their rationale.

LSP completion for column: (offering the entity table’s text columns as candidates): a natural follow-up on the CompletionData surface, not part of this item.

tsvector columns, as TableField<R, Object> with the qualified type name "pg_catalog"."tsvector" preserved verbatim. A vector binding can therefore be type-checked: the named column exists and is a tsvector.

Column-based indexes of every access method, by name, with ordered fields and the unique flag. The GIN-over-tsvector and the trigram index both appear; membership validation works for both.

The access method (GIN vs GiST vs btree) and the operator class (gist_trgm_ops).

Expression indexes are dropped entirely: the to_tsvector(...) expression GIN index simply does not exist in the generated meta, so it cannot even be membership-validated.

The partial-index predicate (the index appears, its WHERE does not).

Generated-column-ness and its expression: the stored tsvector column looks like a plain writable column (the generated record even carries a setter for it).

Validation splits cleanly: membership and column-type checks are promised (binding names resolve, a vector binding is really a tsvector); capability is authored-asserted (the binding’s declared kind, e.g. fulltext vs trigram, is trusted for semantics, the same trust boundary as @condition Java methods).

The blessed Postgres recipe is forced to be the stored generated column, not an expression index: the stored column is visible and type-checkable in the catalog, the expression index is invisible. This independently confirms the recipe choice made for other reasons (trigger-free, deterministic).

DML caveat to pin in fixture work: open-source jOOQ does not mark generated columns readonly, so a graphitron write path must never touch the vector column. Expected safe (tsvector columns are never SDL-mapped, and DML writes only mapped fields), but it needs a pinning test on a searchable-and-mutable table.

Follow-up option, not v1: a graphitron-provided jOOQ generator extension could recover full capability validation by querying pg_catalog (access method, opclass, index expressions) during codegen, where the live JDBC connection exists, and embedding the facts as a side-car the graphitron catalog reads. Opt-in for consumers who want strict validation; the authored assertion remains the baseline.

The same probe should be repeated against Oracle (does jOOQ meta list CTXSYS.CONTEXT domain indexes at all?) once licensed test infrastructure exists; until then the Oracle binding is asserted-only by assumption.

Trigram + tsvector is the complete RDS menu for index-backed ranking, and the two split cleanly along the demand vocabulary: trigram is the type-ahead mechanism, tsvector is the waiting prose-search mechanism. An empirical demo (2026-07-16) pinned the fork: websearch_to_tsquery('mat') finds nothing in "Matrix" (no prefix matching in core FTS), while trigram word_similarity ranks it first, and EXPLAIN confirms the trigram KNN ordering runs off the GiST index (Index Scan ... Order By).

A Bm25 arm is a pure addition (new sealed arm, two SQL templates, a how-to) if RDS ever adopts one of the extensions or the deployment constraint changes; the SDL contract would not move.

orderBy: RELEVANCE on existing connections: argument-correlation validation GraphQL cannot express, a hardcoded-false pageInfo that violates the Relay spec, dual-strategy resolvers. Rejected 2026-07-15; re-affirmed with stronger arguments 2026-07-16 (decision 1).

SDL-authored index definitions (@searchable field tags with weights, a logical index namespace owned by graphitron): authoring a schema object we neither create nor enforce is DDL in spirit; weights are index-time facts. Rejected 2026-07-15.

Naive ILIKE shim as a zero-migration on-ramp: does not scale, and as the default path it is an enticing dead end. The on-ramp is documentation of the real migration instead. Rejected 2026-07-15.

Deep ranked paging (ranked-offset cursors): no consumer demand (top-N confirmed sufficient). Archived below, not deleted, in case demand appears.

2026-07-17 (self-review tightening): a principles-architect self-review of the just-resolved open questions surfaced two consistency seams (a how-to worked query that omitted the now-required first; the hit-type record named both TypeaheadHitType and SearchHitType), both fixed, and three design tightenings the owner accepted. (1) maxFirst is communicated on both channels, not the error message alone: the synthesized first argument carries a generated description naming the 1..maxFirst range (introspectable before any request) and the exceeds-cap error message names the value (read reactively). The early "error message is the only channel" framing was a false premise, the argument description is a durable, introspectable channel; both are kept because the description is proactive prose a developer may skip while the error is the more-likely-read channel. (2) The boot-probe field short-circuit is named as a third error disposition: a probe-detected backing failure surfaces as a client-visible field-unavailable error with a generic, redaction-safe message (cause log-only), distinct from both the redacted per-request arm and the specific-message client-error arm; the per-binding health flag is written once at boot and read-only thereafter. (3) The phase-1 Deferred gate’s enforcer is the classifier-attached Rejection.Deferred pinned by a new pipeline assertion, not the leaf-granular stubbed-leaf coverage check (which cannot express a within-leaf QueryTableField-carries-TypeaheadSpec predicate). Carried as a handoff caution, not a spec edit: the eventual operation-fact quarantine code comment must state the fact without citing a roadmap id (the reference guard forbids it).

2026-07-17: reviewer open questions 1, 3, 5 resolved by the item owner. (1) first over maxFirst errors rather than clamps, and first loses its default entirely (first: Int!, required, no defaultFirst): clients choose a page size explicitly; since GraphQL cannot express maxFirst in the schema, the exceeds-cap error message is the sole channel that communicates the bound, so the emitter must name it there. (3) The root-only carrier gate (no @typeahead on child fields in v1) is an accepted deferral. (5) The boot-time supply probe is always on with no opt-out; a failed probe logs a named error/warning and short-circuits only the affected field with a client-visible error, leaving the rest of the schema serving, superseding both the earlier fail-fast-whole-boot and opt-out positions. Design body updated across the directive, wire shape, TypeaheadSpec, runtime semantics, validation, tests, and both how-to drafts; no phasing change.

2026-07-17: Oracle-licensing build-mechanics question (reviewer open question 4) resolved after the item owner wrote to Lukas Eder (jOOQ). The commercial jOOQ repo can be declared openly in this repo’s build with a license-required comment; credentials are supplied via system properties out-of-band (jOOQ-mcve settings.gradle.kts pattern: jooqUsername / jooqPassword; the Maven analog is a settings.xml <server> reading a property or env var), never committed. Contributors without a license are unaffected; only the licensed-profile-active build that resolves an org.jooq.pro artifact needs credentials. Consequence: licensed Oracle execution can run in this repo’s CI, reducing open question 4 to a pure secret-provisioning decision (this repo’s CI vs internal GitLab), not a build-structure or design blocker. Phase 3 lands the gated commercial-repo declaration + Oracle profile either way; no design change follows. Follow-up in the same exchange added a structural refinement: never publish the license key, and modularize by licensed usage so most of the repo builds license-free. Recorded in open question 4 as: isolate the licensed Oracle execution tests in their own Oracle-IT-profile-gated leaf on org.jooq.pro, keeping model / classification / synthesis / validation / SQL-template emit / pipeline- tier Oracle shape pinning on open-source org.jooq in the existing modules, so a license is needed only to run that one leaf.

2026-07-02: filed from the initial issue #512 discussion (contract vs execution split, no-DDL constraint, keyset-vs-rank analysis).

2026-07-14: requester confirmed bounded top-N suffices for the combobox case; deep ranked paging has no driver.

2026-07-16 (Spec revision, the split revisit): does the dedicated-field decision still hold after the reshape? Yes, stronger. All three original arguments survive (argument-correlation validation, structural keyset-impossibility of score ordering, two demands behind one field), and three new ones arrived with this week’s rounds: the 0.4 threshold pin makes match-without-rank defective under our own recipe; the Oracle compiler makes the search string a compiled input rather than a filter value; the hit wrapper’s forward bet has no landing zone on connection edges. The honest cost was named and recorded: the admin-table quick-filter case is a distinct third demand (match-only, pagination-preserving), now in Non-goals as recognized and requesterless with its open contract questions listed. Decisions 1 and 6 carry the re-affirmation; both how-tos gained the quick-filter distinction.

2026-07-16 (Spec revision, the grain round): are we at the correct grain, or mixing use cases? Two mixings found and corrected. (1) The SearchKind enum spliced mechanism and demand everywhere, not just on Oracle: TRIGRAM and TSVECTOR each carried an implicit demand. (2) Type-ahead and prose search shared one wire contract by construction-history: different input semantics (raw keystrokes vs the websearch query language), different result affordances, only one requester between them. Resolution (owner decision
principles-architect consult): vocabulary pinned (type-ahead vs prose search, decision 9); v1 serves type-ahead only; the directive is demand-named and sheds kind: and config: (@typeahead(column:, defaultFirst:, maxFirst:)), since demand plus dialect determine the mechanism uniquely and the migration fact’s only real enforcer is the boot probe; the classifier reads the dialect once through the JooqCatalog boundary and carries the sealed TypeaheadBacking arm (Trigram/OracleText, both legitimately empty records; sealed earned by template dispatch plus Deferred coverage); the Deferred gate is dialect-keyed (Oracle builds stay Deferred until phase 3); a future second mechanism on one dialect lands as an optional mechanism: argument defaulting to the incumbent; prose search moves to Non-goals as a requester-gated sibling directive with @search left unclaimed for it, superseding the Oracle round’s demand-axis-argument sketch. Reviewer question 2 dissolved; phasing is three landings (the tsvector phase removed); item retitled.

2026-07-16 (Spec revision, floor statement): the supported PostgreSQL floor for the feature is >= 17 (item owner). No design change follows: 17/18 release notes carry no pg_trgm/core-FTS behaviour changes touching the verified claims (17.9’s strict_word_similarity crash fix is in a function we don’t use), and the repo’s CI and Testcontainers already pin PostgreSQL 18. The drafting-sandbox empirics ran on its native 16 (PGDG is unreachable from the sandbox, so no local 17 re-run); the honesty anchor moves to the execution tier, which runs on the repo’s pinned image and re-pins every claim on the supported family when phase 2 lands. One version-scoped correction: the collation round’s LIKE-rejection side-note holds for ⇐ 17 only (18 supports LIKE on nondeterministic collations), which does not touch the round’s conclusion.

2026-07-16 (Spec revision, Oracle docs-first round): drafted the Oracle how-to (relevance-ranked-search-oracle-howto.adoc) by the same docs-first method, against Oracle Text documentation (no live Oracle in this environment; the how-to’s banner says so and its closing verification checklist becomes the internal pipeline’s execution plan). Writing it falsified the "conservative escaper" design: in the CONTEXT grammar whitespace means phrase and brace-escaped literals get no prefix or fuzzy expansion, so the escaper as specced compiled the combobox demand away entirely. Replaced by a query compiler (tokenize, fuzzy completed tokens, prefix-or-fuzzy last token, & conjunction, braces only for reserved words) that is mutually load-bearing with a canonical migration pinning PREFIX_INDEX, STOPLIST CTXSYS.EMPTY_STOPLIST (a stopword-only query is otherwise a DRG-50901 error, breaking never-throws parity), BASE_LETTER accent folding (full accent insensitivity, stronger than the Postgres arm’s threshold dividend), SYNC (ON COMMIT) and OPTIMIZE (AUTO_DAILY). Principles-architect consult resolved the one-mechanism-two-demands fork: kind names the mechanism, v1 pins ORACLE_TEXT to type-ahead, future Oracle prose relevance is a demand-axis directive argument (see Non-goals), not a new kind and not a config: overload. The Tests section’s Oracle honesty note is now enumerated per invariant.

2026-07-16 (Spec revision, collation question): can collations serve accent-insensitive search? No: trigram similarity ignores the column’s collation entirely (verified: identical word_similarity on a plain column and on an accent-insensitive nondeterministic ICU collation; PostgreSQL 17 and older additionally reject LIKE on nondeterministic collations, 18 lifts that, irrelevant to the conclusion either way). Collations govern =/ORDER BY, never <%. The round did surface a dividend: at the pinned 0.4 threshold, one accented character behaves as a substitution typo, so "flaklypa" finds "Flåklypa" with no unaccent machinery (accent-dense words still miss, ~0.29 for "blabar" vs "Blåbærsyltetøy"). The how-to’s earlier accent-significant claim was measured at the 0.6 default and is corrected; the accent-dividend case joins the trigram execution tests.

2026-07-16 (Spec revision, later the same day): the docs-first round. Drafted the trigram combobox how-to (relevance-ranked-search-howto.adoc) ahead of implementation to force the user-experience decisions; its typing-behaviour tables are empirically verified (PostgreSQL 16 + pg_trgm) and double as phase 2 acceptance behaviour. Writing it falsified two comfortable assumptions: (1) the default word_similarity_threshold (0.6) drops single-typo matches (~0.43), so "stays at the pg_trgm default" was replaced by the pinned 0.4 in the canonical migration, boot-probe-reported; (2) the film.search_text (title || description) fixture sketch modeled the long-text-in-trigram anti-pattern the how-to itself warns against, so the fixture became two shapes (direct film.title bind, concatenated actor.search_name). Also honesty-tightened the typo execution assertion to membership-in-top-N (a same-prefix title can outrank the intended row).

2026-07-16 (Spec revision): the extension round. RDS reality researched and recorded (BM25 extensions pg_search/pg_textsearch named as anticipated future SearchBacking arms, unavailable on RDS today; rum/PGroonga excluded); TRIGRAM promoted into v1 as the combobox default after an empirical demo showed core FTS fails partial-word type-ahead while pg_trgm serves it with index-ordered KNN ranking; the string-similarity vs document-relevance distinction documented, with the threshold + KNN combination; phasing reordered (trigram end-to-end before tsvector, Oracle now phase 4); boot-time supply probe added to the runtime design (reviewer question 5).

2026-07-15 (Spec revision, same session): folded in the principles-architect consult. Load-bearing changes: phase 1 lands @search as Rejection.Deferred so a classified-but-unemitted carrier fails the build instead of serving unranked rows (validator-mirror gap); the carrier fact is single-sourced (classifier resolves every application to SearchSpec or Rejection, misuse pass drains rejections, emitter keys off the same spec). Precision: the Operation.Facet precedent is a dead placeholder while search is live (noted as the obligation behind single-sourcing); the first/pagination name-space overlap is pinned with the @asConnection co-occurrence rejection named load-bearing; the Oracle arm’s weaker in-repo enforcement floor is stated plainly.

2026-07-15 (Spec): expanded to a full plan (authored @search surface, promoter-ridden hit-type synthesis, SearchSpec/SearchBacking model, per-kind SQL templates, rejection matrix, three-phase landing) and transitioned Backlog → Spec.

2026-07-15 (later): resolved the jOOQ-meta feasibility question empirically (see the dedicated section); no open question gates the Spec transition any longer. Also recorded why we stay on our authored fixture schema instead of switching to pagila: the sakila core is a minority of the fixture corpus and is itself customized load-bearingly (category.parent_category_id self-FK pinned by constraint name, film.text_rating in the example schema); we import pagila’s idea (a film.fulltext column) built the modern way (stored generated column) rather than its trigger-based implementation.

2026-07-15: design discussion closed the major forks: dedicated generated field over a connection order mode; hit wrapper without score; the demand/supply inversion (the index is a consumer-owned bearer of facts, graphitron maps and type-checks); backed-only with no shim; native mechanisms for Postgres and Oracle in scope, external engines out; documentation as a first-class deliverable. Later the same day: Sikt holds a jOOQ license, so Oracle codegen support is committed; test placement (internal GitLab pipelines worst case, possibly a CI license via Lukas) is a logistics question inside the Spec, not a gate on it.