How OpenMAIC Implements Durable Sessions with PostgreSQL

OpenMAIC persists classroom session state to PostgreSQL using a dedicated storage package that implements atomic CRUD operations across runtime_sessions and runtime_records tables, ensuring ACID compliance and survival across server restarts.

The THU-MAIC/OpenMAIC repository provides a robust runtime storage layer designed for educational applications requiring state persistence. When server-side persistence is enabled via environment configuration, the complete runtime state—including session metadata, learner progress, and chronological event records—survives container restarts, crashes, and horizontal scaling events through a PostgreSQL-backed architecture.

Architecture of the PostgreSQL Storage Layer

Storage Package Structure

The persistence logic is encapsulated in packages/@openmaic/storage/src/runtime-store.ts. This module utilizes the node-postgres (pg) driver to execute parameterized SQL statements against the database. The store exports a thin API surface comprising createSession, getSession, appendRecord, setSessionStatus, and deleteSession, which abstract the underlying table operations while maintaining raw SQL performance characteristics.

Database Schema Design

The relational schema is defined in packages/@openmaic/storage/test/pg-schema-contract.test.ts and applied automatically on application startup through a schema-ensure routine. The implementation creates three logical structures: the runtime_sessions table stores session identifiers (id), stage references (stageId), learner authentication keys (learnerKey), timestamps, and runtimeDslVersion metadata; the runtime_records table maintains an append-only log of session events linked by foreign key; and the runtime_status column tracks the current lifecycle state of each session.

Configuring Durable Sessions

Environment Variables

Backend selection occurs through environment-level configuration. Setting NEXT_PUBLIC_PERSISTENCE=postgres or providing a valid PERSISTENCE_POSTGRES_URL connection string activates the PostgreSQL runtime store implementation. When these variables are absent, the system gracefully degrades to client-side IndexedDB storage, ensuring local development requires no external database dependencies.

Docker Compose Profile

Production deployment is streamlined through the server-persistence Docker Compose profile documented in packages/docs/content/docs/deployment.mdx. This profile orchestrates a PostgreSQL container alongside the OpenMAIC application container, automatically injecting the connection string and initializing the required schema without manual migration steps.

Runtime API Implementation

HTTP Endpoint Structure

The HTTP facade resides in app/api/runtime/[...path]/route.ts. This catch-all route handler parses incoming request paths and delegates to specific storage operations. The API surface exposes POST /runtime/sessions for session instantiation, POST /runtime/sessions/:id/records for event appending, PATCH /runtime/sessions/:id/status for lifecycle state transitions, GET /runtime/sessions/:id for full state retrieval, and DELETE /runtime/sessions/:id for resource cleanup.

Authentication and Authorization

Upon session creation, the route handler generates a bearer token in the format Bearer <learnerKey> and returns it to the client. Subsequent write operations to /runtime/sessions/:id/records must include this token in the Authorization header. The handler validates the token against the learnerKey stored in the runtime_sessions table before permitting mutations, ensuring strict session ownership.

Durability Guarantees and Error Handling

ACID Compliance

Every session mutation executes as an atomic SQL transaction, leveraging PostgreSQL’s native ACID guarantees. This ensures that partial writes never corrupt session state, even during concurrent access patterns or unexpected process termination. The append-only design of runtime_records ensures historical data integrity while allowing efficient point-in-time recovery.

Retry Logic for Transient Failures

The storage implementation includes explicit retry mechanisms for transient PostgreSQL errors such as deadlocks and connection timeouts. Test coverage in packages/@openmaic/storage/test/pg-runtime-store.test.ts verifies that the store retries appendRecord operations after encountering recoverable database errors, preventing data loss during network partitions or high-contention scenarios.

Client Integration Examples

The following TypeScript examples demonstrate interacting with the durable session API from a client application:

// Create a new durable session
await fetch('/api/runtime/sessions', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    id: 'session-42',
    stageId: 'stage-1',
    learnerKey: 'user:42',
    runtimeDslVersion: '2.0.0',
  }),
});

// Append a record to the session with authentication
await fetch('/api/runtime/sessions/session-42/records', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'Authorization': 'Bearer user:42', // Returned from creation
  },
  body: JSON.stringify({
    type: 'chat',
    content: 'Hello, world!',
  }),
});

// Retrieve complete session state including all records
const resp = await fetch('/api/runtime/sessions/session-42');
const session = await resp.json(); // { id, status, records: [...] }

// Delete session and all associated records
await fetch('/api/runtime/sessions/session-42', { method: 'DELETE' });

Summary

• OpenMAIC implements durable sessions through a dedicated @openmaic/storage package that wraps PostgreSQL operations in packages/@openmaic/storage/src/runtime-store.ts.
• The system uses three core tables—runtime_sessions, runtime_records, and runtime_status—to maintain ACID-compliant state persistence.
• Activation requires setting NEXT_PUBLIC_PERSISTENCE=postgres or PERSISTENCE_POSTGRES_URL, with a server-persistence Docker profile available for zero-config deployment.
• The app/api/runtime/[...path]/route.ts handler exposes REST endpoints for session lifecycle management, secured via Bearer <learnerKey> authentication.
• Built-in retry logic and atomic SQL transactions ensure data survives server restarts, network hiccups, and concurrent access patterns.

Frequently Asked Questions

How does OpenMAIC handle session persistence when PostgreSQL is unavailable?

When the NEXT_PUBLIC_PERSISTENCE environment variable is unset or PostgreSQL connection fails during initialization, OpenMAIC automatically falls back to browser-based IndexedDB storage. This allows uninterrupted local development while ensuring production deployments maintain durable server-side state.

What tables does OpenMAIC create in PostgreSQL for session storage?

OpenMAIC creates two primary tables: runtime_sessions stores session metadata including IDs, stage references, and learner keys, while runtime_records maintains an append-only log of session events. A runtime_status column within the sessions table tracks the current lifecycle state, with the schema defined in packages/@openmaic/storage/test/pg-schema-contract.test.ts.

How does OpenMAIC ensure data integrity during concurrent session updates?

The storage layer relies on PostgreSQL’s native ACID transaction guarantees, executing all write operations as atomic SQL statements. For transient failures such as deadlocks, the implementation includes retry logic verified in packages/@openmaic/storage/test/pg-runtime-store.test.ts, ensuring that competing updates resolve without data corruption or loss.

Can OpenMAIC run without PostgreSQL for local development?

Yes, OpenMAIC is designed to function without external database dependencies in development environments. When the NEXT_PUBLIC_PERSISTENCE and PERSISTENCE_POSTGRES_URL variables are omitted, the client-side code utilizes IndexedDB for local storage, providing identical API semantics while persisting data only within the browser.

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