Can OpenMAIC Use PostgreSQL for Its Document Store? Complete Implementation Guide

Yes, OpenMAIC ships with a production-ready PostgreSQL-backed document store that activates automatically when a DATABASE_URL environment variable is configured.

OpenMAIC is a multi-agent platform that requires robust persistence for stages, scenes, and metadata. According to the OpenMAIC source code, the platform includes a fully-featured PostgreSQL document store implementation that replaces the default IndexedDB storage when deployed server-side.

How OpenMAIC Implements PostgreSQL Document Storage

Core Architecture and Interfaces

The primary implementation resides in packages/@openmaic/storage/src/document/pg.ts. This file exports the PgDocumentStore class, which implements three critical interfaces: DocumentStore, DocumentFolderStore, and StageFreshnessManifestStore used throughout the platform.

These abstractions allow the rest of the codebase to interact with storage agnostically. Whether running in a browser with IndexedDB or on a server with PostgreSQL, the consuming code calls the same methods without knowing the underlying persistence mechanism.

Automatic Backend Detection

The server runtime automatically selects the PostgreSQL backend when the DATABASE_URL environment variable is present. In lib/server/agent-runtime/runner.ts, the startup logic detects this configuration and wires PgDocumentStore into the agent runtime instead of the browser fallback.

Key Features of the PostgreSQL Document Store

ACID Transaction Safety

PgDocumentStore requires a withTransaction hook injected by the PostgreSQL driver. According to lines 45-47 of pg.ts, this hook "checks out a fresh connection, opens a transaction, pins every query to it, then commits or rolls back." This design guarantees ACID semantics for all document reads and writes, ensuring data integrity during concurrent agent operations.

Multi-Tenant Security with Owner Scoping

All operations are bound to a specific ownerId through the forOwner and requireOwner methods. This prevents cross-user data leakage by filtering every query by the owner identifier. The implementation mirrors the multi-tenant security model used throughout the MAIC platform, making it suitable for SaaS deployments where strict isolation between user data is mandatory.

Schema Management and Database Triggers

The PostgreSQL schema is defined as a single DOCUMENT_PG_SCHEMA string (lines 58-125 of pg.ts). The ensureDocumentSchema function applies this idempotently during startup.

The schema includes database triggers named openmaic_bump_stage_revision and openmaic_bump_scene_revision that:

• Maintain per-stage and per-scene revision counters automatically
• Emit pg_notify events to wake agents when document changes occur

Configuring OpenMAIC for PostgreSQL

Environment Configuration

Deployments must set the DATABASE_URL environment variable to enable the PostgreSQL backend. As documented in packages/docs/content/docs/deployment.mdx, the connection string follows the standard PostgreSQL URI format:

DATABASE_URL=postgres://openmaic:openmaic-dev@postgres:5432/openmaic

When this variable is present, the server automatically bypasses IndexedDB and initializes the PostgreSQL store.

Instantiating the Store Programmatically

For custom integrations or testing scenarios, you can instantiate PgDocumentStore directly with a connection pool and transaction wrapper:

// Example: instantiate a PgDocumentStore using @vercel/postgres
import { createPool } from '@vercel/postgres';
import { PgDocumentStore } from '@openmaic/storage';

// 1️⃣ Create a PostgreSQL connection pool
const pool = createPool({
  connectionString: process.env.DATABASE_URL!, // e.g. postgres://user:pw@host:5432/db
});

// 2️⃣ Provide a withTransaction hook that the store expects
const withTransaction = async <T>(fn: (db: any) => Promise<T>) => {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
};

// 3️⃣ Create the store (optionally bound to an owner)
const store = new PgDocumentStore(pool, { withTransaction });

// 4️⃣ Use the store like any DocumentStore
await store.saveDocument({
  stage: { id: 'stage-1', name: 'Demo', createdAt: Date.now(), updatedAt: Date.now() },
  scenes: [{ id: 'scene-1', order: 0, content: 'Hello' }],
});
const doc = await store.loadDocument('stage-1');
console.log(doc?.stage.name); // → "Demo"

Working with Owner-Scoped Documents

To enforce multi-tenancy in application code, bind the store to a specific user using the forOwner method:

// Example: bound to a specific user (owner) – useful for multi-tenant APIs
const userStore = store.forOwner('user‑123');

// Create a folder for the user
await userStore.createFolder('folder-abc', 'My Courses');

// List the user’s documents (only those they own)
const docs = await userStore.listDocuments();

This pattern ensures that userStore can only access documents where ownerId equals 'user-123', providing a secure boundary for multi-user applications.

Summary

• OpenMAIC includes a complete PostgreSQL document store implementation in packages/@openmaic/storage/src/document/pg.ts
• Activation requires only the DATABASE_URL environment variable; no code changes are needed
• The PgDocumentStore class implements DocumentStore, DocumentFolderStore, and StageFreshnessManifestStore interfaces for seamless integration
• Features ACID transactions via the withTransaction hook injected by the PostgreSQL driver
• Enforce multi-tenancy through forOwner and requireOwner methods that scope all queries to specific user IDs
• Uses PostgreSQL triggers and pg_notify for revision tracking and real-time agent wake-up mechanisms

Frequently Asked Questions

Does OpenMAIC require PostgreSQL, or can it use other databases?

OpenMAIC defaults to IndexedDB for browser environments and does not require PostgreSQL. However, for server-side deployments requiring ACID guarantees, persistent storage, and multi-tenant isolation, PostgreSQL is the supported backend. The codebase currently focuses on PostgreSQL for production server deployments.

How does OpenMAIC handle database migrations?

The platform applies the DOCUMENT_PG_SCHEMA string idempotently via ensureDocumentSchema. This runs automatically during server startup in lib/server/agent-runtime/runner.ts, creating tables, indexes, and triggers if they do not exist. No manual migration scripts are required for standard deployments.

Is the PostgreSQL document store suitable for multi-tenant SaaS applications?

Yes. The PgDocumentStore class implements owner-scoped methods (forOwner, requireOwner) that bind all queries to specific user IDs at the database level. This architectural design prevents cross-tenant data access by ensuring users can only read and write documents associated with their ownerId.

What PostgreSQL features does OpenMAIC use for real-time updates?

The implementation leverages pg_notify events emitted by database triggers (openmaic_bump_stage_revision, openmaic_bump_scene_revision) to wake agents when document revisions change. This mechanism provides efficient, event-driven updates without requiring constant polling of the database.

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