AgentZero Migration Guide

Operational handbook for deploying schema changes, managing zero-downtime upgrades, and handling data migrations.

end

Database Schema Evolution

Migration Structure

Migrations live in /supabase/migrations/ and follow Supabase's naming convention:

$  snippetread-only
20260325000000_multi_tenant_schema.sql          (baseline)
20260411000000_create_documents.sql             (documents + chunks tables)
20260412000000_agent_scoped_documents.sql       (adds agent_id to documents)
20260418000000_lemonsqueezy_billing.sql         (subscriptions table)
20260418000001_founding_grants.sql              (RPCs + founding_grants table)
20260425000000_agent_conversations.sql          (conversations + messages)
20260425000001_memo_summaries.sql               (memory store + match_memo_summaries RPC)
20260516000000_waitlist.sql                     (waitlist signups)

Timestamp Format: YYMMDDhhmmss (e.g., 20260325000000 = 2026-03-25 00:00:00 UTC)

Unique Constraint: Each migration file has a unique timestamp; no two migrations can have the same prefix.

Running Migrations

Local Development

$  snippetread-only
# Deploy all pending migrations to local/staging Supabase
supabase migration up

# See status
supabase migration list

# Reset to a clean slate (CAUTION: destructive)
supabase db reset

Staging Environment

$  snippetread-only
# Link your staging project (if not already linked)
supabase link --project-id <staging-project-id>

# Dry-run the migration (shows SQL without executing)
supabase migrations list --linked

# Deploy migrations
supabase db push

# Verify applied migrations
supabase migration list

Production

$  snippetread-only
# 1. Backup before any changes
supabase db remote backup create

# 2. Link to production project
supabase link --project-id <prod-project-id>

# 3. Preview the migration (shows changes without applying)
supabase db pull  # This generates a local schema snapshot

# 4. Deploy to production
supabase db push

# 5. Verify in Supabase dashboard
# - Check Database > Migrations tab
# - Check Tables tab for schema changes
# - Test via API calls or SQL editor

Creating a New Migration

Step 1: Create the file

$  snippetread-only
supabase migration new <descriptive_name>
# Generates: supabase/migrations/20260516120000_descriptive_name.sql

Step 2: Write idempotent SQL

Always use IF NOT EXISTS clauses to prevent errors on re-runs:

$  snippetread-only
-- Good: idempotent
ALTER TABLE agents
  ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'active';

CREATE INDEX IF NOT EXISTS idx_agents_status ON agents (status);

-- Bad: will error if column already exists
ALTER TABLE agents ADD COLUMN status TEXT DEFAULT 'active';

Step 3: Test locally

$  snippetread-only
supabase db reset  # Start fresh
# Edit migration, save
supabase migration up
# Test that schema changes are correct

Step 4: Commit and push

$  snippetread-only
git add supabase/migrations/20260516120000_*.sql
git commit -m "migration: add agent status column"
git push origin feature/agent-status

end

Rollback Procedures

Manual Rollback (Least Preferred)

If a migration causes issues and you need to revert:

$  snippetread-only
# 1. In Supabase dashboard, SQL Editor, manually undo the change
#    (e.g., DROP COLUMN, DROP TABLE, etc.)
#
# 2. Mark the migration as reverted in local state:
#    supabase migration mark <migration-name> --reverted
#
# 3. Create a new corrective migration for the fix
#    supabase migration new fix_previous_issue
#
# 4. Push the corrective migration
#    supabase db push

Recommended: Create a Corrective Migration

Rollbacks via new migrations (rather than deletion) maintain audit trail:

Original migration (problematic):

$  snippetread-only
-- 20260516120000_add_status.sql
ALTER TABLE agents ADD COLUMN status TEXT DEFAULT 'active';
CREATE INDEX idx_agents_status ON agents (status);

Corrective migration (if needed):

$  snippetread-only
-- 20260516150000_fix_agent_status.sql
DROP INDEX IF EXISTS idx_agents_status;
ALTER TABLE agents DROP COLUMN IF EXISTS status;

This approach:

›Keeps full audit trail (no deletions)
›Prevents "missing migration" issues
›Allows easy replay of history via supabase migration up

Testing Rollback (Development Only)

$  snippetread-only
# Reset to a clean slate
supabase db reset

# Simulate the old schema (before your new migration)
supabase migration up --to <previous-migration-name>

# Test data integrity in the old schema
# ...

# Then re-apply new migrations
supabase migration up

end

Zero-Downtime Deployments

AgentZero's architecture supports zero-downtime schema changes via the following patterns:

Pattern 1: Adding Nullable Columns

Safe: Code can handle missing value, schema adds column with default.

$  snippetread-only
-- Migration: add optional feature column
ALTER TABLE agents
  ADD COLUMN IF NOT EXISTS new_feature TEXT DEFAULT NULL;

-- Application code:
if (agent.new_feature) { /* use it */ }
// Works before and after migration

Deployment Order:

›Deploy schema (migration)
›Deploy code (reads the new column, ignores if NULL)
›No downtime, no rollback risk

end

Pattern 2: Adding Columns with Data Migration

Requires coordination between schema deployment and data backfill.

$  snippetread-only
-- Step 1: Add column as nullable
ALTER TABLE user_credits
  ADD COLUMN IF NOT EXISTS monthly_budget INT DEFAULT NULL;

-- Step 2: Backfill data in batches (see "Data Migrations" section)
-- UPDATE user_credits SET monthly_budget = 1000 WHERE monthly_budget IS NULL;

