SysMARA ORM — AI-First Database Layer for TypeScript

A database layer designed for machine-readable operations, capability-scoped queries, and impact-aware migrations.

Philosophy

Traditional ORMs — Prisma, Drizzle, TypeORM — were designed for human developers. They hide SQL behind abstraction layers, provide convenient query builders, and let you write any query your code can express. This is powerful for humans, but opaque for AI agents. An agent reading Prisma client calls cannot determine which operations are authorized, which invariants should hold, or what the blast radius of a schema change would be.

SysMARA ORM inverts this design. Every aspect of the database layer is explicit, bounded, and machine-readable.

Schema IS the System Graph

There is no separate ORM schema to maintain. SysMARA ORM reads your entity specs from SystemSpecs — the same specs that define your system graph — and generates SQL directly. When you change an entity in your YAML specs, the database schema changes. One source of truth.

Every Query is a Capability

You cannot make arbitrary database queries. Every operation must be scoped to a declared capability. If a capability create_user declares access to the user entity, you can only query user through that capability. This means AI agents cannot accidentally make unauthorized queries — the ORM refuses operations that are not declared in the system specs.

Invariants = DB Constraints

Invariants declared in your specs are not just runtime checks. The schema generator converts them into actual database constraints — UNIQUE, CHECK, NOT NULL, and foreign key constraints. The database enforces your business rules even if application code has a bug.

Machine-Readable Operation Log

Every database operation is recorded as a structured JSON entry in the operation log. AI agents can read this log to understand exactly what the system has done: which capability triggered the operation, which entity was affected, which invariants were checked, how long it took, and how many rows were affected.

Impact-Aware Migrations

Before applying any schema change, the migration engine diffs the previous and current specs, identifies every affected capability and invariant, computes a risk level, and determines whether human review is required. No blind ALTER TABLE statements.

Getting Started

Configure SysMARA ORM in your sysmara.config.yaml:

database:
  adapter: sysmara-orm
  provider: postgresql
  connectionString: env(DATABASE_URL)

Usage

Initialize the ORM

import { SysmaraORM } from 'sysmara/database/adapters/sysmara-orm/orm';

const orm = new SysmaraORM(
  { adapter: 'sysmara-orm', provider: 'postgresql' },
  specs  // your parsed SystemSpecs
);

Execute a capability

The capability() method resolves a capability from the system specs, validates the input against the capability's contract, and routes to the appropriate entity operation. Every call is logged.

// Execute a capability as a database operation
const result = await orm.capability('create_user', {
  email: 'alice@example.com',
  role: 'admin',
});

// The ORM validates that 'create_user' exists in specs,
// that 'email' and 'role' are declared inputs,
// and logs the operation with invariant tracking.

Use typed repositories

Repositories are scoped to a single entity within a capability's boundaries. All operations through the repository are validated against the capability contract and logged to the operation log.

// Get a typed repository scoped to a capability
const repo = orm.repository<User>('user', 'create_user');

// Standard CRUD operations — all capability-scoped and logged
const user = await repo.findOne({ email: 'alice@example.com' });
const newUser = await repo.create({ email: 'bob@example.com', role: 'member' });
const updated = await repo.update('abc-123', { role: 'admin' });
await repo.delete('abc-123');

// Without explicit capability — uses first capability that declares the entity
const repo2 = orm.repository<User>('user');

If you try to create a repository for an entity that no capability declares access to, the ORM throws an error. There are no backdoor queries.

Generate SQL schema

const sql = orm.generateSchema();
// Returns complete CREATE TABLE statements with:
// - Provider-specific types (PostgreSQL, MySQL, SQLite)
// - NOT NULL, UNIQUE, CHECK constraints from entity field constraints
// - Foreign keys detected from *_id field naming
// - UUID primary keys with provider-appropriate defaults

Example output for PostgreSQL:

-- GENERATED BY SYSMARA ORM
-- Provider: postgresql

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE TABLE IF NOT EXISTS "user" (
  "id" UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "role" VARCHAR(255) NOT NULL,
  "created_at" TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  CONSTRAINT chk_user_role_enum CHECK ("role" IN ('admin', 'member', 'viewer'))
);

Operation Log

The operation log is what makes SysMARA ORM AI-first. Every database operation — whether executed through orm.capability() or a repository method — is recorded as a structured JSON entry. AI agents can read this log to audit system behavior, debug issues, and reason about what happened.

Log entry structure

