# How OpenMAIC Implements Durable Sessions with PostgreSQL

> OpenMAIC ensures durable sessions with PostgreSQL by persisting classroom state to atomic CRUD operations across runtime_sessions and runtime_records tables, guaranteeing ACID compliance and survival after server restarts.

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

---

**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:

```typescript
// 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',
  }),
});

```

```typescript
// 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!',
  }),
});

```

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

```

```typescript
// 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.