Developer Database

PIE provides each plugin (agent) with its own managed PostgreSQL database — a private schema in a shared Postgres instance, isolated by a dedicated role with restricted privileges. This is the same multi-tenant model used by platforms like Supabase.

How It Works

Developer (Postico/pgAdmin) ──direct connection──► Developer DB
                                                     ▲
Plugin Code (E2B sandbox) ──context.managedDb.query()──► Plugin Bridge ──► Developer DB
                                                         (injects user_id)

Each agent gets its own isolated database schema. If you have 3 agents, you get 3 separate schemas with independent tables and credentials.

Two access paths:

1. Direct connection — Use your credentials in any Postgres client (Postico, pgAdmin, psql, DBeaver) to create tables, inspect data, and design your schema.
2. Runtime via plugin code — Call context.managedDb.query(sql, params) from your plugin. PIE automatically injects the end-user's ID as a session variable so you can build per-user data isolation.

Provisioning Your Database

Databases are auto-provisioned — you don't need to initialize them manually. Provisioning happens automatically when:

1. You save a plugin that has a database section in its manifest (the declarative schema approach), or
2. Your plugin code calls context.managedDb.query() for the first time (the code-first approach)

You can also provision manually via the Database tab in the Inspector panel, or via the API:

curl -X POST https://your-pie-domain.com/api/plugins/YOUR_PLUGIN_ID/database \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Provisioning creates:

• A private PostgreSQL schema (e.g., dev_abc123def456...)
• A restricted role with access only to that schema
• Login credentials for direct connections

Getting Your Credentials

View credentials in the Database tab in the Developer Portal, or fetch via the API:

curl https://your-pie-domain.com/api/plugins/YOUR_PLUGIN_ID/database/credentials \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Response:

{
  "credentials": {
    "host": "db.example.com",
    "port": 5432,
    "database": "pie_developer",
    "username": "pier_abc123def456...",
    "password": "...",
    "schemaName": "dev_abc123def456...",
    "sslMode": "require",
    "connectionString": "postgresql://[email protected]:5432/pie_developer?sslmode=require"
  }
}

Use the connectionString directly in Postico, pgAdmin, or any Postgres client.

Connecting with Postico / pgAdmin

1. Copy your connectionString from the credentials endpoint
2. Paste it into your Postgres client's connection dialog
3. Your search_path is automatically set to your private schema — you don't need to prefix table names

Declarative Schema

Define your database tables directly in the plugin manifest's database section. PIE auto-creates tables and adds columns on every save — similar to how Drizzle or Prisma push schemas, but additive-only (never drops anything).

Manifest Format

manifest:
  trigger: auto
  database:
    tables:
      notes:
        columns:
          id: "uuid primary key default gen_random_uuid()"
          pie_user_id: "text not null default current_setting('app.user_id', true)"
          title: "text not null"
          content: "text"
          created_at: "timestamptz default now()"
        rls: "pie_user_id"
      settings:
        columns:
          id: "uuid primary key default gen_random_uuid()"
          pie_user_id: "text not null default current_setting('app.user_id', true)"
          key: "text not null"
          value: "jsonb"
        rls: "pie_user_id"
  tool:
    name: my_tool
    ...

Each key under tables is the table name. Each key under columns is the column name — the value is a raw Postgres column definition (type + constraints + default). The rls field (optional) names the column that holds the end-user ID.

How Sync Works

On every plugin save, PIE compares the manifest schema against the actual database:

Scenario	Action
Table in manifest but not in DB	CREATE TABLE
Column in manifest but not on existing table	ALTER TABLE ADD COLUMN
Table/column in DB but not in manifest	Ignored (no drops)
Column exists but with a different type	Warning (no alter)

This is always safe — you can never lose data through the sync process.

The rls Shorthand

When you set rls: "pie_user_id" on a table, PIE automatically:

1. Runs ALTER TABLE ... ENABLE ROW LEVEL SECURITY
2. Runs ALTER TABLE ... FORCE ROW LEVEL SECURITY
3. Creates a policy: USING (pie_user_id = current_setting('app.user_id', true)) WITH CHECK (...)

This means every query through context.managedDb.query() automatically filters rows to the current user — no manual RLS setup needed.

Example: What the Platform Runs

Given this manifest:

database:
  tables:
    notes:
      columns:
        id: "uuid primary key default gen_random_uuid()"
        pie_user_id: "text not null default current_setting('app.user_id', true)"
        title: "text not null"
      rls: "pie_user_id"

PIE runs (on first save):

CREATE TABLE IF NOT EXISTS "dev_<plugin_hex>"."notes" (
  "id" uuid primary key default gen_random_uuid(),
  "pie_user_id" text not null default current_setting('app.user_id', true),
  "title" text not null
);

ALTER TABLE "dev_<plugin_hex>"."notes" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "dev_<plugin_hex>"."notes" FORCE ROW LEVEL SECURITY;

CREATE POLICY "notes_user_isolation"
  ON "dev_<plugin_hex>"."notes"
  FOR ALL
  USING ("pie_user_id" = current_setting('app.user_id', true))
  WITH CHECK ("pie_user_id" = current_setting('app.user_id', true));

On subsequent saves, only new tables/columns are created. Existing objects are left untouched.

Removing Tables or Columns from the Manifest

If you remove a table or column from the manifest, nothing happens — it stays in the database. The sync is additive-only. To drop a table or column, connect directly with Postico/pgAdmin and run the DDL manually.

Manual Schema Management

The declarative schema is optional. You can still create tables manually via a direct connection or context.managedDb.query('CREATE TABLE ...'). The manifest approach simply automates the common case.