{
  "id": "op_1_1710410400000",
  "capability": "create_user",
  "entity": "user",
  "operation": "insert",
  "invariants_checked": ["email_must_be_unique"],
  "affected_fields": ["email", "role"],
  "timestamp": "2026-03-14T12:00:00.000Z",
  "duration_ms": 12,
  "affected_rows": 1,
  "sql_template": "INSERT INTO \"user\" (\"email\", \"role\") VALUES ($1, $2)"
}

Querying the log

// Get all operations
const allOps = orm.getOperationLog();

// The OperationLog instance supports filtered queries
const log = orm.getOperationLogInstance();
const userOps = log.getByEntity('user');
const inserts = log.getByOperation('insert');
const capOps = log.getByCapability('create_user');
const count = log.size();

Migration Engine

The migration engine diffs two versions of your SystemSpecs and produces an impact-aware migration plan. Unlike traditional migration tools that only generate SQL, SysMARA's migration engine knows the system graph — it tracks which capabilities, invariants, modules, policies, and flows are affected by every schema change.

Generating a migration plan

const plan = orm.planMigration(previousSpecs);

// plan contains:
// {
//   steps: [...],
//   affectedCapabilities: ['create_user', 'update_user'],
//   affectedInvariants: ['email_must_be_unique'],
//   riskLevel: 'low',
//   requiresReview: false
// }

Migration step types

Risk classification

The migration engine computes a risk level based on the most destructive operation in the plan:

• Low: Only additive changes (new tables, new columns).
• Medium: Column type or nullability changes.
• High: Column drops — data will be lost.
• Critical: Table drops — entire entity data will be lost.

Migrations classified as high or critical automatically set requiresReview: true.

Example migration plan output

{
  "steps": [
    {
      "action": "add_column",
      "entity": "user",
      "field": "phone",
      "details": "Add column \"phone\" (string) to \"user\"",
      "sql": "ALTER TABLE \"user\" ADD COLUMN \"phone\" VARCHAR(255);"
    },
    {
      "action": "create_table",
      "entity": "notification_preference",
      "details": "Create new table \"notification_preference\"",
      "sql": "-- See full schema for complete DDL"
    }
  ],
  "affectedCapabilities": ["create_user", "update_user", "get_user_profile"],
  "affectedInvariants": ["email_must_be_unique"],
  "riskLevel": "low",
  "requiresReview": false
}

Impact analysis

The migration engine can also produce a full impact surface for a migration plan, identifying affected modules, policies, flows, tests, and generated artifacts:

const engine = orm.getMigrationEngine();
const impact = engine.analyzeImpact(plan, specs);

// impact.affectedModules     → ['users']
// impact.affectedPolicies    → ['admin_full_access']
// impact.affectedFlows       → ['user_onboarding']
// impact.affectedTests       → ['tests/create_user.test.ts']
// impact.generatedArtifacts  → ['routes/create_user.ts', 'sysmara-orm/schema/user.sql']

SQL generation

const sql = engine.generateSQL(plan);
// Produces a complete migration script:
// - Header with risk level, affected capabilities, and invariants
// - Wrapped in BEGIN/COMMIT transaction
// - Each step as a commented SQL statement

Why AI-First Matters

The difference between SysMARA ORM and traditional ORMs is not about features — it is about who the ORM is designed for.

Traditional ORMs hide complexity from humans

Prisma's prisma.user.create() and Drizzle's db.insert(users).values() hide SQL behind convenient APIs. This is great for human developers who understand the system. But when an AI agent calls prisma.user.create(), there is no record of why that query ran, which capability authorized it, or which invariants should have been checked.

SysMARA ORM makes everything transparent

Bounded queries prevent AI mistakes

When an AI agent works with a traditional ORM, it can generate any query the ORM supports. There is no guardrail preventing the agent from querying tables it should not access or modifying data in ways that violate business rules.

SysMARA ORM enforces capability boundaries at the ORM level. If the create_user capability declares access to the user entity, a repository scoped to that capability can only operate on user. The agent cannot accidentally query billing data through a user capability.

The operation log enables AI reasoning

Because every operation is logged with its capability, entity, invariants, and SQL template, an AI agent can:

• Audit what the system has done without parsing application logs.
• Detect anomalies by comparing operations against capability contracts.
• Debug issues by tracing operations back to the capability that triggered them.
• Optimize by analyzing query patterns and durations.
• Verify invariant enforcement by checking that expected invariants were validated.

Related Concepts

• Database Adapters — the pluggable adapter system SysMARA ORM is part of
• Entities — the specs that SysMARA ORM generates schema from
• Capabilities — every ORM query is scoped to a capability
• Invariants — become database constraints in SysMARA ORM
• CLI Reference — sysmara db commands for schema and migrations