-- Step 3: Once backfilled, make NOT NULL
ALTER TABLE user_credits
  ALTER COLUMN monthly_budget SET NOT NULL;

Deployment Order:

›Deploy Step 1 (add column nullable) → schema updated, code unaffected
›Run data migration (backfill) → data integrity restored
›Deploy Step 3 (make NOT NULL) → enforce constraint
›Code now requires the column (already exists from step 1)

end

Pattern 3: Renaming Columns (Highly Disruptive)

Avoid if possible. If necessary, use a two-migration pattern:

Migration 1:

$  snippetread-only
ALTER TABLE organisations
  ADD COLUMN IF NOT EXISTS slug_new TEXT;

-- Backfill: UPDATE organisations SET slug_new = slug;

Code Update:

$  snippetread-only
// Read from both columns for safety
const slug = org.slug_new ?? org.slug;

Migration 2 (after code deployment):

$  snippetread-only
ALTER TABLE organisations DROP COLUMN slug;
ALTER TABLE organisations RENAME COLUMN slug_new TO slug;

Deployment Order:

›Deploy migration 1 (add new column, code still reads from old)
›Deploy code (reads new column with fallback to old)
›Run backfill for new column
›Deploy migration 2 (drop old column)
›Code continues working, migration complete

end

Pattern 4: Dropping Columns (Safe with Timing)

$  snippetread-only
-- Only drop AFTER code is updated to not reference it
ALTER TABLE agents DROP COLUMN IF EXISTS legacy_field;

Prerequisite: All code paths must be updated to not read/write legacy_field first.

Deployment Order:

›Deploy code changes (remove all reads/writes of legacy_field)
›Deploy migration (drop column)
›Verify logs show no "column not found" errors

end

Pattern 5: Constraint Changes

Adding constraints (safe):

$  snippetread-only
-- If data violates the constraint, this will fail.
-- Pre-run: DELETE FROM table WHERE condition violates constraint
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Removing constraints (safe):

$  snippetread-only
ALTER TABLE users DROP CONSTRAINT users_email_unique;

Modifying CHECK constraints (requires recreation):

$  snippetread-only
-- 1. Drop old constraint
ALTER TABLE subscriptions DROP CONSTRAINT subscriptions_tier_check;

-- 2. Add new constraint
ALTER TABLE subscriptions
  ADD CONSTRAINT subscriptions_tier_check CHECK (tier IN ('pro', 'founding', 'trial'));

end

Data Migrations

Bulk Update Pattern

For updating large datasets, use batched server-side migrations to avoid timeouts.

Migration: Add Credits to Existing Users

File: /supabase/migrations/20260516_grant_initial_credits.sql

$  snippetread-only
-- Migration: Grant initial 100 credits to all users without a user_credits row
-- Assumes user_credits table exists with (user_id, credits_remaining, credits_used)

BEGIN;

-- 1. Insert missing user_credits rows with initial balance
INSERT INTO user_credits (user_id, credits_remaining, credits_used)
SELECT id, 100, 0
FROM users
WHERE NOT EXISTS (
  SELECT 1 FROM user_credits WHERE user_credits.user_id = users.id
)
ON CONFLICT (user_id) DO NOTHING;

-- 2. Verify insert
SELECT COUNT(*) AS inserted_users FROM user_credits;

COMMIT;

Execution:

$  snippetread-only
supabase db push
# Verify in dashboard: SELECT COUNT(*) FROM user_credits;

end

Server-Side Migration: One-Time Jobs

For complex logic that can't run in SQL alone, use a TypeScript script:

File: /scripts/migrate-emails.ts

$  snippetread-only
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SECRET_KEY!
);

const BATCH_SIZE = 100;

async function migrateEmails() {
  let offset = 0;
  let totalProcessed = 0;

  while (true) {
    // Fetch batch
    const { data: users, error } = await supabase
      .from("users")
      .select("id, email")
      .is("verified_email_sent_at", null)
      .range(offset, offset + BATCH_SIZE - 1);

    if (error) {
      console.error("Query error:", error);
      break;
    }

    if (!users || users.length === 0) {
      console.log("Migration complete. Processed:", totalProcessed);
      break;
    }

    // Process batch (e.g., send verification emails)
    for (const user of users) {
      console.log(`Sending email to ${user.email}...`);
      // await sendVerificationEmail(user.email);
    }

    // Mark batch as processed
    const ids = users.map((u) => u.id);
    const { error: updateError } = await supabase
      .from("users")
      .update({ verified_email_sent_at: new Date().toISOString() })
      .in("id", ids);

    if (updateError) {
      console.error("Update error:", updateError);
      break;
    }

    totalProcessed += users.length;
    offset += BATCH_SIZE;

    // Wait between batches to avoid rate limits
    await new Promise((r) => setTimeout(r, 500));
  }
}

migrateEmails();

Execution:

$  snippetread-only
# Run manually after production migration
NEXT_PUBLIC_SUPABASE_URL=... SUPABASE_SECRET_KEY=... \
  npx tsx scripts/migrate-emails.ts

# Monitor progress in logs
# Ctrl+C to stop (resumes from last batch on rerun)

end

Verification Steps

After any data migration:

$  snippetread-only
# 1. Count rows before/after
supabase -p <project-id> query "SELECT COUNT(*) FROM users;"
supabase -p <project-id> query "SELECT COUNT(*) FROM user_credits;"

