[Blog](https://cohesivity.ai/blog)

Architecture

Published Jul 19, 2026·Updated Aug 30, 2026

![](https://cohesivity.ai/authors/arag.webp)![](https://cohesivity.ai/authors/anshu.webp)[Arag](https://www.linkedin.com/in/aragagrawal/) and [Anshu](https://www.linkedin.com/in/aanshuaggrawal120/)

# Database Design Patterns for AI Agent Applications

[Read as Markdown](https://cohesivity.ai/blog/database-design-patterns-for-ai-agent-applications.md)

Data model

Queries

Records

Access

An agent-ready database schema records tasks, operations, and audit events separately from business rows. It enforces tenant scope and idempotency with constraints, coordinates concurrent workers, and keeps enough history to recover after a timeout without treating the model transcript as evidence.

The database does not need to understand prompts. It needs to make unsafe state transitions and duplicate effects difficult.

## Start with tasks and operations

A task is the user-level workflow. An operation is one external or internal effect attempted by that task. Keep them separate because one task may retry an operation several times or wait between operations.

```sql
CREATE TABLE agent_tasks (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  workflow_version text NOT NULL,
  state text NOT NULL,
  state_version bigint NOT NULL DEFAULT 0,
  current_step text,
  wake_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id)
);

CREATE TABLE agent_operations (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  task_id uuid NOT NULL,
  idempotency_key text NOT NULL,
  action text NOT NULL,
  input_hash text NOT NULL,
  state text NOT NULL,
  provider_reference text,
  result jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, idempotency_key),
  FOREIGN KEY (tenant_id, task_id) REFERENCES agent_tasks (tenant_id, id)
);

```

The composite foreign key prevents an operation from pointing at a task in another tenant. The unique idempotency key prevents two workers from creating the same logical operation twice. Compare the stored input hash before returning an existing result, so the same key cannot silently authorize different parameters.

## Use constraints for rules the model must never negotiate

Enforce uniqueness, foreign keys, non-null ownership, legal enum values, and version checks in the database. Application validation still improves errors, but a final constraint protects every writer, including a migration script or a newly generated tool.

For tenant isolation, ensure every tenant-owned child table can be joined through a tenant-scoped key. The [multi-tenant agent infrastructure guide](https://cohesivity.ai/blog/multi-tenant-infrastructure-for-ai-agents) covers row, schema, and database isolation choices.

## Record current state and append-only events

Current-state tables make reads and scheduling simple. An append-only event table explains how the state changed.

Write the state update and event in one transaction:

```sql
UPDATE agent_tasks
SET state = $1, state_version = state_version + 1, updated_at = now()
WHERE tenant_id = $2 AND id = $3 AND state_version = $4;

INSERT INTO agent_events
  (tenant_id, task_id, event_type, actor_id, policy_version, payload)
VALUES ($1, $2, $3, $4, $5, $6);

```

Require the update to affect one row before committing. A zero-row update means another worker advanced the task, so the stale worker must reload rather than overwrite it.

Do not place secrets, full prompts, or unbounded provider responses in event payloads. Store redacted facts or references with a separate retention policy.

## Add an outbox for reliable downstream delivery

When a database update must publish an event or enqueue work, write an outbox row in the same transaction. A relay sends pending rows and marks them delivered. Consumers must still be idempotent because a relay can publish twice after losing an acknowledgement.

This closes the gap where the database commits but the process crashes before it tells the queue. It also gives operations a queryable delivery state instead of relying on a log line.

## Coordinate workers with leases or version checks

Use optimistic versions for brief state transitions. Use a lease when a worker needs temporary ownership of a task. A lease contains owner, acquired time, and expiry. Renew it while working, and require the owner or a newer valid lease for completion.

Do not hold a database transaction open while a model thinks or a provider responds. Reserve the operation, commit, perform the call, then reconcile and update. The [persistent state guide](https://cohesivity.ai/blog/persistent-state-for-long-running-ai-agents) explains how this supports a fresh worker.

## Keep JSON flexible but queryable facts explicit

JSONB suits model-specific metadata, provider payload fragments, and step-local values that vary by workflow. Columns suit tenant ownership, state, operation identity, scheduling, retention, and fields used by policy or indexes.

If a JSON path appears in every authorization query or scheduler, promote it to a typed column. Database introspection is useful to coding agents, but a simpler schema is not permission. Restrict tools to approved statements or an application API rather than handing generated SQL an administrator connection.

## Link traces without turning the database into telemetry storage

Store trace and span identifiers on tasks and operations. Keep detailed model and tool telemetry in the observability system. This allows an investigator to move from a bad business row to the run that produced it without duplicating large traces in Postgres.

OpenTelemetry’s [GenAI semantic conventions](https://opentelemetry.io/docs/specs/semconv/gen-ai/) define common agent and tool attributes. Treat that schema as evolving, and pin the version your instrumentation emits.

## Prove the schema under retry and concurrency

Run two workers against the same task. Reuse an idempotency key with matching and conflicting parameters. Crash after the business transaction commits but before the queue acknowledgement. Delete a tenant in a test environment and verify that owned tasks, operations, artifacts, and credentials follow the intended policy.

The prompt below maps those guarantees to the current schema. It asks for query paths and constraints because “we handle that in the agent” is not a database invariant.

or send it to[Claude Code](https://claude.ai/new?q=Inspect+this+repository%27s+database+schema+and+the+AI+agent+workflows+that+write+to+it.+Do+not+edit+files+or+run+migrations.+Find+the+tables+and+constraints+for+tenants%2C+tasks%2C+operations%2C+idempotency%2C+checkpoints%2C+audit+events%2C+outbox+delivery%2C+leases%2C+and+trace+references.+Choose+one+retried+write+and+show+the+exact+query+path+that+prevents+duplication.+Choose+one+concurrent+task+and+show+how+stale+workers+are+rejected.+List+missing+constraints+and+ambiguous+JSON+fields%2C+then+propose+a+minimal+migration+sequence+without+applying+it. "Send to Claude")[Cursor](https://cursor.com/link/prompt?text=Inspect+this+repository%27s+database+schema+and+the+AI+agent+workflows+that+write+to+it.+Do+not+edit+files+or+run+migrations.+Find+the+tables+and+constraints+for+tenants%2C+tasks%2C+operations%2C+idempotency%2C+checkpoints%2C+audit+events%2C+outbox+delivery%2C+leases%2C+and+trace+references.+Choose+one+retried+write+and+show+the+exact+query+path+that+prevents+duplication.+Choose+one+concurrent+task+and+show+how+stale+workers+are+rejected.+List+missing+constraints+and+ambiguous+JSON+fields%2C+then+propose+a+minimal+migration+sequence+without+applying+it. "Send to Cursor")[Codex](https://chatgpt.com/codex?prompt=Inspect+this+repository%27s+database+schema+and+the+AI+agent+workflows+that+write+to+it.+Do+not+edit+files+or+run+migrations.+Find+the+tables+and+constraints+for+tenants%2C+tasks%2C+operations%2C+idempotency%2C+checkpoints%2C+audit+events%2C+outbox+delivery%2C+leases%2C+and+trace+references.+Choose+one+retried+write+and+show+the+exact+query+path+that+prevents+duplication.+Choose+one+concurrent+task+and+show+how+stale+workers+are+rejected.+List+missing+constraints+and+ambiguous+JSON+fields%2C+then+propose+a+minimal+migration+sequence+without+applying+it. "Send to Codex")[opencode](https://opencode.ai/?q=Inspect+this+repository%27s+database+schema+and+the+AI+agent+workflows+that+write+to+it.+Do+not+edit+files+or+run+migrations.+Find+the+tables+and+constraints+for+tenants%2C+tasks%2C+operations%2C+idempotency%2C+checkpoints%2C+audit+events%2C+outbox+delivery%2C+leases%2C+and+trace+references.+Choose+one+retried+write+and+show+the+exact+query+path+that+prevents+duplication.+Choose+one+concurrent+task+and+show+how+stale+workers+are+rejected.+List+missing+constraints+and+ambiguous+JSON+fields%2C+then+propose+a+minimal+migration+sequence+without+applying+it. "Send to OpenCode")