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.Servicethat holds a value supplied once per run withLayer.succeedat the composition root. The three inputs areDatabaseSnapshot,VisitorRoleandQueryRules(src/inputs.ts). - Core service: a
Context.Servicethat holds the computed model for one run. Each hasmakeand onestatic readonly layer. The core services areSemanticModel,Names,Suggestions,Selection,PlansandShapes. - Plugin: a name, the plugins it requires, the services it holds and an optional output, an
Effectthat builds a list of documents from the core services (src/plugin.ts). The built-in names areeffect,kysely,types,zod,valibot,arktype,typia,sql-queries,hono,express,elysia,trpc,orpcandsuggestion-report(src/pipeline.ts). - Candidate: one query that the visitor's schema supports, such as
find-by-keyon 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:
- Acquire the source.
snapshotFromFile(src/source.ts) decodes the input file once with a schema union: aCatalogSnapshotJSON, or PostgreSQL introspection output read throughsrc/pg-source.ts.snapshotFromConnectionrunsmakeIntrospectionQuery()on a live database and decodes the result through the same introspection schema. The CLI takes exactly one of--inputor--connection(DATABASE_URLwhen neither is given). The browser entry (src/browser.ts) usesDatabaseSnapshot.fromExecutor, which takes rows from any SQL executor such as PGlite and imports no platform module. - Generate documents.
generateSnapshotinsrc/pipeline.tsprovides the input layers andcoreLayer, runs the selected output effects, and merges their documents. - Validate.
validateSnapshotinsrc/snapshot.tschecks paths and typechecks the documents. - Publish.
commitSnapshotinsrc/snapshot.tswrites 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):
| Service | Shape | Source |
|---|---|---|
DatabaseSnapshot | CatalogSnapshot (decoded catalog) | input file or live database |
VisitorRole | role name string | CLI argument |
QueryRules | readonly rule list | library 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.
| Service | Module | Yields | Builder |
|---|---|---|---|
SemanticModel | src/semantic/service.ts | DatabaseSnapshot, VisitorRole | buildSemanticModels |
Names | src/names/service.ts | SemanticModel, Inflection | buildNames |
Suggestions | src/suggestions/service.ts | SemanticModel | buildQuerySuggestions |
Selection | src/selection/service.ts | Suggestions, SemanticModel, Names, QueryRules | selectQueries |
Plans | src/plans/service.ts | Selection, SemanticModel, Names | buildQueryPlans |
Shapes | src/shapes/service.ts | Plans, SemanticModel, Names, DomainOptions | buildShapeCatalog |
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.
| Name | Module | Documents |
|---|---|---|
effect | src/effect/output.ts | schemas.ts, queries.ts, rpc.ts, http.ts, errors.ts |
kysely | src/kysely/output.ts | kysely/db.ts, kysely/queries.ts |
types | src/types-plugin/output.ts | types.ts |
zod | src/validators/zod.ts | zod.ts |
valibot | src/validators/valibot.ts | valibot.ts |
arktype | src/validators/arktype.ts | arktype.ts |
typia | src/validators/typia.ts | typia.ts |
suggestion-report | src/reports/output.ts | query-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 withVisitor role not foundorUnknown role referencedwhen 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 areno-privilege,no-supporting-index,nullable-key,partial-index,no-updatable-columns,not-updatableandmissing-required-column(src/suggestions/schema.ts). Policy expressions are never read. A candidate records only itsrlsflag.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 asksNamesfor 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
| Kind | Operation | Cardinality | Key source |
|---|---|---|---|
find-by-key | select | maybe-one | a unique index on column keys |
list-keyset | select, ordered by the key, with a cursor and limit | many | a unique index on column keys |
list-by-foreign-key | select | many | a foreign key |
list-by-index | select, 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 limit | many | a btree index with no predicate, plain column keys and no nullable order column |
insert | insert | one | none |
update-by-key | update | maybe-one | a unique index on column keys |
delete-by-key | delete | maybe-one | a 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.tsdefines the catalog schemas.src/shapes/build.tsbuilds the shape catalog from the query plans and the relational model.src/effect/build.tsderives the codec catalog from it.src/effect/render.tsrendersschemas.ts. Each shape becomes anEffectv4Schema.Struct, and each enum becomesSchema.Literals.src/effect/procedures.tsrendersqueries.ts. Each plan becomes one function that runs SQL throughSqlClient.src/effect/errors.tsrenderserrors.ts. It declaresNotFound(HTTP 404) andStoreFailure(HTTP 500). The error classes carry no SQL text and no underlying cause.src/effect/rpc.tsrendersrpc.ts. It declares one RPC per procedure and a handler layer,ApiHandlers.src/effect/http.tsrendershttp.ts. It declares oneHttpApiGroupper entity and one handler layer per group. Each handler mapsSqlErrorandSchemaErrortoStoreFailure. The find and update handlers also map a missing row toNotFound.
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.tsrenderskysely/db.ts. It declares one interface per table and aDBinterface keyed byschema.tablenames.src/kysely/queries.tsrenderskysely/queries.ts. Each plan becomes one function that takes aKysely<DB>instance and returns a Kysely query.src/kysely/catalog.tsbuilds 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
functionNameinflector (camelCase of the database name). A@namesmart tag wins. A function name shares uniqueness with the entity query names. A clash is aNamesError. - 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. Theeffect,kyselyandsql-queriesoutputs build the call insrc/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
voidhas 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 thetypesand validator outputs declare. - Kind. A
volatilefunction 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 adatabaseFunctionsHTTP 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. Avoidfunction answers 204. A maybe-one result that is absent answers 200 with a null body in every transport, and its success schema isNullOrin 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 selectsas r where not (r is null). Theschemasoption of a transport selects functions by their own schema. - Query rules.
QueryRulesapply to entity queries only. A function is exposed by the visitor's USAGE on its schema, its EXECUTE privilege and the@omitsmart 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 markedNOINHERITkeeps 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 useFORCE 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 CHECKnode trees are parsed insrc/node-tree.ts(frompolqual/polwithcheck). A session conjunct is a top-level equality between a table column and a zero-argument stable or immutable function. Those conjuncts land onPolicySnapshot.using/withCheck. Column defaults that are exactly such a call land onColumnSnapshot.defaultFunction(fromadbin). - A column is session-bound for a command when every applicable permissive policy for that command has the same session conjunct on it (
sessionBindingson the security entity). Restrictive policies do not break a binding. Owner, superuser andBYPASSRLSvisitors 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.
| Candidate | Requires |
|---|---|
find-by-key, list-keyset | A unique index with column keys, no partial predicate, no nullable key column, and select on every column of the table |
list-by-index | A 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-key | A btree or hash index with no predicate that leads with the foreign key columns in order, and select on every column |
insert | insert on the table |
update-by-key | The key gates, select on every key column, and a writable non-key column |
delete-by-key | The 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 (insertorupdate) on the column, and the column is neither generated norGENERATED 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
tagssection of the config file (src/selection/tag-config.ts).SemanticModelmerges 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 (@brandon a table,@omiton a domain) fail the build. - A column smart tag does not change which columns a plan reads or writes.
@namesets the parameter name as written.@omitremoves the candidates whose key columns include that column (src/selection/build.ts). - A view is an entity with
kindview or materialized view.writesholds the commandspg_relation_is_updatablereports, and each column carriespg_column_is_updatable; a command outsidewritesis rejected asnot-updatable. A view has no constraints, so@primaryKey a, band@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 citesdeclared-keyevidence. Declared key columns and columns tagged@notNullread as not null, as in PostGraphile. A key tag on a column comment, or@notNullon a relation comment, fails the build. The security model'srunsAsisownerfor a view withoutsecurity_invokerand 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.tsdeletes each example target and runs the CLI directly into that target, starting with an empty tree and manifest. The packagetypecheckscript runs this script and thentsc. The roottypecheckscript 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.tsrebuildsexamples/introspection.json. It uses the Docker andpsqlhelpers inscripts/fixture-db.ts. The helpers start apostgres:18-alpinecontainer and applyfixtures/demo-migrations/: the reset script runs aspostgres, and the other scripts run as the owner role. The script then dumps the catalog withmakeIntrospectionQuery()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.tsandsrc/snapshot.tstouch the file system. Onlysrc/pg-source.tsreads the catalog format. - A shared AST layer for all output languages. Each output renders its own text.