How the OpenMAIC PostgreSQL Backend Manages Revision Counters and Access Control

The OpenMAIC PostgreSQL backend uses dedicated revision tables with database triggers to maintain monotonic version counters for stages and scenes, while enforcing access control through owner-bound store instances that automatically scope all queries to a specific owner_id.

OpenMAIC stores every document structure—comprising stages and their associated scenes—within a PostgreSQL backend that must track state changes and enforce security boundaries simultaneously. The implementation relies on two complementary mechanisms: automated revision counters managed at the database level via triggers, and application-level access control that binds query execution to specific user contexts. Understanding these systems is essential for developers integrating with the storage layer or deploying multi-tenant document workflows.

Revision Counter Architecture

Dedicated Revision Tables

The backend maintains two companion tables to track versioning: document_stage_revision for individual stages and document_scene_revision for each (stage, scene) pair. These tables store monotonic revision numbers that increment automatically whenever underlying data changes, providing a consistent versioning mechanism across the document hierarchy.

According to the source code in packages/@openmaic/storage/src/document/pg.ts (lines 1557‑1662), these tables are defined alongside the primary document storage schema, ensuring that every stage and scene has a corresponding revision entry that remains synchronized with data mutations.

Database Triggers and the Lock-Order Invariant

PostgreSQL triggers openmaic_bump_stage_revision() and openmaic_bump_scene_revision() handle automatic counter increments. These triggers fire on INSERT, UPDATE, and DELETE operations against the main document_stages and document_scenes tables (trigger bodies defined at lines 1669‑1692 and 1695‑1699).

To prevent deadlocks during concurrent updates, the triggers enforce a strict lock-order invariant: they always acquire the stage revision lock before the scene revision lock (stage → scene). This ordering ensures that concurrent transactions modifying related documents complete without circular wait conditions.

Each trigger execution also emits a pg_notify event on the channel openmaic_agent_event_wakeup with a payload containing the stage ID ({kind:'stage',stageId}). This notification mechanism allows asynchronous agents listening on the same channel to wake immediately when relevant document changes occur, enabling real-time synchronization without polling.

Bulk Operation Optimization

For scenarios requiring high-volume data imports or migrations, the backend supports a session-local configuration switch openmaic.suppress_stage_notify. When enabled, this setting silences the PostgreSQL notification broadcasts while still updating the revision counters, preventing notification flooding during bulk loads while maintaining data consistency.

Access Control Mechanisms

Owner-Bound Store Pattern

The PgDocumentStore class implements access control through an owner-bound architecture. The forOwner(ownerId) method (lines 91‑95) returns a new store instance pre-configured with a specific ownerId, ensuring that all subsequent queries automatically filter by that owner identifier.

// Bind the store to a specific user (owner-scoped)
const userStore = store.forOwner('user-42');

When an owner is bound, every read and write operation becomes automatically scoped to that owner_id, effectively creating a tenant-isolated view of the document space without requiring manual query filters at the call site.

Scoped Query Predicates

Internally, the store generates parameterized SQL predicates through the scopePredicate(alias, ownerParameter) helper (lines 96‑99). This function produces the appropriate WHERE clause fragments to restrict queries to the bound owner.

The corresponding scopeParams(stageId?) method (lines 101‑109) constructs the parameter list for these predicates, handling both global owner scoping and stage-specific queries. This centralized parameter generation ensures consistent access control application across all document operations.

Mutation Guards

To prevent accidental unscoped modifications, the requireOwner() method (lines 15‑20) acts as a runtime guard. It throws an error if any operation that mutates folders or documents is attempted without an owner bound to the store instance.

All folder-related APIs—including createFolder, listFolders, renameFolder, deleteFolder, and moveDocumentToFolder—invoke requireOwner() before executing their SQL statements. This architectural guarantee ensures that users can only view or modify resources explicitly associated with their authenticated owner_id.

Practical Implementation Example

