When we started building Astapa, we had a simple question: how do you give an AI agent write access to a database without giving it write access to your database?

The answer is copy-on-write isolation. Here's how it works.

The core idea

Imagine a layer that sits between your agent and Postgres. Every SELECT query passes through unchanged — the agent sees real production data. But every INSERT, UPDATE, or DELETE gets redirected to a shadow table.

From the agent's perspective, it's writing to the database normally. From production's perspective, nothing happened.

Shadow tables

When an agent first writes to a table, we create a shadow copy in a session-specific schema:

sql

-- Original write
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

-- Becomes
INSERT INTO session_abc123._shadow_users (name, email, _mlb_op, _mlb_ts)
VALUES ('Alice', 'alice@example.com', 'INSERT', NOW())

The shadow table has the same structure as the original, plus metadata columns to track the operation type and timestamp.

Union views

Here's where it gets interesting. When the agent reads data, it needs to see both production rows AND its own staged changes. We accomplish this with union views:

sql

CREATE VIEW session_abc123._view_users AS
  -- Rows from shadow (agent's changes)
  SELECT * FROM session_abc123._shadow_users
  UNION ALL
  -- Rows from production (minus any the agent modified)
  SELECT * FROM public.users
  WHERE id NOT IN (
    SELECT id FROM session_abc123._shadow_users
    UNION
    SELECT id FROM session_abc123._deleted_users
  )

The agent's queries get rewritten to use these views. A simple SELECT * FROM users becomes SELECT * FROM session_abc123._view_users.

Query rewriting

The proxy parses every SQL statement and rewrites table references. This works for:

Simple queries: SELECT * FROM users
Joins: SELECT * FROM users u JOIN orders o ON u.id = o.user_id
Subqueries: SELECT * FROM users WHERE id IN (SELECT user_id FROM orders)
CTEs: WITH active AS (SELECT * FROM users WHERE status = 'active') SELECT * FROM active

We use sqlparser-rs for parsing. It handles the full Postgres SQL dialect — arrays, JSON operators, window functions, recursive CTEs, all of it.

Handling deletes

Deletes are tricky. You can't just remove a row from a shadow table that doesn't have it yet. Instead, we track deletions separately:

sql

-- Original delete
DELETE FROM users WHERE id = 42

-- Becomes
INSERT INTO session_abc123._deleted_users (id)
SELECT id FROM public.users WHERE id = 42

The union view excludes these IDs from production results, making the row "disappear" from the agent's perspective.

Conflict detection

The hardest problem is detecting when production state has drifted since the agent started working.

Our solution: row hashes. When a session is created, we don't snapshot anything. But when the agent first reads a row it will later modify, we capture a hash of that row's contents.

At approval time, we recompute hashes for all touched rows and compare:

Row users.id=42:
  Captured hash: a1b2c3d4
  Current hash:  e5f6g7h8
  Status: CONFLICT (row modified since read)

If any row has drifted, we surface the conflict. The reviewer can then decide whether to proceed anyway or reject the session.

Performance considerations

You might worry this is slow. It's not.

Lazy infrastructure. We don't create shadow tables until the first write to that table. Most sessions only touch a few tables.
Query caching. Rewritten queries are cached by (schema, original_sql) tuple. The parser only runs once per unique query pattern.
Parallel view creation. When a query references multiple tables, we create their infrastructure in parallel using Tokio tasks.
Advisory locks. Multiple connections can safely create the same view simultaneously — we use Postgres advisory locks to prevent races.

In benchmarks, the overhead is ~2ms per query for cache misses, <0.1ms for cache hits.

The schema lifecycle

Each session gets its own Postgres schema:

session_a1b2c3d4_e5f6_g7h8_i9j0_k1l2m3n4o5p6/
  _shadow_users
  _shadow_orders
  _deleted_users
  _view_users
  _view_orders

When approved, we apply changes to production and drop the schema. When rejected or expired, we just drop the schema. No cleanup needed.

What we're not doing

A few things we explicitly don't handle:

Schema migrations. DDL statements (CREATE TABLE, ALTER COLUMN) are blocked. Use proper migration tools.
Sequences. Auto-increment IDs in shadow tables get fresh values at approval time to avoid collisions.
Large objects. BLOB/TOAST support is on the roadmap but not implemented yet.

Try it yourself

The best way to understand copy-on-write isolation is to use it:

bash

# Create a session
curl -X POST localhost:8080/sessions -d '{"project_id":"test"}'

# Connect through the proxy
psql "postgresql://localhost:5433/mydb?options=-c%20astapa_session%3D<session_id>"

# Make changes, see them isolated
UPDATE users SET balance = 0 WHERE id = 42;
SELECT * FROM users WHERE id = 42;  -- Shows balance = 0

# Check production (unchanged)
psql postgresql://localhost:5432/mydb -c "SELECT balance FROM users WHERE id = 42"
-- Still shows original balance

Astapa is open source. View the code or read the docs.

Database sandboxes for AI agents