# 2. Spot-check a few rows
supabase -p <project-id> query "SELECT * FROM users LIMIT 5;"

# 3. Check for NULL values in critical columns
supabase -p <project-id> query "SELECT COUNT(*) FROM users WHERE email IS NULL;"

# 4. Run test queries matching app logic
supabase -p <project-id> query "
  SELECT u.id, u.email, uc.credits_remaining
  FROM users u
  LEFT JOIN user_credits uc ON u.id = uc.user_id
  LIMIT 10;
"

# 5. Test Server Actions (e.g., in staging)
# Call streamAgentAction, checkUserCredits, etc. to verify data integrity

end

Row Level Security (RLS) Considerations

RLS Policy Evolution

AgentZero uses RLS to enforce multi-tenancy. Policies are defined in migrations:

Current Status: RLS enabled on all tables, but all Server Actions use the adminClient which bypasses RLS via the service-role key.

When you need to add user-facing read policies, follow this pattern:

$  snippetread-only
-- Migration: 20260520_add_agent_read_policy.sql

-- Allow authenticated users to read their organisation's agents
CREATE POLICY "Users can read their org's agents"
ON agents
FOR SELECT
USING (
  organisation_id IN (
    SELECT organisation_id FROM users WHERE id = auth.uid()
  )
);

Application Layer: No changes needed if using adminClient in Server Actions (already does this check):

$  snippetread-only
// Server Action: Already org-scoped before query
const { data: agents } = await adminClient
  .from("agents")
  .select(...)
  .eq("organisation_id", session.user.orgId);  // ← manual scoping

Testing RLS Policies

$  snippetread-only
# In Supabase SQL editor, test as different users:

-- As authenticated user (with valid JWT)
SET ROLE authenticated;
SET app.current_user_id = '<uuid>';
SELECT * FROM agents;  -- Should return only org's agents

-- As service role (bypasses RLS)
SET ROLE service_role;
SELECT * FROM agents;  -- Returns all agents (no filtering)

end

API Versioning (Current: v0.1.0)

AgentZero does not currently version APIs. All endpoints are at /api/... (no version prefix).

When to Introduce Versioning

If you need to make breaking changes to a Server Action or webhook format:

Option 1: Route-Based Versioning (HTTP endpoints)

$  snippetread-only
/app/api/v1/webhooks/lemonsqueezy/route.ts
/app/api/v2/webhooks/lemonsqueezy/route.ts

Option 2: Server Action Overloading

$  snippetread-only
// lib/actions/agent-actions.ts

// Old signature (deprecated)
export async function streamAgentAction_v1(...) { ... }

// New signature
export async function streamAgentAction_v2(...) { ... }

// Alias to new version
export const streamAgentAction = streamAgentAction_v2;

Option 3: Feature Flags

$  snippetread-only
const AGENT_ACTIONS_VERSION = process.env.AGENT_ACTIONS_V ?? "2";

export async function streamAgentAction(...) {
  if (AGENT_ACTIONS_VERSION === "1") {
    return streamAgentAction_v1(...);
  } else {
    return streamAgentAction_v2(...);
  }
}

end

Dependency Updates

Node.js & NPM

$  snippetread-only
# Check for updates
npm outdated

# Update package.json versions
npm update

# Audit security vulnerabilities
npm audit

# If npm audit fails, fix manually
npm install <package>@<version>

# Commit and test
git add package.json package-lock.json
npm test
git commit -m "chore: update dependencies"

Next.js Upgrade (e.g., 16 → 17)

$  snippetread-only
# Update in package.json
npm install next@17

# Test local dev
npm run dev

# Build and check for warnings
npm run build

# Test server actions and API routes
npm start

# Check TypeScript
npx tsc --noEmit

Auth.js Upgrade (e.g., v5-beta → v5-stable)

Auth.js is used for session management. Breaking changes in version bumps:

$  snippetread-only
npm install next-auth@5.1.0

# Test login/logout/signup Server Actions
# Check session serialization in streamAgentAction, etc.

# Run full test suite if available
npm run test

end

Configuration Changes

Environment Variables

Environment variables are loaded from .env.local (local) or .env (staging/prod).

Adding a New Environment Variable

›

Document it in /ENV_TEMPLATE.txt or similar:

$  snippetread-only
RAG_MAX_RESULTS=10          # Max chunks returned from semantic search

›
Update deployment checklist for ops team

›

Add type safety if reading in TypeScript:

$  snippetread-only
const RAG_MAX_RESULTS = parseInt(process.env.RAG_MAX_RESULTS ?? "10", 10);

Rotating Secrets

If a secret (API key, webhook secret) is compromised:

›Generate new secret in external service (Lemon Squeezy, Resend, etc.)
›Update .env (local) and Vercel/hosting environment variables
›Redeploy application
›Revoke old secret in external service
›Monitor logs for old secret usage (should cease immediately)

end

Monitoring & Observability During Deployments

Pre-Deployment Checklist

›[ ] Run tests: npm run test (if tests exist)
›[ ] Build succeeds: npm run build
›[ ] No TypeScript errors: npx tsc --noEmit
›[ ] Lint passes: npm run lint
›[ ] All migrations reviewed (SQL inspection)
›[ ] Backup created in Supabase dashboard
›[ ] Staging tested with real data

During Deployment

Watch these logs in Supabase/Vercel dashboard:

$  snippetread-only
# Database logs (SQL execution)
supabase -p <project-id> logs --sql

