Skip to main content

pg-sourcerer Architecture

This document describes the code in src/ as it is now. Each claim names the file that holds it. For how to run the generator, read AUTHORING.md.

Terms​

  • Input service: a Context.Service that holds a value supplied once per run with Layer.succeed at the composition root. The three inputs are DatabaseSnapshot, VisitorRole and QueryRules (src/inputs.ts).
  • Core service: a Context.Service that holds the computed model for one run. Each has make and one static readonly layer. The core services are SemanticModel, Names, Suggestions, Selection, Plans and Shapes.
  • Plugin: a name, the plugins it requires, the services it holds and an optional output, an Effect that builds a list of documents from the core services (src/plugin.ts). The built-in names are effect, kysely, types, zod, valibot, arktype, typia, sql-queries, hono, express, elysia, trpc, orpc and suggestion-report (src/pipeline.ts).
  • Candidate: one query that the visitor's schema supports, such as find-by-key on a table (src/suggestions/schema.ts).
  • Query plan: a backend-neutral description of one selected candidate (src/plans/schema.ts).
  • Document: one generated file, a path and its source text (src/domain.ts, Document).
  • Generation: one publication of a complete document set. The pristine output is recorded as hashes in .pg-sourcerer/manifest.json.

Pipeline​

generateProject in src/runtime.ts runs four steps in order:

  1. Acquire the source. snapshotFromFile (src/source.ts) decodes the input file once with a schema union: a CatalogSnapshot JSON, or PostgreSQL introspection output read through src/pg-source.ts. snapshotFromConnection runs makeIntrospectionQuery() on a live database and decodes the result through the same introspection schema. The CLI takes exactly one of --input or --connection (DATABASE_URL when neither is given). The browser entry (src/browser.ts) uses DatabaseSnapshot.fromExecutor, which takes rows from any SQL executor such as PGlite and imports no platform module.
  2. Generate documents. generateSnapshot in src/pipeline.ts provides the input layers and coreLayer, runs the selected output effects, and merges their documents.
  3. Validate. validateSnapshot in src/snapshot.ts checks paths and typechecks the documents.
  4. Publish. commitSnapshot in src/snapshot.ts writes the next generation.

Step 2 is pure Effect composition. Step 1 reads the input file, step 3 reads installed packages, and step 4 writes files.

Services And Layers​

Inputs​

CLI flags and library arguments become Layer.succeed values at the composition root (src/pipeline.ts, generateSnapshot):

ServiceShapeSource
DatabaseSnapshotCatalogSnapshot (decoded catalog)input file or live database
VisitorRolerole name stringCLI argument
QueryRulesreadonly rule listlibrary argument; CLI passes []

Core services​

Each core module owns one service. make yields upstream tags and calls the existing pure builder. Unit tests call the pure builders directly.

ServiceModuleYieldsBuilder
SemanticModelsrc/semantic/service.tsDatabaseSnapshot, VisitorRolebuildSemanticModels
Namessrc/names/service.tsSemanticModel, InflectionbuildNames
Suggestionssrc/suggestions/service.tsSemanticModelbuildQuerySuggestions
Selectionsrc/selection/service.tsSuggestions, SemanticModel, Names, QueryRulesselectQueries
Planssrc/plans/service.tsSelection, SemanticModel, NamesbuildQueryPlans
Shapessrc/shapes/service.tsPlans, SemanticModel, Names, DomainOptionsbuildShapeCatalog

coreLayer is Shapes.layer with Layer.provideMerge of the upstream layers. Effect memoizes each layer by reference; there are no layer factories.

Errors​

Each fallible service and output fails with its own Schema.TaggedError class. failure-report.ts renders by tag. There is no string conversion of a tagged error into a generic module failure.

Plugins​

A Plugin (src/plugin.ts) holds a name, the plugins it requires, a layer of the services it holds and an optional Output (see docs/AUTHORING.md). An output may fail with any tagged error; generatePlugins wraps each failure in OutputFailed { output, error }. The built-in plugins with a document of their own are:

The Documents column holds the default file names. The Layout service decides the folder: by default <schema>/<table>/ for a symbol of one entity, and the root for a shared symbol.

NameModuleDocuments
effectsrc/effect/output.tsschemas.ts, queries.ts, rpc.ts, http.ts, errors.ts
kyselysrc/kysely/output.tskysely/db.ts, kysely/queries.ts
typessrc/types-plugin/output.tstypes.ts
zodsrc/validators/zod.tszod.ts
valibotsrc/validators/valibot.tsvalibot.ts
arktypesrc/validators/arktype.tsarktype.ts
typiasrc/validators/typia.tstypia.ts
suggestion-reportsrc/reports/output.tsquery-suggestions.json

