# How the OpenMAIC PostgreSQL Backend Manages Revision Counters and Access Control

> Discover how the OpenMAIC PostgreSQL backend manages revision counters and access control using revision tables, triggers, and owner-bound store instances. Learn about its robust data management.

- Repository: [MAIC/OpenMAIC](https://github.com/THU-MAIC/OpenMAIC)
- Tags: internals
- Published: 2026-09-12

---

**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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L1557-L1662)), 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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L1669-L1692) and [1695‑1699](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L1695-L1699)).

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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L91-L95)) returns a new store instance pre-configured with a specific `ownerId`, ensuring that all subsequent queries automatically filter by that owner identifier.

```typescript
// 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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L96-L99)). This function produces the appropriate `WHERE` clause fragments to restrict queries to the bound owner.

The corresponding **`scopeParams(stageId?)`** method (lines [101‑109](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L101-L109)) 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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L15-L20)) 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:

```typescript
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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L1669-L1699)).

### 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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L96-L109)). 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](https://github.com/THU-MAIC/OpenMAIC/blob/main/packages/@openmaic/storage/src/document/pg.ts#L15-L20)) 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.