# Application logs (errors, warnings)
# Vercel: Deployments → View Logs

# Webhook logs (if applicable)
# Check: Supabase SQL Editor → SELECT * FROM webhook_events

Check these metrics:

$  snippetread-only
# API response times
# Error rates (4xx, 5xx)
# Database connection pool usage
# Credit deduction success rate

Post-Deployment Verification

›
Test critical user flows in staging:
- ›Sign up → create agent → stream response
- ›Upload document → semantic search
- ›Purchase (webhook simulation)

›

Verify no data loss:

$  snippetread-only
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM agents;
SELECT COUNT(*) FROM documents;

›
Check logs for errors:
- ›No "Unauthorised" floods
- ›No "Insufficient credits" false positives
- ›No embedding generation failures
›
Monitor for 24 hours post-deploy before considering stable

end

Troubleshooting Deployments

Migration Fails to Apply

Error: ERROR: column "new_col" already exists

Fix: Migration is not idempotent. Ensure all statements use IF NOT EXISTS:

$  snippetread-only
-- Change from:
ALTER TABLE agents ADD COLUMN status TEXT;

-- To:
ALTER TABLE agents ADD COLUMN IF NOT EXISTS status TEXT;

Data Migration Hangs

Error: Script stops responding after N rows

Cause: Query timeout or RPC timeout

Fix: Reduce batch size:

$  snippetread-only
const BATCH_SIZE = 100;  // Try 50 or 25 instead

RLS Policy Breaks Read Access

Error: SELECT returns 0 rows after adding RLS policy

Fix: Check policy logic:

$  snippetread-only
-- Bad: policy too restrictive
CREATE POLICY "org_only" ON agents
FOR SELECT USING (FALSE);  -- Always denies

-- Good: scoped to user's org
CREATE POLICY "org_only" ON agents
FOR SELECT USING (
  organisation_id = (
    SELECT organisation_id FROM users WHERE id = auth.uid()
  )
);

Webhook Signature Fails After Secret Rotation

Error: Lemon Squeezy webhooks return 401 Unauthorized

Fix: Verify secret is updated:

$  snippetread-only
# In Lemon Squeezy dashboard:
# 1. Note the webhook secret
# 2. Update Vercel/local env var: LEMONSQUEEZY_WEBHOOK_SECRET
# 3. Redeploy
# 4. Test webhook manually (LS dashboard → Webhooks → Resend)

end

Backup & Recovery

Automated Backups

Supabase provides:

›Daily backups (free tier: 7 days retention)
›Weekly backups (paid tier: longer retention)

View backups:

$  snippetread-only
supabase -p <project-id> db ls backups

Manual Backup (Recommended Before Major Deployments)

$  snippetread-only
supabase -p <project-id> db remote backup create
# Creates timestamped backup in Supabase UI → Database → Backups

Restore from Backup

$  snippetread-only
# 1. In Supabase dashboard: Database → Backups → Restore
# 2. Select the backup timestamp
# 3. Confirm (destructive — will overwrite current data)

Export Data for Safekeeping

$  snippetread-only
# Export as CSV (for key tables)
supabase -p <project-id> query "
  COPY users TO STDOUT WITH CSV HEADER;
" > users_backup.csv

# Export as SQL (full schema + data)
pg_dump \
  postgresql://<user>:<password>@<host>:5432/<db> \
  > agentzero_full_backup.sql

end

Upgrade Path: v0.1 → v0.2 (Future)

When deploying a new major version, follow this checklist:

›
Schema migrations
- ›[ ] All new tables have RLS enabled
- ›[ ] Foreign keys reference correct tables
- ›[ ] Indexes created for query performance
›
Data compatibility
- ›[ ] Data migrations for new required columns
- ›[ ] Backfill legacy data if needed
- ›[ ] Verify no data loss
›
API compatibility
- ›[ ] Old Server Actions still work (backward compatible)
- ›[ ] New Server Actions tested thoroughly
- ›[ ] Webhook format unchanged (or migrate both versions)
›
Deployment sequence
- ›[ ] Deploy schema first (backward compatible)
- ›[ ] Deploy application code second
- ›[ ] Run data migrations (if any)
- ›[ ] Monitor for 24 hours
- ›[ ] Decommission old code paths (in v0.3)
›
Rollback plan
- ›[ ] Can revert app to v0.1 if critical issues found
- ›[ ] Schema migration can be reversed (corrective migration)
- ›[ ] Data can be restored from backup

end

Reference: Current Migration Timeline

| Migration | Applied | Purpose | |-----------|---------|---------| | 20260325000000_multi_tenant_schema.sql | ✓ | Org/user/agent tables | | 20260325000001_user_credits.sql | ✓ | user_credits ledger + deduct/refund RPCs + auto-create trigger | | 20260411000000_create_documents.sql | ✓ | Documents + chunks tables (RAG baseline) | | 20260412000000_agent_scoped_documents.sql | ✓ | Add agent_id to documents | | 20260418000000_lemonsqueezy_billing.sql | ✓ | Subscriptions + webhook log | | 20260418000001_founding_grants.sql | ✓ | Founding purchases + RPCs | | 20260425000000_agent_conversations.sql | ✓ | Conversations + messages | | 20260425000001_memo_summaries.sql | ✓ | Memo summaries table + match_memo_summaries RPC (OpenAI 1536-dim baseline) |

Next migration number: 20260517000000_... (or later, depending on date)

