Core Concepts

Table constraints and foreign keys

Synapsor enforces the relational integrity constraints that agent workflows should not be allowed to improvise.

Overview

Use constraints for relationships that must stay true even when SQL is reached through an agent capability, SDK call, or operator workflow.

The current single-node engine supports primary keys, unique columns, not-null columns, and immediate foreign-key checks. Foreign keys are catalog-backed and enforced by execution; they are not documentation-only syntax.

Advanced foreign-key modes are intentionally not public yet: composite keys, self-references, cascade, set-null/default, deferrable checks, CHECK constraints, and table-level UNIQUE(a, b) remain unsupported until they have full executor and storage coverage.

Developer notes

Use foreign keys for relationships such as proposal-to-expense, approval-to-proposal, evidence-to-run, and receipt-to-expense.
Expect child INSERT and child key UPDATE to require a visible parent row.
Expect parent key UPDATE and parent DELETE to fail while visible child rows reference the old parent key.
Use hidden bindings and RBAC for who can touch rows; use constraints for relationships the database must reject if impossible.

Supported constraint syntax

SQL

-- PRIMARY KEY, UNIQUE, NOT NULL, and immediate FK checks are enforced.
CREATE TABLE employees (
  employee_id VARCHAR PRIMARY KEY,
  tenant_id VARCHAR NOT NULL,
  email VARCHAR UNIQUE
);

-- employee_id must reference a visible employees(employee_id) row.
CREATE TABLE expenses (
  expense_id VARCHAR PRIMARY KEY,
  employee_id VARCHAR REFERENCES employees(employee_id),
  state VARCHAR NOT NULL
);

CREATE TABLE expense_approvals (
  approval_id VARCHAR PRIMARY KEY,
  expense_id VARCHAR NOT NULL,
  reviewer_id VARCHAR NOT NULL,
  CONSTRAINT fk_approval_expense
    FOREIGN KEY (expense_id) REFERENCES expenses(expense_id)
);

Examples

sql

-- REFERENCES employees means employees(employee_id) because employee_id is the parent primary key.
CREATE TABLE expenses (
  expense_id VARCHAR PRIMARY KEY,
  employee_id VARCHAR REFERENCES employees
);