selectPluginNames decodes names with Schema.Literals. An unknown name or a duplicate name fails with PluginSelectionError. pluginsByName maps decoded names to Plugin values with Match. Missing core dependencies are TypeScript errors; there is no runtime missing-provider check.

sql-queries and typia require types. A transport (hono, express, elysia, trpc, orpc) requires its queries plugin (default kysely) and its validator plugin when set, and webApp() requires effect and kysely.

Every transport and effect require routes, a plugin with no output whose layer provides the Routes service (src/routes/service.ts). Routes.select(schemas) returns one HttpRoute per selected query plan and database function: method, path, parameters, body schema, status, absence and handler names (src/routes/model.ts). Paths come from src/routes/path.ts. The transports render routes; none derives them. One build of the layer serves every transport in a run (test/plugins.test.ts).

Services and resolution​

Plugin<R> is contravariant in R, the services the plugin holds, so a Plugin<Routes> is assignable to Plugin (= Plugin<never>). That assignment is the only erasure: requires holds erased plugins and only the resolver reads it. A plugin is built in typed steps (Plugin.make, requires, layer, output), each generic in one plugin's R, so the compiler checks what a layer or an output reads against what the earlier steps supplied. A plugin's layer is a function of the run's plugins by name (Replacements in src/plugin.ts). Plugin.requires merges the dependency's layer into the plugin's layer, and Plugin.output stores the output provided with the plugin's layer, so a plugin reads services only from the plugins it requires, as the run replaces them.

resolvePlugins (pure, returns a Result) turns the config list into one plugin per name: the config's plugins, then the plugins they require in depth-first order. A listed plugin replaces every required plugin of the same name, because files link by plugin name. Each requires edge resolves by name (requiredLayer in src/plugin.ts): where the run lists a different plugin of the required name, the edge merges that plugin's layer after the required one, so its services win by tag over the required plugin's in every dependent's layer and output, at any depth and in any config order. A dependent's own later Plugin.layer step still wins over both, as the most local choice. The required plugin still builds and supplies every service the replacement does not provide, so a dependent's type holds without trusting the replacement. Inside the replacement its own name is not replaced again, so a replacement may require and wrap the plugin it replaces. A service the replaced plugin derives inside its own layer from a replaced service keeps the replaced plugin's value. Effect builds one layer object once per run, in the first context that asks for it, so a layer object is a step of one plugin only; plugins share a service by requiring the plugin that holds it. A wrapping replacement builds its steps over the plugin it wraps, and every other plugin sees the wrapper. Two different required plugins of one name fail with PluginConflict until the config lists one.

generatePlugins builds the layers of every resolved plugin, with coreLayer, under one Effect.provide around the run. A plugin's own provide of a layer that this provide already built reuses it, so each layer builds once.

The Effect output keeps an internal EffectCatalogs service for codecs and procedures shared by the symbol declarations. That layer is provided only inside effectOutput.

One SymbolRegistry serves a run. Each registry output declares its exported symbols first (capability <output>:<kind>:<name>, name, file, kind), then every output links its imports by capability, so output order does not matter. A capability nobody declared fails with UnknownCapability at link time; importOptional returns Option.none instead. Two symbols collide only when they export the same name from the same file.

assembleDocuments merges contributions and fails with DuplicateDocumentPath when two sources write the same path.

Core Models​

  • SemanticModel (src/semantic/build.ts) builds three models from the catalog facts. The relational model holds entities, fields, relations and enums. The index model holds indexes. The security model holds what the visitor may do. It fails with Visitor role not found or Unknown role referenced when the snapshot does not fit.
  • Suggestions (src/suggestions/build.ts) lists the candidates. A candidate is accepted when one of its readings has no rejections. A rejected candidate becomes a notice with a reason. The reasons are no-privilege, no-supporting-index, nullable-key, partial-index, no-updatable-columns, not-updatable and missing-required-column (src/suggestions/schema.ts). Policy expressions are never read. A candidate records only its rls flag.
  • Selection (src/selection/build.ts) chooses the candidates to generate. Query rules run first, in order. The last matching rule sets the state. Smart tags (@omit) run after the rules and always win (src/selection/tags.ts). The builder asks Names for the name of each candidate and fails when two selected candidates share a name.
  • Plans (src/plans/build.ts) turns each selected, named candidate into a query plan. A plan holds the operation, the projection, the parameters, the predicate, the order, the limit and the cardinality. Parameter names must be unique within a plan.

Candidate kinds​

