Skip to content

README.md

Latest commit

 

History

History
337 lines (279 loc) · 17.3 KB

README.md

File metadata and controls

337 lines (279 loc) · 17.3 KB
Raw

@kozou/api

Stable (Kozou v1.0; composite foreign keys as of v1.1; RPC actions as of v1.6). The REST wire format, query grammar, and OpenAPI extensions documented below are a stable contract: they will not change incompatibly without a major release — see Stability.

Kozou's own REST layer. Given a SchemaContext (from @kozou/introspect

  • @kozou/core) and a PostgreSQL connection, it serves the tables and views of your database as a REST API, driven entirely by your DDL and COMMENT metadata — no hand-written route code.

This is the in-house data layer that Kozou v1.0 makes the default backend. The Admin UI talks to it through the same DataAdapter seam (@kozou/core) it already uses, so swapping the data layer is not a breaking change for UI code.

API

Service

  • GET / — service info (name, version) and the list of available resources.
  • GET /openapi.json — an OpenAPI 3.1 document for the whole API (see OpenAPI).

Reading

  • GET /<resource> — list rows of a table or view. Returns { rows, total, page, pageSize }. Supports:
    • pagination — page (1-based) and pageSize (LIMIT / OFFSET, capped);
    • sort — sort=field.asc,other.desc;
    • filter — <column>=<op>.<value> (see Filtering);
    • free-text search — search=<text> (ILIKE across text columns);
    • embedding — embed=<relation-chain> (see Embedding).
  • GET /<resource>/<id> — fetch a single table row by primary key. For a composite primary key, <id> is the key columns in declaration order, comma-joined in one path segment (/order_lines/42,3); embed is supported here too. Returns the row, or 404.
  • GET /<resource>?as=options&label=<col>&fields=<a,b>&q=<text>&limit=<n> — lightweight relation-select lookup. Returns { options: [{ id, label }] }. For a composite-key resource each id is a JSON array of key components in primary-key declaration order. To address that row's item route, build the {id} path segment from the components: percent-encode each component and join them with an unescaped comma. A component value that itself contains a comma cannot be represented in the item route (a documented limitation of the comma-joined segment).

Writing

  • POST /<resource> — create a row from a JSON body; returns 201 + the created row. An empty body inserts a row of column defaults.
  • PATCH /<resource>/<id> — update the supplied columns; returns the row, or 404.
  • DELETE /<resource>/<id> — delete by primary key; returns the deleted row, or 404.

Writes are rejected on views (405) and on unknown columns (400).

Filtering

List filters use a <column>=<op>.<value> grammar:

operator meaning
eq / neq equal / not equal
gt / gte / lt / lte range comparisons
like / ilike pattern match (* is the wildcard)
in membership — in.(a,b,c)
is is.null / is.notnull / is.true / is.false

A bare value (status=paid) is shorthand for eq and stays backward compatible. Repeating a column ANDs its filters (e.g. a gte + lt range). A value that itself looks like an operator can be forced to equality with an explicit eq. prefix (name=eq.gt.5). Operators are a fixed allowlist, the column is checked against the schema, and every value is passed as a bound parameter — nothing is interpolated into SQL text. Values for the common scalar families (integer, numeric / float, boolean) are validated up front and rejected with a 400; values for other types are enforced by PostgreSQL.

Embedding

embed=<relation-chain> inlines related rows as nested JSON, on both list and item reads:

  • forward (to-one) and reverse (to-many) relations, mixed in one request — including composite (multi-column) foreign keys, which join on every column pair; a forward composite relation is selected by its referenced table name, a reverse one by the child table name, and x-kozou-embeds advertises only relations whose selector is unambiguous;
  • dot-separated chains up to 5 deep (embed=order.customer.region);
  • comma-separated for several relations (embed=customer,lines);
  • up to 25 distinct relations per request, and up to 100 child rows inlined per parent for a to-many relation.

To-many embeds are rendered as a JSON array ordered by the child's primary key. Many-to-many is expressed by naming the junction explicitly (embed=link.far); there is no automatic flattening of junction tables.

OpenAPI

GET /openapi.json returns an OpenAPI 3.1 document generated from the schema. Database COMMENTs drive it: table / view / column descriptions become schema descriptions, CHECK / ENUM members become enum, and Kozou's @-annotations become vendor extensions:

  • @ai: notes → x-kozou-ai
  • the resolved widget → x-kozou-widget
  • @policy: advisories → x-kozou-policy
  • embeddable relations → x-kozou-embeds (with cardinality)

The document models the schema, the x-kozou-* metadata, and the list / item / CRUD surface faithfully: create and update carry their own request schemas (<resource>.CreateInput requires only NOT NULL columns without a default; <resource>.UpdateInput is a partial subset), the as=options relation-select mode is documented on the collection path with its alternate response shape, and the advertised embed hints match what the server resolves at request time.

RPC actions

Beyond the table/view CRUD surface, @kozou/api can compile opt-in Postgres functions into callable actions — POST /rpc/<schema>.<fn> with a JSON body of named arguments. This is how the security model Kozou recommends (JWT → SET LOCAL ROLE → RLS + column GRANTs, with state changes funnelled through functions) stays reachable: the verb is exposed, the database still enforces who may run it.