end

Links & Resources

›Supabase CLI: https://supabase.com/docs/guides/cli
›SQL Migrations: https://supabase.com/docs/guides/migrations
›RLS Policies: https://supabase.com/docs/guides/auth/row-level-security
›pgvector: https://supabase.com/docs/guides/database/extensions/pgvector
›Lemon Squeezy Webhooks: https://docs.lemonsqueezy.com/api/webhooks

AgentZero Migration Guide

Operational handbook for deploying schema changes, managing zero-downtime upgrades, and handling data migrations.

end

Database Schema Evolution

Migration Structure

Migrations live in /supabase/migrations/ and follow Supabase's naming convention:

$  snippetread-only
20260325000000_multi_tenant_schema.sql          (baseline)
20260411000000_create_documents.sql             (documents + chunks tables)
20260412000000_agent_scoped_documents.sql       (adds agent_id to documents)
20260418000000_lemonsqueezy_billing.sql         (subscriptions table)
20260418000001_founding_grants.sql              (RPCs + founding_grants table)
20260425000000_agent_conversations.sql          (conversations + messages)
20260425000001_memo_summaries.sql               (memory store + match_memo_summaries RPC)
20260516000000_waitlist.sql                     (waitlist signups)

Timestamp Format: YYMMDDhhmmss (e.g., 20260325000000 = 2026-03-25 00:00:00 UTC)

Unique Constraint: Each migration file has a unique timestamp; no two migrations can have the same prefix.

Running Migrations

Local Development

$  snippetread-only
# Deploy all pending migrations to local/staging Supabase
supabase migration up

# See status
supabase migration list

# Reset to a clean slate (CAUTION: destructive)
supabase db reset

Staging Environment

$  snippetread-only
# Link your staging project (if not already linked)
supabase link --project-id <staging-project-id>

# Dry-run the migration (shows SQL without executing)
supabase migrations list --linked

# Deploy migrations
supabase db push

# Verify applied migrations
supabase migration list

Production

$  snippetread-only
# 1. Backup before any changes
supabase db remote backup create

# 2. Link to production project
supabase link --project-id <prod-project-id>

# 3. Preview the migration (shows changes without applying)
supabase db pull  # This generates a local schema snapshot

# 4. Deploy to production
supabase db push

# 5. Verify in Supabase dashboard
# - Check Database > Migrations tab
# - Check Tables tab for schema changes
# - Test via API calls or SQL editor

Creating a New Migration

Step 1: Create the file

$  snippetread-only
supabase migration new <descriptive_name>
# Generates: supabase/migrations/20260516120000_descriptive_name.sql

Step 2: Write idempotent SQL

Always use IF NOT EXISTS clauses to prevent errors on re-runs:

$  snippetread-only
-- Good: idempotent
ALTER TABLE agents
  ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'active';

CREATE INDEX IF NOT EXISTS idx_agents_status ON agents (status);

-- Bad: will error if column already exists
ALTER TABLE agents ADD COLUMN status TEXT DEFAULT 'active';

Step 3: Test locally

$  snippetread-only
supabase db reset  # Start fresh
# Edit migration, save
supabase migration up
# Test that schema changes are correct

Step 4: Commit and push

$  snippetread-only
git add supabase/migrations/20260516120000_*.sql
git commit -m "migration: add agent status column"
git push origin feature/agent-status

end

Rollback Procedures

Manual Rollback (Least Preferred)

If a migration causes issues and you need to revert:

$  snippetread-only
# 1. In Supabase dashboard, SQL Editor, manually undo the change
#    (e.g., DROP COLUMN, DROP TABLE, etc.)
#
# 2. Mark the migration as reverted in local state:
#    supabase migration mark <migration-name> --reverted
#
# 3. Create a new corrective migration for the fix
#    supabase migration new fix_previous_issue
#
# 4. Push the corrective migration
#    supabase db push

Recommended: Create a Corrective Migration

Rollbacks via new migrations (rather than deletion) maintain audit trail:

Original migration (problematic):

$  snippetread-only
-- 20260516120000_add_status.sql
ALTER TABLE agents ADD COLUMN status TEXT DEFAULT 'active';
CREATE INDEX idx_agents_status ON agents (status);

Corrective migration (if needed):

$  snippetread-only
-- 20260516150000_fix_agent_status.sql
DROP INDEX IF EXISTS idx_agents_status;
ALTER TABLE agents DROP COLUMN IF EXISTS status;

This approach:

›Keeps full audit trail (no deletions)
›Prevents "missing migration" issues
›Allows easy replay of history via supabase migration up

Testing Rollback (Development Only)

$  snippetread-only
# Reset to a clean slate
supabase db reset

# Simulate the old schema (before your new migration)
supabase migration up --to <previous-migration-name>

# Test data integrity in the old schema
# ...

# Then re-apply new migrations
supabase migration up

end

Zero-Downtime Deployments

AgentZero's architecture supports zero-downtime schema changes via the following patterns:

Pattern 1: Adding Nullable Columns

Safe: Code can handle missing value, schema adds column with default.

$  snippetread-only
-- Migration: add optional feature column
ALTER TABLE agents
  ADD COLUMN IF NOT EXISTS new_feature TEXT DEFAULT NULL;

-- Application code:
if (agent.new_feature) { /* use it */ }
// Works before and after migration

Deployment Order:

›Deploy schema (migration)
›Deploy code (reads the new column, ignores if NULL)
›No downtime, no rollback risk