The following TypeScript example demonstrates initializing the PostgreSQL document store, binding an owner, and interacting with revision-aware document operations:

import { PgDocumentStore } from '@openmaic/storage';
import { createClient } from '@vercel/postgres';   // any Queryable implementation

// 1️⃣ Initialise a generic store (no owner restriction)
const client = createClient({ connectionString: process.env.DATABASE_URL! });
const store = new PgDocumentStore(client, {
  // withTransaction must supply a fresh transactional connection each call
  withTransaction: async (body) => await body(client),
});

// 2️⃣ Bind the store to a specific user (owner-scoped)
const userStore = store.forOwner('user-42');

// 3️⃣ Read the freshness manifest – includes stage revision + per-scene revisions
const manifest = await userStore.readFreshnessManifest('stage-abc');
// manifest.rev -> stage revision
// manifest.scenes -> [{ id, order, rev }, …]

// 4️⃣ Create a folder (owner-scoped)
const { folder, reused } = await userStore.createFolder('folder-1', 'My Courses');

// 5️⃣ Update a document; the underlying triggers bump the revision counters automatically
await userStore.saveDocument(myMaicDocument);

// 6️⃣ After a save, retrieve the new stage revision from the manifest
const fresh = await userStore.readFreshnessManifest(myMaicDocument.stage.id);
console.log('New stage revision:', fresh?.rev);

Key implementation details demonstrated above include the forOwner method establishing tenant isolation, and readFreshnessManifest retrieving the current revision numbers that database triggers maintain automatically.

Summary

• Revision counters are maintained in dedicated PostgreSQL tables (document_stage_revision, document_scene_revision) and incremented automatically via database triggers that enforce a stage-before-scene lock ordering to prevent deadlocks.
• Real-time notifications are broadcast via pg_notify on the openmaic_agent_event_wakeup channel whenever revisions change, enabling asynchronous agent wake-up, with optional suppression via openmaic.suppress_stage_notify for bulk operations.
• Access control operates through owner-bound store instances where the forOwner() method creates scoped views that automatically append owner_id filters to all queries.
• Mutation safety is guaranteed by the requireOwner() guard, which prevents write operations on folders and documents unless an owner context is explicitly established.
• All revision and access control logic resides in packages/@openmaic/storage/src/document/pg.ts, with specific implementation details at the line ranges cited throughout this article.

Frequently Asked Questions

How does OpenMAIC prevent deadlocks when updating revision counters?

The PostgreSQL triggers openmaic_bump_stage_revision() and openmaic_bump_scene_revision() enforce a strict lock-order invariant where stage revision locks are always acquired before scene revision locks. This sequential ordering (stage → scene) eliminates circular wait conditions that would otherwise cause deadlocks during concurrent document updates, as implemented in packages/@openmaic/storage/src/document/pg.ts (lines 1669‑1699).

Can I disable revision change notifications during bulk imports?

Yes. Set the session-local variable openmaic.suppress_stage_notify to silence the pg_notify broadcasts on the openmaic_agent_event_wakeup channel while still allowing the triggers to increment revision counters. This optimization prevents notification flooding during large data migrations while maintaining data consistency, as supported by the trigger logic in the PostgreSQL backend.

How does the owner scoping mechanism prevent cross-tenant data access?

When you invoke forOwner(ownerId), the PgDocumentStore instance binds all subsequent queries to that specific owner_id through the scopePredicate() and scopeParams() helpers (lines 96‑109). These methods automatically generate SQL WHERE clauses that restrict results to the bound owner, ensuring that queries cannot access or modify documents belonging to other tenants even if the stage or scene IDs are known.

What happens if I attempt to mutate a document without binding an owner?

The requireOwner() method (lines 15‑20) throws an error if any mutating operation—such as creating folders, moving documents, or saving stage data—is called on a store instance without an owner bound via forOwner(). This runtime check enforces the architectural requirement that all document mutations must occur within an explicit user context, preventing anonymous or unscoped modifications to the document hierarchy.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}