Nothing is exposed by default. A function is compiled only when its COMMENT carries the @expose: rpc tag:

COMMENT ON FUNCTION approve_order(order_id uuid) IS
  'Approve an order and reserve stock.
   @ai: not idempotent — check status before re-calling.
   @expose: rpc';

Exposure ≠ permission. Whether a caller may actually run it is enforced by the PostgreSQL EXECUTE privilege under the request's role; a denial is 42501 → 403. Guardrails (loud build issues when a tagged function is skipped, never a silent gap):

  • PUBLIC EXECUTE is a hard skip. CREATE FUNCTION grants EXECUTE to PUBLIC by default, which would let anyone (incl. an anon role) call the endpoint. Such a function is not exposed unless intentionally opened — REVOKE EXECUTE … FROM PUBLIC and GRANT to the intended role, or declare it public with @expose: rpc public / api.rpc.allowPublicExecute.
  • SECURITY DEFINER needs a double opt-in. It runs as its owner and can bypass RLS, so it is exposed only when listed in api.rpc.allowDefiner and it declares an owner-safe SET search_path (owner-only schemas plus a trailing pg_temp).
  • Overloads / unsupported shapes (VARIADIC / polymorphic / unnamed args, unmappable returns, an overloaded schema.name) are loudly skipped.

Arguments are named (scalar / enum / FK-typed); a DEFAULT argument may be omitted. The return maps to the wire as: scalar → the value, single composite → an object, SETOF / RETURNS TABLE → an array, void → 204. Values are always bound parameters; argument names are validated against the function's signature.

The same exposed set is compiled to every surface: the OpenAPI document gains a POST /rpc/<schema>.<fn> operation (operationId: rpc.<schema>.<fn>, with the @ai / @policy advisory as x-kozou-*), the MCP server gains a describe_functions tool, and the Admin UI gains an "Actions" page. See the kozou package config for api.rpc.allowDefiner / allowPublicExecute.

Errors

Every non-2xx response carries the same envelope:

{ "error": { "code": "machine-readable", "message": "human-readable" } }

Database outcomes map to stable statuses. The raw database message is written to the server log, never into the response body (it can carry internal schema detail):

Database condition (SQLSTATE) HTTP code
insufficient privilege / row-level security (42501) 403 forbidden
unique violation (23505) 409 conflict
foreign-key violation (23503) 409 conflict
not-null violation (23502) 400 constraint_violation
check violation (23514) 400 constraint_violation
anything else 500 internal (generic message; detail in the server log)

Mapped messages are fully generic: the violated constraint or column name appears only in the server log, never in the response (an error can name objects outside the exposed surface — for instance a foreign key from a table that is not exposed — so identifiers are withheld across the board). Data exceptions (SQLSTATE class 22) are deliberately not mapped: mapping the whole class would relabel genuine kozou bugs as client errors. Instead, the client inputs that would otherwise raise one are validated before they reach the database — list filter and search values, item id segments, and write-body values all return a 400 up front when malformed. Pre-flight covers the scalar families with a reliable lexical form (the integer widths, the decimal/float family — including NaN, and ±Infinity for the float types — boolean, and uuid); a malformed value of another type — for example a bad date — still falls through to a 500, by design. Two further notes: in a write body only string-valued fields are pre-flighted (a non-string JSON value is left to PostgreSQL — a JSON number is already a valid numeric, an object targets a json column), and the check requires the canonical lexical form, so it will reject some redundant spellings PostgreSQL also accepts (a hex/underscored integer literal, an abbreviated boolean prefix) — send the plain decimal / true/false form.

With auth enabled, the auth layer adds its own statuses: missing token → 401 (or the configured anonRole takes over), invalid token → 401, disallowed role → 403.

Stability

Stable as of Kozou v1.0 (covered by semantic versioning — no incompatible change without a major release):

  • the REST envelopes — the list { rows, total, page, pageSize }, the item shape, and the create / update / delete return shapes;
  • the query grammar — pagination, sort, the <column>=<op>.<value> filter grammar, as=options, and embed;
  • the GET /openapi.json document — OpenAPI 3.1 plus the x-kozou-ai / x-kozou-widget / x-kozou-policy / x-kozou-embeds extensions;
  • the composite-foreign-key relation shape (as of Kozou v1.1) — a multi-column foreign key is a first-class relation. Where advertised in x-kozou-embeds it embeds, with the server joining on every column pair. A forward composite relation is selected by its referenced table name (advertised only when no other foreign key shares that target table); a reverse one is selected by the child table name (advertised only when the child has exactly one foreign key back and no forward relation shadows that name). The hint carries fields (the local foreign-key columns, in declaration order; the hint is an embed selector, not a join recipe), and as=options on a composite-key target returns array option ids whose components follow primary-key declaration order;
  • the auth boundary — JWT claims mapped to SET LOCAL ROLE (see below);
  • the error envelope { error: { code, message } } and the database-mapped status table (as of Kozou v1.1.1 — see Errors above);
  • the RPC actions wire (as of Kozou v1.6) — the REST POST /rpc/<schema>.<fn> endpoint (a named-arguments JSON body; the return-shape mapping scalar → the value, single composite → an object, a set (SETOF / RETURNS TABLE) → an array (objects for a row set, bare values for a scalar set), void → 204), the function metadata in the OpenAPI document (x-kozou-volatility / x-kozou-security, the @ai / @policy advisory as x-kozou-ai / x-kozou-policy, and per-argument x-kozou-widget / x-kozou-type / x-kozou-relation), and the MCP call execution tool, which shares the same argument and return-value shaping (representing a void return as an explicit "executed, no value" object instead of 204). The exposure rules (@expose: rpc, the PUBLIC-EXECUTE hard skip, the SECURITY DEFINER double opt-in) were already settled; this adds the wire to the guarantee.