end

Pattern 2: Adding Columns with Data Migration

Requires coordination between schema deployment and data backfill.

$  snippetread-only
-- Step 1: Add column as nullable
ALTER TABLE user_credits
  ADD COLUMN IF NOT EXISTS monthly_budget INT DEFAULT NULL;

-- Step 2: Backfill data in batches (see "Data Migrations" section)
-- UPDATE user_credits SET monthly_budget = 1000 WHERE monthly_budget IS NULL;

-- Step 3: Once backfilled, make NOT NULL
ALTER TABLE user_credits
  ALTER COLUMN monthly_budget SET NOT NULL;

Deployment Order:

›Deploy Step 1 (add column nullable) → schema updated, code unaffected
›Run data migration (backfill) → data integrity restored
›Deploy Step 3 (make NOT NULL) → enforce constraint
›Code now requires the column (already exists from step 1)

end

Pattern 3: Renaming Columns (Highly Disruptive)

Avoid if possible. If necessary, use a two-migration pattern:

Migration 1:

$  snippetread-only
ALTER TABLE organisations
  ADD COLUMN IF NOT EXISTS slug_new TEXT;

-- Backfill: UPDATE organisations SET slug_new = slug;

Code Update:

$  snippetread-only
// Read from both columns for safety
const slug = org.slug_new ?? org.slug;

Migration 2 (after code deployment):

$  snippetread-only
ALTER TABLE organisations DROP COLUMN slug;
ALTER TABLE organisations RENAME COLUMN slug_new TO slug;

Deployment Order:

›Deploy migration 1 (add new column, code still reads from old)
›Deploy code (reads new column with fallback to old)
›Run backfill for new column
›Deploy migration 2 (drop old column)
›Code continues working, migration complete

end

Pattern 4: Dropping Columns (Safe with Timing)

$  snippetread-only
-- Only drop AFTER code is updated to not reference it
ALTER TABLE agents DROP COLUMN IF EXISTS legacy_field;

Prerequisite: All code paths must be updated to not read/write legacy_field first.

Deployment Order:

›Deploy code changes (remove all reads/writes of legacy_field)
›Deploy migration (drop column)
›Verify logs show no "column not found" errors

end

Pattern 5: Constraint Changes

Adding constraints (safe):

$  snippetread-only
-- If data violates the constraint, this will fail.
-- Pre-run: DELETE FROM table WHERE condition violates constraint
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Removing constraints (safe):

$  snippetread-only
ALTER TABLE users DROP CONSTRAINT users_email_unique;

Modifying CHECK constraints (requires recreation):

$  snippetread-only
-- 1. Drop old constraint
ALTER TABLE subscriptions DROP CONSTRAINT subscriptions_tier_check;

-- 2. Add new constraint
ALTER TABLE subscriptions
  ADD CONSTRAINT subscriptions_tier_check CHECK (tier IN ('pro', 'founding', 'trial'));

end

Data Migrations

Bulk Update Pattern

For updating large datasets, use batched server-side migrations to avoid timeouts.

Migration: Add Credits to Existing Users

File: /supabase/migrations/20260516_grant_initial_credits.sql

$  snippetread-only
-- Migration: Grant initial 100 credits to all users without a user_credits row
-- Assumes user_credits table exists with (user_id, credits_remaining, credits_used)

BEGIN;

-- 1. Insert missing user_credits rows with initial balance
INSERT INTO user_credits (user_id, credits_remaining, credits_used)
SELECT id, 100, 0
FROM users
WHERE NOT EXISTS (
  SELECT 1 FROM user_credits WHERE user_credits.user_id = users.id
)
ON CONFLICT (user_id) DO NOTHING;

-- 2. Verify insert
SELECT COUNT(*) AS inserted_users FROM user_credits;

COMMIT;

Execution:

$  snippetread-only
supabase db push
# Verify in dashboard: SELECT COUNT(*) FROM user_credits;

end

Server-Side Migration: One-Time Jobs

For complex logic that can't run in SQL alone, use a TypeScript script:

File: /scripts/migrate-emails.ts

$  snippetread-only
import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SECRET_KEY!
);

const BATCH_SIZE = 100;

async function migrateEmails() {
  let offset = 0;
  let totalProcessed = 0;

  while (true) {
    // Fetch batch
    const { data: users, error } = await supabase
      .from("users")
      .select("id, email")
      .is("verified_email_sent_at", null)
      .range(offset, offset + BATCH_SIZE - 1);

    if (error) {
      console.error("Query error:", error);
      break;
    }

    if (!users || users.length === 0) {
      console.log("Migration complete. Processed:", totalProcessed);
      break;
    }

    // Process batch (e.g., send verification emails)
    for (const user of users) {
      console.log(`Sending email to ${user.email}...`);
      // await sendVerificationEmail(user.email);
    }

    // Mark batch as processed
    const ids = users.map((u) => u.id);
    const { error: updateError } = await supabase
      .from("users")
      .update({ verified_email_sent_at: new Date().toISOString() })
      .in("id", ids);

    if (updateError) {
      console.error("Update error:", updateError);
      break;
    }

    totalProcessed += users.length;
    offset += BATCH_SIZE;

    // Wait between batches to avoid rate limits
    await new Promise((r) => setTimeout(r, 500));
  }
}

migrateEmails();

Execution:

$  snippetread-only
# Run manually after production migration
NEXT_PUBLIC_SUPABASE_URL=... SUPABASE_SECRET_KEY=... \
  npx tsx scripts/migrate-emails.ts

# Monitor progress in logs
# Ctrl+C to stop (resumes from last batch on rerun)

end

Verification Steps

After any data migration:

$  snippetread-only
# 1. Count rows before/after
supabase -p <project-id> query "SELECT COUNT(*) FROM users;"
supabase -p <project-id> query "SELECT COUNT(*) FROM user_credits;"

# 2. Spot-check a few rows
supabase -p <project-id> query "SELECT * FROM users LIMIT 5;"

# 3. Check for NULL values in critical columns
supabase -p <project-id> query "SELECT COUNT(*) FROM users WHERE email IS NULL;"

# 4. Run test queries matching app logic
supabase -p <project-id> query "
  SELECT u.id, u.email, uc.credits_remaining
  FROM users u
  LEFT JOIN user_credits uc ON u.id = uc.user_id
  LIMIT 10;
"

# 5. Test Server Actions (e.g., in staging)
# Call streamAgentAction, checkUserCredits, etc. to verify data integrity

end

Row Level Security (RLS) Considerations

RLS Policy Evolution

AgentZero uses RLS to enforce multi-tenancy. Policies are defined in migrations:

Current Status: RLS enabled on all tables, but all Server Actions use the adminClient which bypasses RLS via the service-role key.

When you need to add user-facing read policies, follow this pattern:

$  snippetread-only
-- Migration: 20260520_add_agent_read_policy.sql

-- Allow authenticated users to read their organisation's agents
CREATE POLICY "Users can read their org's agents"
ON agents
FOR SELECT
USING (
  organisation_id IN (
    SELECT organisation_id FROM users WHERE id = auth.uid()
  )
);

Application Layer: No changes needed if using adminClient in Server Actions (already does this check):

$  snippetread-only
// Server Action: Already org-scoped before query
const { data: agents } = await adminClient
  .from("agents")
  .select(...)
  .eq("organisation_id", session.user.orgId);  // ← manual scoping

Testing RLS Policies

$  snippetread-only
# In Supabase SQL editor, test as different users:

-- As authenticated user (with valid JWT)
SET ROLE authenticated;
SET app.current_user_id = '<uuid>';
SELECT * FROM agents;  -- Should return only org's agents

-- As service role (bypasses RLS)
SET ROLE service_role;
SELECT * FROM agents;  -- Returns all agents (no filtering)

end

API Versioning (Current: v0.1.0)

AgentZero does not currently version APIs. All endpoints are at /api/... (no version prefix).

When to Introduce Versioning

If you need to make breaking changes to a Server Action or webhook format:

Option 1: Route-Based Versioning (HTTP endpoints)

$  snippetread-only
/app/api/v1/webhooks/lemonsqueezy/route.ts
/app/api/v2/webhooks/lemonsqueezy/route.ts

Option 2: Server Action Overloading

$  snippetread-only
// lib/actions/agent-actions.ts

// Old signature (deprecated)
export async function streamAgentAction_v1(...) { ... }

// New signature
export async function streamAgentAction_v2(...) { ... }

// Alias to new version
export const streamAgentAction = streamAgentAction_v2;

Option 3: Feature Flags

$  snippetread-only
const AGENT_ACTIONS_VERSION = process.env.AGENT_ACTIONS_V ?? "2";

export async function streamAgentAction(...) {
  if (AGENT_ACTIONS_VERSION === "1") {
    return streamAgentAction_v1(...);
  } else {
    return streamAgentAction_v2(...);
  }
}

end

Dependency Updates

Node.js & NPM

$  snippetread-only
# Check for updates
npm outdated

# Update package.json versions
npm update

# Audit security vulnerabilities
npm audit

# If npm audit fails, fix manually
npm install <package>@<version>

# Commit and test
git add package.json package-lock.json
npm test
git commit -m "chore: update dependencies"

Next.js Upgrade (e.g., 16 → 17)

$  snippetread-only
# Update in package.json
npm install next@17

# Test local dev
npm run dev

# Build and check for warnings
npm run build

# Test server actions and API routes
npm start

# Check TypeScript
npx tsc --noEmit

Auth.js Upgrade (e.g., v5-beta → v5-stable)

Auth.js is used for session management. Breaking changes in version bumps:

$  snippetread-only
npm install next-auth@5.1.0

# Test login/logout/signup Server Actions
# Check session serialization in streamAgentAction, etc.

# Run full test suite if available
npm run test

end

Configuration Changes

Environment Variables

Environment variables are loaded from .env.local (local) or .env (staging/prod).

Adding a New Environment Variable

›

Document it in /ENV_TEMPLATE.txt or similar:

$  snippetread-only
RAG_MAX_RESULTS=10          # Max chunks returned from semantic search

›
Update deployment checklist for ops team

›

Add type safety if reading in TypeScript:

$  snippetread-only
const RAG_MAX_RESULTS = parseInt(process.env.RAG_MAX_RESULTS ?? "10", 10);

Rotating Secrets

If a secret (API key, webhook secret) is compromised:

›Generate new secret in external service (Lemon Squeezy, Resend, etc.)
›Update .env (local) and Vercel/hosting environment variables
›Redeploy application
›Revoke old secret in external service
›Monitor logs for old secret usage (should cease immediately)

end

Monitoring & Observability During Deployments

Pre-Deployment Checklist

