Agent sync schema declaration

This document defines the schema declaration that a sync agent sends before its first row batch and whenever the source schema changes. It covers the arbitrary POS/SR sync product and deliberately remains source-agnostic.

The declaration complements the existing row contract at POST /v1/sync/rows. It does not replace the server-side canonical snapshots already used by the AI semantic workflow.

1. Why this exists

Every row batch currently declares one table’s columns and canonical types. That is sufficient to create and fill the PostgreSQL mirror, but it cannot describe the database as a whole:

primary, unique and foreign keys;
relationships between tables;
source-native types, precision and nullability;
tables that are empty during the first sync;
source product, SQL dialect and release;
schema changes that happen before a changed table produces another row.

Those details materially improve automatic semantic-model generation for an unknown POS database. They also let the backend detect a modified SR installation instead of assuming that every installation of a named release has exactly the same schema.

2. Decisions

The agent sends canonical JSON, not executable DDL.
What a source cannot describe is declared absent, never omitted. “No foreign keys exist” and “foreign keys were not collected” are different facts and the semantic workflow acts differently on them.
The endpoint is device-authenticated and derives restaurant_id from the device token. A client-supplied tenant ID is never accepted.
The server treats the complete payload as untrusted input.
Source DDL may be retained locally by the agent for diagnostics, but is not required by the wire contract and is never executed by the backend.
The snapshot contains schema metadata only. It contains no rows, samples, credentials or connection strings.
A server-computed SHA-256 over canonicalized schema shape is the identity and idempotency key. The submitted hash is only an assertion and must match.
The declared schema and the observed mirror schema are different records:
- declared — what an authenticated agent reports;
- observed — what the backend verifies in the PostgreSQL mirror.
Wren and semantic-model generation use the observed schema. Agent metadata may enrich it with source-native keys and relationships only after every referenced table and column has been verified in the mirror.
Additive declarations may create empty mirror tables and add columns through the existing constrained DDL builder. Incoming SQL text is never executed.
A snapshot never automatically drops a table/column, changes a physical type, or executes a source default expression.

3. Endpoint

POST /v1/sync/schema
Authorization: Bearer <device token>
Content-Type: application/json
Content-Encoding: gzip                     # optional, same policy as row sync

/v1/sync/schema is the canonical route. No POS-specific alias is needed.

The endpoint follows the existing agent status discipline:

status	meaning	agent action
`200`	this exact shape was already accepted	record the acknowledgement and continue
`201`	a new schema version was accepted	record the acknowledgement and continue
`400`, `413`, `415`, `422`	malformed or permanently unsupported declaration	stop row delivery for this schema and surface an operator error
`401`, `403`	device authentication/authorization failed	retain data and retry after provisioning is fixed
`409`	declared hash/version conflicts with the row stream or another active declaration	retain data, refresh discovery and retry
`5xx`	temporary backend/storage failure	retry with backoff

The backend returns 2xx only after the declaration and its server-computed hash have committed.

4. Version 1 JSON contract

Example:

{
  "format_version": 1,
  "snapshot_id": "sch_01JBE3A8B4DBQ7W6MZTYV4S9YE",
  "schema_hash": "b52b554466682e515e17d9fca7f8967bcff669c0e9ad0d1ddbc0ab6b1bc2a0a3",
  "captured_at": "2026-07-30T12:30:00Z",
  "venue_id": "ven_0123456789ab",
  "device_id": "dev_main",
  "agent_version": "1.8.0",
  "source": {
    "dialect": "mssql",
    "product": "softrestaurant",
    "product_version": "9.0.4",
    "database_schema": "dbo"
  },
  "tables": [
    {
      "name": "orders",
      "source_name": "ORDERS",
      "kind": "table",
      "columns": [
        {
          "name": "order_id",
          "source_name": "ORDER_ID",
          "ordinal": 1,
          "type": "int64",
          "source_type": "bigint",
          "nullable": false
        },
        {
          "name": "total",
          "source_name": "TOTAL",
          "ordinal": 2,
          "type": "decimal",
          "source_type": "numeric(18,2)",
          "nullable": false,
          "precision": 18,
          "scale": 2
        },
        {
          "name": "closed_at",
          "source_name": "CLOSED_AT",
          "ordinal": 3,
          "type": "datetime",
          "source_type": "datetime2",
          "nullable": true
        }
      ],
      "primary_key": ["order_id"],
      "unique_keys": [
        {
          "name": "uq_orders_external",
          "columns": ["external_id"]
        }
      ],
      "foreign_keys": [],
      "indexes": [
        {
          "name": "ix_orders_closed_at",
          "columns": ["closed_at"],
          "unique": false
        }
      ],
      "replication": {
        "strategy": "upsert",
        "key_columns": ["order_id"]
      }
    },
    {
      "name": "order_lines",
      "source_name": "ORDER_LINES",
      "kind": "table",
      "columns": [
        {
          "name": "line_id",
          "source_name": "LINE_ID",
          "ordinal": 1,
          "type": "int64",
          "source_type": "bigint",
          "nullable": false
        },
        {
          "name": "order_id",
          "source_name": "ORDER_ID",
          "ordinal": 2,
          "type": "int64",
          "source_type": "bigint",
          "nullable": false
        }
      ],
      "primary_key": ["line_id"],
      "unique_keys": [],
      "foreign_keys": [
        {
          "name": "fk_order_lines_order",
          "columns": ["order_id"],
          "referenced_table": "orders",
          "referenced_columns": ["order_id"]
        }
      ],
      "indexes": [],
      "replication": {
        "strategy": "replace_groups",
        "key_columns": ["line_id"],
        "replace_key": "order_id"
      }
    }
  ]
}