Scalar leaf values are serialized by the PostgreSQL driver's default JSON output — notably numeric and bigint are rendered as JSON strings (to preserve precision). This applies to both the table/view and RPC surfaces.

Still evolving (not yet covered by the stability guarantee):

  • the @kozou/codegen output (a separate package, still experimental).

Scope: default coverage vs PostgREST opt-out

@kozou/api covers the relational REST surface most schemas need; Kozou v1.0 makes it the default backend. Deployments that need a feature in the "opt-out" column can stay on (or switch back to) the PostgREST adapter (adapter: postgrest) — see the migration notes in the kozou package. The intent is no silent gap: the in-house backend by default, PostgREST as a deliberate opt-out.

Capability @kozou/api (v1.0 default) Notes
Table CRUD ✅ (incl. composite primary keys) item routes (get/update/delete by id) need a primary key — single or composite; a primary-key-less table gets list + create only
Views ✅ read-only / list no item-by-id, writes are 405, no embedding from a view
Embed 1:1 / many-to-1 / 1-to-many ✅ embed= (mixed direction, multi-hop) forward to-one + reverse to-many
Many-to-many △ name the junction and chain (embed=link.far) no automatic junction flattening
Relation-select ✅ as=options&label=&q= composite-key targets return array option ids (components in key declaration order)
Filter operators ✅ eq / neq / gt / gte / lt / lte / like / ilike / in / is
Sort / pagination ✅ sort, page / pageSize
COMMENT-native OpenAPI 3.1 (x-kozou-*) ✅ Kozou's differentiator — PostgREST treats COMMENTs as opaque text
JWT + RLS (SET LOCAL ROLE) ✅
RPC (Postgres functions) ✅ opt-in (@expose: rpc) see RPC actions; compiled to REST + OpenAPI + MCP + Admin UI. Wire is stable as of v1.6
Full-text search (fts) ❌ opt-out approximate with ilike
Vertical select (column projection) ❌ opt-out always returns all columns
Writable views (INSTEAD OF) ❌ opt-out views are read-only
Upsert / bulk insert ❌ opt-out
Automatic M:N flattening ❌ opt-out name the junction instead

Security boundary

Kozou is a resource server and enforcement layer, not an identity provider: it verifies tokens and switches PostgreSQL role so your RLS decides access, but user registration, login, and token issuance are delegated to an external provider (Supabase Auth recommended; Auth0 / Clerk via the JWKS endpoint below). Shipping a user-management / login library is a non-goal.

By default the API ships with no authentication and binds to 127.0.0.1 (like the MCP HTTP server); it prints a loud warning when bound to a non-loopback host. Run the unauthenticated server only inside a trusted boundary (local dev, a docker-compose network).

Opt-in JWT + row-level security. Pass an auth config (and a pool) to startApiServer to require a signed JWT on every request. Kozou verifies the token (an HS256 shared secret, an RS256 public key, or a provider's remote JWKS endpoint — exactly one), then runs each request inside a transaction on a dedicated connection under SET LOCAL ROLE <role-from-claim>, with the claims published via set_config('request.jwt.claims', …, true) — so your own Postgres RLS policies decide what each request can read and write. Kozou authenticates and switches role; it does not generate policies. A missing or invalid token gets 401; a token whose role is not permitted gets 403.

Set auth.anonRole to allow anonymous access: a request that carries no Authorization header runs under that role (with empty claims) so your RLS policies decide what an anonymous caller sees, instead of a 401. Only a fully absent header is anonymous — a present but invalid/expired token is still 401, never silently downgraded. The login role must be GRANTed membership in the anonymous role.

Set auth.jwt.jwksUri to verify against a provider's remote JWKS endpoint (Auth0, Clerk, Supabase, …) instead of a static key: the verification key is selected by the token's kid, fetched once, cached, and refreshed when the provider rotates keys.

For a trusted same-host caller that has no end user to obtain a token from (the bundled Admin UI under kozou dev), signServiceToken mints an HS256 token claiming a given role, signed with the same secret the server verifies.

Safety

  • Table / view / column identifiers are validated against the introspected SchemaContext (an allowlist) before any query is built, and are quoted defensively.
  • All user-supplied values are passed as parameterized query arguments; values are never interpolated into SQL text.

License

Apache 2.0