›[ ] Run tests: npm run test (if tests exist)
›[ ] Build succeeds: npm run build
›[ ] No TypeScript errors: npx tsc --noEmit
›[ ] Lint passes: npm run lint
›[ ] All migrations reviewed (SQL inspection)
›[ ] Backup created in Supabase dashboard
›[ ] Staging tested with real data

During Deployment

Watch these logs in Supabase/Vercel dashboard:

$  snippetread-only
# Database logs (SQL execution)
supabase -p <project-id> logs --sql

# Application logs (errors, warnings)
# Vercel: Deployments → View Logs

# Webhook logs (if applicable)
# Check: Supabase SQL Editor → SELECT * FROM webhook_events

Check these metrics:

$  snippetread-only
# API response times
# Error rates (4xx, 5xx)
# Database connection pool usage
# Credit deduction success rate

Post-Deployment Verification

›
Test critical user flows in staging:
- ›Sign up → create agent → stream response
- ›Upload document → semantic search
- ›Purchase (webhook simulation)

›

Verify no data loss:

$  snippetread-only
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM agents;
SELECT COUNT(*) FROM documents;

›
Check logs for errors:
- ›No "Unauthorised" floods
- ›No "Insufficient credits" false positives
- ›No embedding generation failures
›
Monitor for 24 hours post-deploy before considering stable

end

Troubleshooting Deployments

Migration Fails to Apply

Error: ERROR: column "new_col" already exists

Fix: Migration is not idempotent. Ensure all statements use IF NOT EXISTS:

$  snippetread-only
-- Change from:
ALTER TABLE agents ADD COLUMN status TEXT;

-- To:
ALTER TABLE agents ADD COLUMN IF NOT EXISTS status TEXT;

Data Migration Hangs

Error: Script stops responding after N rows

Cause: Query timeout or RPC timeout

Fix: Reduce batch size:

$  snippetread-only
const BATCH_SIZE = 100;  // Try 50 or 25 instead

RLS Policy Breaks Read Access

Error: SELECT returns 0 rows after adding RLS policy

Fix: Check policy logic:

$  snippetread-only
-- Bad: policy too restrictive
CREATE POLICY "org_only" ON agents
FOR SELECT USING (FALSE);  -- Always denies

-- Good: scoped to user's org
CREATE POLICY "org_only" ON agents
FOR SELECT USING (
  organisation_id = (
    SELECT organisation_id FROM users WHERE id = auth.uid()
  )
);

Webhook Signature Fails After Secret Rotation

Error: Lemon Squeezy webhooks return 401 Unauthorized

Fix: Verify secret is updated:

$  snippetread-only
# In Lemon Squeezy dashboard:
# 1. Note the webhook secret
# 2. Update Vercel/local env var: LEMONSQUEEZY_WEBHOOK_SECRET
# 3. Redeploy
# 4. Test webhook manually (LS dashboard → Webhooks → Resend)

end

Backup & Recovery

Automated Backups

Supabase provides:

›Daily backups (free tier: 7 days retention)
›Weekly backups (paid tier: longer retention)

View backups:

$  snippetread-only
supabase -p <project-id> db ls backups

Manual Backup (Recommended Before Major Deployments)

$  snippetread-only
supabase -p <project-id> db remote backup create
# Creates timestamped backup in Supabase UI → Database → Backups

Restore from Backup

$  snippetread-only
# 1. In Supabase dashboard: Database → Backups → Restore
# 2. Select the backup timestamp
# 3. Confirm (destructive — will overwrite current data)

Export Data for Safekeeping

$  snippetread-only
# Export as CSV (for key tables)
supabase -p <project-id> query "
  COPY users TO STDOUT WITH CSV HEADER;
" > users_backup.csv

# Export as SQL (full schema + data)
pg_dump \
  postgresql://<user>:<password>@<host>:5432/<db> \
  > agentzero_full_backup.sql

end

Upgrade Path: v0.1 → v0.2 (Future)

When deploying a new major version, follow this checklist:

›
Schema migrations
- ›[ ] All new tables have RLS enabled
- ›[ ] Foreign keys reference correct tables
- ›[ ] Indexes created for query performance
›
Data compatibility
- ›[ ] Data migrations for new required columns
- ›[ ] Backfill legacy data if needed
- ›[ ] Verify no data loss
›
API compatibility
- ›[ ] Old Server Actions still work (backward compatible)
- ›[ ] New Server Actions tested thoroughly
- ›[ ] Webhook format unchanged (or migrate both versions)
›
Deployment sequence
- ›[ ] Deploy schema first (backward compatible)
- ›[ ] Deploy application code second
- ›[ ] Run data migrations (if any)
- ›[ ] Monitor for 24 hours
- ›[ ] Decommission old code paths (in v0.3)
›
Rollback plan
- ›[ ] Can revert app to v0.1 if critical issues found
- ›[ ] Schema migration can be reversed (corrective migration)
- ›[ ] Data can be restored from backup

end

Reference: Current Migration Timeline

Next migration number: 20260517000000_... (or later, depending on date)

end

Links & Resources

›Supabase CLI: https://supabase.com/docs/guides/cli
›SQL Migrations: https://supabase.com/docs/guides/migrations
›RLS Policies: https://supabase.com/docs/guides/auth/row-level-security
›pgvector: https://supabase.com/docs/guides/database/extensions/pgvector
›Lemon Squeezy Webhooks: https://docs.lemonsqueezy.com/api/webhooks