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

> Discover how OpenMAIC integrates with PostgreSQL for its document store. This guide provides a complete implementation with automatic activation via DATABASE_URL.

- Repository: [MAIC/OpenMAIC](https://github.com/THU-MAIC/OpenMAIC)
- Tags: how-to-guide
- Published: 2026-09-11

---

**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`](https://github.com/THU-MAIC/OpenMAIC/blob/main/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`](https://github.com/THU-MAIC/OpenMAIC/blob/main/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`](https://github.com/THU-MAIC/OpenMAIC/blob/main/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:

```bash
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:

```ts
// 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:

```ts
// 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`](https://github.com/THU-MAIC/OpenMAIC/blob/main/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.