4.1 Top-level fields

field	required	rules
`format_version`	yes	integer `1`; unknown versions are rejected
`snapshot_id`	yes	agent-generated tracing ID; not the idempotency key
`schema_hash`	yes	lowercase hex SHA-256; server recomputes and compares it
`captured_at`	yes	UTC RFC 3339 timestamp
`venue_id`	yes	must match the venue provisioned for the authenticated device
`device_id`	yes	correlation only; authenticated device identity remains authoritative
`agent_version`	yes	bounded release string
`source`	yes	source identity and SQL dialect, never connection material
`tables`	yes	complete discovered source shape, including empty tables

No restaurant_id, database hostname, username, password or connection string is allowed.

4.2 Source

{
  "dialect": "mssql",
  "product": "softrestaurant",
  "product_version": "9.0.4",
  "database_schema": "dbo"
}

dialect is an allowlisted identifier such as mssql, postgresql, mysql or dbf.
product and product_version are descriptive and do not select trusted server code.
database_schema is source metadata. It is not a PostgreSQL schema name to be interpolated or executed.
Database/server names are omitted because they add little semantic value and may expose customer infrastructure.

4.3 Names

name is the canonical name used by /v1/sync/rows and therefore the physical mirror name.
source_name is the original catalogue spelling for diagnostics and semantic evidence. It never reaches SQL.
Canonical names use the same NFC normalization, lowercase requirement, identifier character rules and 63-byte limit as the row endpoint.
Every table/column/key/index name is unique within its scope.
All column references use canonical name, never source_name.

4.4 Column

Required fields:

{
  "name": "total",
  "source_name": "TOTAL",
  "ordinal": 2,
  "type": "decimal",
  "source_type": "numeric(18,2)",
  "nullable": false
}

type uses the same canonical type vocabulary as row sync:

bool, int16, int32, int64, decimal, money, double,
datetime, date, bytes, guid, string

Optional bounded metadata:

{
  "max_length": 255,
  "precision": 18,
  "scale": 2,
  "generated": false,
  "default_expression": null,
  "comment": null
}

source_type, default_expression and comment are evidence only. They are never executed. Comments must pass the same credential/PII screening used by semantic profiling before they may be sent to an external LLM.

pg_type is deliberately absent. PostgreSQL mirror types are derived server-side through the existing canonical type mapping.

4.5 Keys and relationships

primary_key is an ordered array of existing column names.
Each unique_keys[].columns is a non-empty ordered array.
A foreign key has equal non-zero source and target column counts.
Every local column must exist in the declaring table.
Every referenced table/column must exist in the same snapshot.
Foreign-key actions and source constraint expressions are out of scope; the mirror does not reproduce them.
Index metadata is semantic/performance evidence. The backend does not automatically reproduce source indexes from this payload.

4.6 Replication declaration

replication binds the whole-schema declaration to the existing row contract.

Allowed strategies:

upsert, replace_groups, replace_all

Rules:

upsert requires key_columns;
replace_groups requires key_columns and replace_key;
replace_all may omit both;
every row batch for this schema must declare the same columns, keys and replacement semantics.

A row batch carries the acknowledged schema_hash. Rows are checked against the mirror, not against a declaration, so the hash never refuses a batch. The server records which device delivers under which declaration (§9), and logs a hash the venue never declared: its declarations were lost from the sync database.

delete_keys remains a row operation, not a table replication strategy.

5. Canonicalization and hashing

The agent computes schema_hash, but the server is authoritative.

Before hashing:

normalize canonical identifiers to NFC;
normalize dialect, product and product_version to trimmed lowercase;
sort tables by canonical name;
sort columns by ordinal, then require ordinals to be unique;
preserve column order inside primary/unique/foreign keys;
sort key, relationship and index objects by canonical name;
encode JSON without insignificant whitespace and with stable object keys.

The exact shape hash includes:

format_version;
source dialect/product/version/schema;
tables, columns and canonical/source types;
nullability and bounded type metadata;
primary/unique/foreign keys;
indexes;
replication strategy and keys.

It excludes:

snapshot_id, captured_at, venue_id, device_id, agent_version;
comments;
source default expressions;
source-native display names;
raw DDL;
row counts, samples and other volatile evidence.

Changing excluded metadata must not force a semantic rebuild.

5.1 Producer notes (pinned by the shared vectors)

The steps above leave three byte-level decisions a non-Go producer can legitimately get wrong. They are pinned here and exercised by the shared fixtures (internal/tests/testdata/agent-schema-snapshot.json and agent-schema-snapshot-nasty.json, with their digests pinned in Go golden tests on the backend):

String escapes follow Go’s encoding/json with HTML escaping disabled: " and \ as \" and \\; the short forms \b, \f, \n, \r and \t for the five control characters that have one (verified against go1.25.4: backspace U+0008 and form feed U+000C DO get their short forms); other control characters below U+0020 as lowercase \u00XX (U+0000 itself is rejected at validation because JSONB cannot store it); U+2028 and U+2029 ALWAYS escaped as \u2028 / \u2029, HTML setting notwithstanding. Every other code point — ñ, CJK, anything above U+001F — is raw UTF-8 bytes.
Sort order is unsigned UTF-8 byte order (Go’s string <), applied to the NFC form. It is NOT UTF-16 code-unit order: for characters above the BMP the two disagree, so a .NET producer must sort with StringComparer.Ordinal-style byte comparison over the UTF-8 encoding, not the default string comparison.
Numbers in the hashed shape are integers only (format_version, ordinal, max_length, precision, scale), encoded as bare decimal digits: no fractions, no exponents, no trailing .0. Booleans are true / false.

6. First-sync protocol

Connect to the source catalogue.
Discover and canonicalize the complete configured source schema.
Validate it locally using the same name/type/key rules as row export.
Compute the exact hash.
If the hash differs from the last acknowledged hash, send POST /v1/sync/schema.
Wait for a 2xx acknowledgement.
Attach the acknowledged schema_hash to subsequent row envelopes.
Start/resume row delivery.

Example response:

{
  "status": "accepted",
  "schema_hash": "b52b554466682e515e17d9fca7f8967bcff669c0e9ad0d1ddbc0ab6b1bc2a0a3",
  "schema_version": 17,
  "change_kind": "initial",
  "semantic_status": "pending"
}

Repeated delivery of the same exact hash returns the existing schema_version with status: "unchanged".

7. Mirror application

The backend may use a valid declaration to create an empty mirror shape before rows arrive, but only through existing safe primitives:

quote every identifier;
map canonical types with the existing PgType function;
create a missing schema/table;
add missing nullable columns;
create only the mirror key required for an accepted replication strategy.

The declaration must never cause the backend to:

execute incoming SQL;
drop a table, column or index;
rename a physical object;
narrow/change an existing type;
apply a source default, trigger, procedure, view body or computed expression;
create a foreign key against replicated data;
grant privileges.

Breaking changes are recorded, ingestion remains retry-safe, and destructive mirror reconciliation is an explicit future operation.

Declared tables are created in the mirror before any row arrives (SyncRepository.EnsureDeclaredTables). A table empty at the source never produces a batch, and without this it would be missing rather than empty: a query against it would fail with “relation does not exist” instead of returning no rows.

It goes through EnsureTable, so the DDL, the PgType mapping and the key handling are the ones a batch would use - a shape built by a second code path is how a column ends up bigint here and numeric there, and a type that disagrees with the mirror is 42804, terminal for the batch that finds it.

The redelivery path ensures too. A redelivery is the only signal a till gives after its first declaration, and the mirror may have lost its tables since - dropped by an operator, restored from a backup, purged - so restarting the agent is a real repair rather than an “unchanged” that fixes nothing.

8. Declared versus observed schema

Store the declaration in the reconstructible sync database with:

restaurant_id
device_id
venue_id
schema_version
schema_hash
format_version
canonical_declaration JSONB
received_at
last_seen_at
status

After mirror application, build the existing CanonicalSchema from sync_tables and PostgreSQL catalog metadata. Enrich it with a declared key or relationship only when all referenced objects match the physical mirror.

The observed snapshot keeps the existing properties:

stable, server-computed fingerprint;
initial / compatible / breaking classification;
unchanged snapshots are idempotent;
breaking changes mark the current semantic version stale;
the old published semantic model continues to serve until an administrator explicitly starts rebuilding;
a rebuilt model is published only after Wren validation and the agreed control-question gate pass.

9. Multiple devices

Multiple devices for one restaurant may submit the same snapshot. This is the normal case:

identical hashes are idempotent;
delivery time and device identity do not change the schema version;
the backend retains which devices observed each hash, by declaring it or by delivering rows under it.