KindOperationCardinalityKey source
find-by-keyselectmaybe-onea unique index on column keys
list-keysetselect, ordered by the key, with a cursor and limitmanya unique index on column keys
list-by-foreign-keyselectmanya foreign key
list-by-indexselect, filtered by equality on a leading prefix of an index, ordered by the other index columns (in the index's direction) then the primary key, with a cursor and limitmanya btree index with no predicate, plain column keys and no nullable order column
insertinsertonenone
update-by-keyupdatemaybe-onea unique index on column keys
delete-by-keydeletemaybe-onea unique index on column keys

The candidate id names the kind, the entity and the key fields, for example find-by-key:app_public.comments:app_public.comments.id. Two sources that agree on those three values merge into one candidate (src/suggestions/build.ts).

Output Detail​

The effect output​

  • src/effect/schema.ts defines the catalog schemas.
  • src/shapes/build.ts builds the shape catalog from the query plans and the relational model. src/effect/build.ts derives the codec catalog from it.
  • src/effect/render.ts renders schemas.ts. Each shape becomes an Effect v4 Schema.Struct, and each enum becomes Schema.Literals.
  • src/effect/procedures.ts renders queries.ts. Each plan becomes one function that runs SQL through SqlClient.
  • src/effect/errors.ts renders errors.ts. It declares NotFound (HTTP 404) and StoreFailure (HTTP 500). The error classes carry no SQL text and no underlying cause.
  • src/effect/rpc.ts renders rpc.ts. It declares one RPC per procedure and a handler layer, ApiHandlers.
  • src/effect/http.ts renders http.ts. It declares one HttpApiGroup per entity and one handler layer per group. Each handler maps SqlError and SchemaError to StoreFailure. The find and update handlers also map a missing row to NotFound.

The generated Effect code imports effect/http-api, effect/rpc and effect/sql and uses Schema.Literals. The generator package itself depends on Effect 4.0.6. The consumer that compiles the output must install Effect v4. packages/generated-check/package.json pins effect 4.0.6.

The kysely output​

  • src/kysely/db.ts renders kysely/db.ts. It declares one interface per table and a DB interface keyed by schema.table names.
  • src/kysely/queries.ts renders kysely/queries.ts. Each plan becomes one function that takes a Kysely<DB> instance and returns a Kysely query.
  • src/kysely/catalog.ts builds the procedure catalog. It fails when two plans share a name.

The generated Kysely code imports the kysely package. The consumer must install it. packages/generated-check/package.json pins kysely 0.29.7.

Database functions​

An exposed database function (see the functions section of the report) becomes a call plan, a FunctionPlan in QueryPlanCatalog.functions. It is separate from an entity query plan (src/plans/build.ts).

  • Name. The export name comes from the functionName inflector (camelCase of the database name). A @name smart tag wins. A function name shares uniqueness with the entity query names. A clash is a NamesError.
  • Arguments. Each argument becomes one property of the request. An argument with a default is optional, and an absent one is left out of the call. The call passes arguments by name (schema.fn(a => $1)), so a function with an unnamed argument is not exposed. The effect, kysely and sql-queries outputs build the call in src/render/sql.ts. The validator outputs declare one request schema per function that takes arguments.
  • Result. A set-returning function returns a list. A function that returns one row or composite returns it or nothing (maybe-one). A function that returns void has no payload. A scalar result is a value, null unless the return type is a not-null domain. A function with OUT parameters or a composite type that is not a table returns a record. It gets a result shape named <Name>Result, which the types and validator outputs declare.
  • Kind. A volatile function is a mutation. A stable or immutable function is a query.
  • Routes. Every function is a POST /functions/<kebab name>, whatever its kind. The body holds the request. The Effect output declares a databaseFunctions HTTP group and an RPC per function. The hono, express and elysia outputs validate the body with the request schema, and trpc and orpc use a query for a stable function and a mutation for a volatile one. A void function answers 204. A maybe-one result that is absent answers 200 with a null body in every transport, and its success schema is NullOr in the Effect HttpApi and rpc, because a function may return null as a value. An entity find route keeps its 404. A record whose columns are all null reads as absent, because the call selects as r where not (r is null). The schemas option of a transport selects functions by their own schema.
  • Query rules. QueryRules apply to entity queries only. A function is exposed by the visitor's USAGE on its schema, its EXECUTE privilege and the @omit smart tag, and no rule filters it.

Opt-in suggestion report​

src/reports/output.ts defines the suggestion-report output. It writes query-suggestions.json. The CLI default is effect only. A caller that uses the library passes the name suggestion-report in the output list.

The report also has a functions section. It lists each exposed database function as a query (stable or immutable) or a mutation (volatile). It lists the exposed SECURITY DEFINER functions under bypassesRowSecurity, because they run with their owner's rights. It gives one notice for each function left out, with the reason no-execute, unsupported-signature, row-argument or overloaded. src/semantic/functions.ts decides this. A function the visitor may not execute is one whose EXECUTE grantees (PUBLIC included) the visitor does not reach. The code that generates queries from functions does not exist yet.

Security Model​

The visitor role controls which queries exist. The role name is a required input. There is no default (src/cli.ts, src/semantic/build.ts).

What the visitor holds​

  • Reachable roles. The visitor, PUBLIC, and every role that the visitor reaches through membership. A role marked NOINHERIT keeps its own grants, but it does not pass on the grants of the roles it belongs to (inheritedRoles, src/semantic/build.ts).
  • Table and column privileges. A privilege counts when a grant to a reachable role names it. A column grant and a table grant merge.
  • Owner. The visitor holds all four privileges on a table when it reaches the table owner and the table ACL has no entry for the owner.
  • Superuser. A superuser holds all four privileges on every table.

Row-level security​

  • A superuser, a role with BYPASSRLS, or a visitor that reaches the table owner skips row-level security. The owner case applies only when the table does not use FORCE ROW LEVEL SECURITY.
  • When row-level security applies, the model keeps only the policies whose roles include a reachable role. The model records the policy name, command and permissive flag.
  • Policy USING / WITH CHECK node trees are parsed in src/node-tree.ts (from polqual / polwithcheck). A session conjunct is a top-level equality between a table column and a zero-argument stable or immutable function. Those conjuncts land on PolicySnapshot.using / withCheck. Column defaults that are exactly such a call land on ColumnSnapshot.defaultFunction (from adbin).
  • A column is session-bound for a command when every applicable permissive policy for that command has the same session conjunct on it (sessionBindings on the security entity). Restrictive policies do not break a binding. Owner, superuser and BYPASSRLS visitors get no bindings.

Candidate gates​

src/suggestions/build.ts applies these gates. Each gate that fails adds a rejection.

Every candidate also requires USAGE on its entity's schema, granted to the visitor, PUBLIC or a role the visitor inherits. Without it the only rejection is no-schema-usage. A snapshot with no schema facts is not gated.

CandidateRequires
find-by-key, list-keysetA unique index with column keys, no partial predicate, no nullable key column, and select on every column of the table
list-by-indexA btree index with no predicate and column keys. Each leading prefix is one list, and the empty prefix is one list unless the index is unique. A nullable order column, or a table with no primary key, becomes a notice. A foreign-key list on the same columns merges into the index list
list-by-foreign-keyA btree or hash index with no predicate that leads with the foreign key columns in order, and select on every column
insertinsert on the table
update-by-keyThe key gates, select on every key column, and a writable non-key column
delete-by-keyThe key gates, select on every key column, and delete on the table

A read candidate needs select on every column of the table. One unreadable column removes all read candidates for that table. An update or delete needs select on the key columns because Postgres reads those columns in its WHERE clause.

Session-bound key or foreign-key columns are removed from the candidate's key. The remaining fields keep the candidate when the original key was unique (RLS fixes the bound column among visible rows). When every key column is bound for find, update or delete, the candidate keeps an empty key and becomes a current-row procedure (updateCurrentUsers, route /users/current); only one fully bound unique key per entity and command is kept (primary key if bound, else the first by index id). The plan predicate for that procedure is session-eq terms (WHERE id = app_public.current_user_id()), not an empty WHERE, so a role that bypasses RLS still touches only the session row. A fully bound foreign-key list is rejected with reason session-bound. An insert omits a column bound for insert when its defaultFunction matches the binding function; the generated SQL leaves that column to the default and RLS.

Writable and selectable columns​

  • mayWrite (src/suggestions/writable.ts) is true when the visitor holds the privilege the caller asks for (insert or update) on the column, and the column is neither generated nor GENERATED ALWAYS.
  • projectionOf (src/plans/build.ts) lists the columns the visitor may select. A plan's projection holds those columns.
  • Smart tags have two sources: PostgreSQL comments and the tags section of the config file (src/selection/tag-config.ts). SemanticModel merges the section into the comments of the catalog before it reads any comment: a rule's tags replace the comment's tags of the same name, and its description replaces the comment's prose. One parser (parseSmartTags) and one placement check (misplacedTag) serve both sources. The config wins when both set a tag. A rule that matches no object, an unknown tag name, and a tag on an object it does not apply to (@brand on a table, @omit on a domain) fail the build.
  • A column smart tag does not change which columns a plan reads or writes. @name sets the parameter name as written. @omit removes the candidates whose key columns include that column (src/selection/build.ts).
  • A view is an entity with kind view or materialized view. writes holds the commands pg_relation_is_updatable reports, and each column carries pg_column_is_updatable; a command outside writes is rejected as not-updatable. A view has no constraints, so @primaryKey a, b and @foreignKey (a) references [schema.]table [(x)] on its comment declare them (src/semantic/build.ts); a key tag on a table fails the build. A declared key cites declared-key evidence. Declared key columns and columns tagged @notNull read as not null, as in PostGraphile. A key tag on a column comment, or @notNull on a relation comment, fails the build. The security model's runsAs is owner for a view without security_invoker and for a materialized view, because they read base tables as their owner and the visitor's policies on those tables do not apply.

Determinism​

Given the same source snapshot, project specification and visitor role, the generator must return the same document set. The code uses these rules:

  • Builders sort every list whose input order has no meaning. Examples are tables, fields, candidates, names and plans. The sort compares strings by code unit.
  • Duplicate names and paths are errors. They are never resolved by order.
  • Effect Layer memoization builds each core service once per run. Output name order does not change the documents.

These tests check the rules:

  • test/graph.test.ts: unknown output names and duplicate document paths fail composition.
  • test/semantic.test.ts: the list order does not depend on the order of the input facts.
  • test/plugin.test.ts: the effect output keeps the established document contents.
  • test/suggestions.test.ts: the same catalog comes out for any table order.
  • test/reports.test.ts: the report bytes do not depend on output name order.

Validation​

validateSnapshot in src/snapshot.ts checks each document path. A path must be normalized and relative. It must not contain .. or start with the reserved .pg-sourcerer directory. It must not repeat, and it must not differ from another path only by case. It must not have another document as its parent.

Then the validator typechecks all TypeScript documents together. It uses the TypeScript compiler API with strict on. It resolves imports between generated files from the documents in memory. It resolves other imports from the packages that are installed near the output root. Each diagnostic names the file and the line.

A snapshot with no diagnostics becomes a ValidatedSnapshot. commitSnapshot accepts only that type. The publisher itself is the SnapshotPublisher service (src/snapshot.ts).

Publication​

commitSnapshot in src/snapshot.ts merges the validated documents into the editable tree at outputDir. The previous pristine output is recorded in outputDir/.pg-sourcerer/manifest.json. Commit this hash manifest with the application code.

src/publish/plan.ts plans each file merge without I/O. The publisher considers paths in the base or the new output and leaves other files alone. src/publish/manifest.ts prepares SHA-256 hashes and decodes the manifest with an Effect Schema. TypeScript files compare top-level declarations; other files compare whole-file hashes. Every merge completes in memory before any write, so a parse failure leaves the tree and manifest unchanged.

src/publish/tree.ts writes the application files first, then the hash manifest. A retry recovers interrupted writes by comparing against the old hashes. Conflicts write two-way markers into the application files and advance the manifest. There is no line merge inside a declaration both sides changed, because the manifest stores no base text. The report lists written, created and deleted paths and every conflict. The CLI exits with code 1 when the report has conflicts. Merged text is not reformatted.

See Editable generated code for merge rules, ordering and recovery.

The tests in test/publish-plan.test.ts cover the file decisions. test/publish.test.ts exercises edits, schema additions, conflicts, deletion and the parse-before-write guarantee through the real publisher.

Generation And Fixture Scripts​

  • packages/generated-check/scripts/regenerate.ts deletes each example target and runs the CLI directly into that target, starting with an empty tree and manifest. The package typecheck script runs this script and then tsc. The root typecheck script runs that package script too. The CLI's own validation step rejects any document that does not compile, so a bad generator change fails the run.
  • packages/pg-sourcerer/scripts/introspection-fixture.ts rebuilds examples/introspection.json. It uses the Docker and psql helpers in scripts/fixture-db.ts. The helpers start a postgres:18-alpine container and apply fixtures/demo-migrations/: the reset script runs as postgres, and the other scripts run as the owner role. The script then dumps the catalog with makeIntrospectionQuery() from @danielfgray/pg-introspection.

Non-Goals​

  • Live loading or unloading of outputs.
  • Incremental updates to a run that already finished.
  • Sandboxing of builder code. Builders are trusted pure functions.
  • File or database access from a core service. Only src/runtime.ts and src/snapshot.ts touch the file system. Only src/pg-source.ts reads the catalog format.
  • A shared AST layer for all output languages. Each output renders its own text.