Creating Tables

You can create tables in two ways: declaratively via the manifest (recommended), or manually.

Declarative (Manifest)

Add tables to the database.tables section in your manifest (see above). They're created automatically on save.

Manual (SQL)

Connect with your credentials and create tables normally:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  external_user_id TEXT NOT NULL,
  display_name TEXT,
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE notes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Using context.managedDb.query() in Plugins

Call context.managedDb.query() from your plugin code. Unlike context.db.query() (which is for external databases), the managed DB requires no connection object — credentials are handled automatically.

Basic Queries

async function handler(input, context) {
  // INSERT
  await context.managedDb.query(
    'INSERT INTO notes (user_id, title, content) VALUES ($1, $2, $3)',
    [input.userId, input.title, input.content]
  );

  // SELECT
  const result = await context.managedDb.query(
    'SELECT * FROM notes WHERE user_id = $1 ORDER BY created_at DESC LIMIT 10',
    [input.userId]
  );

  return { notes: result.rows };
}

Query Response

{
  rows: [{ id: '...', title: '...', content: '...' }],
  columns: ['id', 'title', 'content'],
  rowCount: 1,
  truncated: false,
  executionTimeMs: 12
}

Options

const result = await context.managedDb.query(
  'SELECT * FROM large_table',
  [],
  { timeoutMs: 10000, maxRows: 5000 }
);

Option	Default	Max	Description
timeoutMs	30000	30000	Statement timeout in milliseconds
maxRows	1000	10000	Maximum rows returned

User Context

Every query executed through context.managedDb.query() has the end-user's ID injected as a PostgreSQL session variable. Your plugin code can access it, and more importantly, you can use it in Row-Level Security policies.

The following session variables are set on every query:

Variable	Value	Access with
app.user_id	The end-user's PIE user ID	current_setting('app.user_id')
app.plugin_id	Your plugin's ID	current_setting('app.plugin_id')

Using User ID in Queries

async function handler(input, context) {
  // context.user.id is the same value as app.user_id
  const result = await context.managedDb.query(
    'SELECT * FROM notes WHERE external_user_id = $1',
    [context.user.id]
  );
  return { notes: result.rows };
}

Row-Level Security (RLS) Patterns

For per-user data isolation, you can create RLS policies that use the injected app.user_id session variable. This way, even if your plugin code has a bug, users can never see each other's data.

Declarative RLS

If you use the manifest's database.tables section, just add rls: "pie_user_id" to your table and the policy is created automatically. The manual setup below is only needed if you're managing tables outside the manifest.

Setup

-- 1. Store the PIE user ID on your rows
CREATE TABLE user_data (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pie_user_id TEXT NOT NULL,
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Enable RLS
ALTER TABLE user_data ENABLE ROW LEVEL SECURITY;

-- 3. Create a policy that restricts access to the current user's rows
CREATE POLICY user_data_isolation ON user_data
  FOR ALL
  USING (pie_user_id = current_setting('app.user_id', true))
  WITH CHECK (pie_user_id = current_setting('app.user_id', true));

-- 4. Force RLS for the table owner too (important!)
ALTER TABLE user_data FORCE ROW LEVEL SECURITY;

With this in place, every query through context.managedDb.query() automatically filters to the current user's data:

// This only returns rows belonging to the current user
const result = await context.managedDb.query('SELECT * FROM user_data');

Auto-Populating User ID on Insert

Use a trigger or default value to automatically set the user ID:

ALTER TABLE user_data
  ALTER COLUMN pie_user_id SET DEFAULT current_setting('app.user_id', true);

Now inserts don't even need to specify the user ID:

await context.managedDb.query(
  'INSERT INTO user_data (data) VALUES ($1)',
  [{ key: 'value' }]
);

Limits

Limit	Value
Statement timeout	30 seconds
Max rows per query	10,000
Max response size	5 MB
Max concurrent connections (direct)	10

Security

• Your schema is completely isolated. Your role cannot access any other developer's schema.
• Your role has NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOREPLICATION — no privilege escalation is possible.
• Runtime queries run on fresh connections per request, preventing session-state leakage.
• The public schema is locked down — REVOKE ALL ON SCHEMA public FROM PUBLIC is enforced on the developer database.
• Credentials are encrypted at rest using AES-256-GCM.

Password Rotation

If you need to regenerate your database password (e.g., if credentials are leaked), use the Regenerate Password button in the Database tab, or call the API:

curl -X POST https://your-pie-domain.com/api/plugins/YOUR_PLUGIN_ID/database/regenerate-password \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This immediately invalidates the old password and returns new credentials.

Usage Stats

View table counts and storage in the Database tab, or via the API:

curl https://your-pie-domain.com/api/plugins/YOUR_PLUGIN_ID/database/stats \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Response:

{
  "stats": {
    "schemaName": "dev_abc123...",
    "tableCount": 3,
    "totalSizeBytes": 81920,
    "tables": [
      { "name": "users", "sizeBytes": 40960 },
      { "name": "notes", "sizeBytes": 32768 },
      { "name": "settings", "sizeBytes": 8192 }
    ]
  }
}

Comparison: context.db.query() vs context.managedDb.query()

Feature	context.db.query()	context.managedDb.query()
Database	Any external Postgres	PIE-managed developer DB
Connection	You provide credentials	Automatic
Access	Read-only (SELECT/WITH/EXPLAIN)	Full CRUD
User context	None	app.user_id auto-injected
Use case	Querying user's own databases	Storing plugin data