The supported topology is many devices reading one logical POS database. Connecting devices from a second or otherwise different source database to the same on-prem installation is an unsupported operator configuration. The backend detects and reports the resulting conflict; it does not merge independent databases or attempt to decide which one contains authoritative business rows.

If devices submit different shapes:

store both observations;
never delete mirror objects based on either declaration;
accept a purely additive superset through the constrained mirror builder;
mark incompatible concurrent shapes as a schema conflict;
continue using the last verified observed snapshot for AI;
expose the conflict to the administrator as an unsupported source mismatch.

Choosing a primary terminal, reconciling duplicated business rows and POS failover are explicitly outside this task.

10. Security and privacy

Use existing device-token authentication and restaurant binding.
Reject unexpected JSON fields (DisallowUnknownFields).
Apply the existing decompressed request-body cap.
Preserve the current maximum of 500 tables and 400 columns per table; add a bounded total-column count and bounded metadata strings before implementation.
Validate all cross-references before any mirror mutation.
Never log the full body at normal log levels.
Reject credential-like field names or values in diagnostic metadata.
Never include source rows, sampled values, connection strings or credentials.
Do not forward source_name, comments or defaults to an external LLM until the local privacy filter accepts them.
Do not trust schema_hash, device/venue identity, source version or relation claims merely because the request is authenticated.

11. What a declaration must be able to say

Absent is not the same as uncollected

sys.foreign_keys answers the question for SQL Server. A directory of free DBF tables has no such concept - a .dbc container may carry relations, and nothing else will. The contract must therefore let a source say “this database has no foreign keys” distinctly from “foreign keys were not collected”, or the semantic workflow cannot tell a flat database from an unfinished agent, and will keep asking an administrator to confirm relationships that provably do not exist.

The same applies to unique constraints beyond index evidence, and to precision guarantees the DBF header cannot make.

Any database, not only SoftRestaurant

The product syncs an arbitrary POS mirror, so collection may assume neither a scheme file nor a naming convention. Where a scheme exists it renames and narrows, and both spellings survive - that is what name and source_name are for. Where none exists, canonical names collapse to source names and the snapshot must still be complete.

12. Object-level refusals

A 422 is terminal by design: the agent stops row delivery for the schema. That is right when the document cannot be interpreted, and far too heavy when one column of one table is wrong - a text column whose max_length INFORMATION_SCHEMA spells as 2147483647, a child table whose replace_groups names no key, or a column name that reads as a credential would each stop every table.

The server may not withhold

Withholding cannot be done here. §5 makes the server recompute the hash and require it to equal the submitted one, and rows later travel under the acknowledged hash to be placed by the committed shape. A server that silently dropped a column would store a shape that no longer matches the hash its rows carry - breaking the one identity this whole ordering exists to protect.

So withholding belongs to the agent, where the mechanism is: withheld_columns (§4.5) exists precisely so a table filtered by the operator does not look re-shaped. The refusal tells the agent what to withhold, beside the prose for an operator.

Which refusals are which

Document-level - remain terminal. Nothing can be dropped to make these interpretable, or the sender is not who it claims to be: unknown format_version; schema_hash malformed or mismatched; captured_at not UTC; venue_id/device_id; a NUL anywhere; unknown dialect; tables missing, not an array or malformed; the document limits on tables and total columns; a table declared twice; unknown fields; trailing data.

Object-level - withhold and continue. One table or one column is unusable while the rest of the database is described correctly: a column with an unmirrorable name, declared twice, a bad ordinal, an unknown canonical type, or max_length/precision/scale out of bounds; a table with an unknown kind, no columns, or over the per-table column limit; a key or index naming an undeclared column, naming one twice, having no columns, or declared twice; a replication block missing key_columns, missing replace_key, or naming an unknown strategy.

A withheld table must also be withheld from row delivery. Otherwise rows arrive for an object the declaration does not describe, which is the failure §4.6 exists to prevent. The agent controls both paths, so this is its obligation and is testable there.

Mechanism

The server answers 422 with a machine-readable list beside the prose: refused: [{table, column, reason}].
The agent withholds the named objects, counts them in withheld_columns, recomputes the hash and re-declares once.
A second refusal of the same object is terminal.
Bounded: one automatic re-declaration per pass, and a threshold on how much may be withheld - a server refusing a third of the tables is a broken source, not a few odd columns, and an operator has to see that rather than a quietly thinner semantic model.

Visibility is not optional. What was withheld goes to the agent’s log, to withheld_columns, and to the admin status API. A semantic model that silently lost a third of its columns is worse than a sync that stopped.

13. Out of scope

Selecting a primary POS terminal.
Business-row deduplication between devices.
Merging two different source databases into one restaurant mirror.
Executing vendor DDL.
Destructive mirror migrations.
Automatically publishing a model merely because the agent declared a schema.