PostgreSQL Migration: Why We Dropped SQLite for Writes

The Problem: Architectural Mismatch

Satellite Infrastructure Write Patterns

DeployStack's satellite architecture creates fundamentally different database load patterns than typical web applications:

Continuous Write Operations:

Satellite Heartbeats: Every active satellite writes heartbeat data every 30 seconds
Activity Tracking: Real-time logging of MCP tool executions across all satellites
Process Lifecycle Events: Start/stop/crash events for stdio MCP servers
Team Metrics: Per-team, per-satellite usage tracking
Background Jobs: Job queue state changes, results storage, cleanup operations
Audit Logs: Security and compliance event logging

Scaling Characteristics:

100 satellites -> 2 writes/min (heartbeat) = 200 writes/min baseline
+ MCP tool execution logging: 10 tools/min -> 100 satellites = 1,000 writes/min
+ Process lifecycle events, metrics, job queue updates
= 50-200 writes/second minimum at moderate scale

This is not a read-heavy web application. Both reads and writes are high-frequency operations.

SQLite/Turso Architectural Constraint

Single-Writer Serialization:

SQLite's architecture serializes all write operations
One write blocks all others, regardless of threading
SQLITE_BUSY errors occur under concurrent load
5-second transaction timeout to prevent permanent blocking
~150k rows/second maximum throughput with zero improvement from additional threads

Turso's MVCC Implementation (Concurrent Writes) 2025-11-28:

Technology preview status
Does not support CREATE INDEX operations
Substantial memory overhead (stores complete row copies, not deltas)
No asynchronous I/O (limits scalability)
No production timeline announced

The Decision: PostgreSQL-Only Architecture

Technical Rationale

1. Production-Ready Concurrent Writes

PostgreSQL's MVCC implementation has been battle-tested in production for decades:

True multi-writer parallelism without serialization
Multiple transactions write simultaneously without blocking
No SQLITE_BUSY errors or artificial timeouts
Proven performance with thousands of writes per second

2. Architectural Fit for Distributed Systems

The satellite infrastructure maps well to PostgreSQL's design:

Connection pooling works efficiently with distributed satellites (PgBouncer, built-in pooling)
Proven performance in microservices architectures
Better handling of high-concurrency scenarios than single-writer databases
Mature operational patterns for distributed deployments

3. Operational Maturity

PostgreSQL provides complete production tooling:

Reliable monitoring (pgAdmin, DataGrip, pganalyze, pg_stat_statements)
Point-in-time recovery capabilities
Streaming replication for high availability
Mature backup and recovery tools (`pg_dump`, WAL-E, `pgBackRest`)
Extensive production experience across industry

Migration Architecture

Schema Migration Strategy

The migration leveraged Drizzle ORM's database abstraction to minimize changes:

Before (Multi-Database):

// Conditional driver selection
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';

// Runtime driver switching based on configuration
const db = selectedType === 'sqlite'
  ? drizzle(sqliteClient, { schema })
  : drizzle(postgresClient, { schema });

After (PostgreSQL-Only):

// Single driver implementation
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({
  host: config.host,
  port: config.port,
  database: config.database,
  user: config.user,
  password: config.password,
  ssl: config.ssl ? { rejectUnauthorized: false } : false
});

const db = drizzle(pool, { schema });

Type System Changes

SQLite/Turso Compromises:

Timestamps stored as integers (Unix epoch)
Booleans stored as integers (0/1)
No native JSONB support
Limited type safety

PostgreSQL Native Types:

timestamp with timezone for proper datetime handling
Native boolean type
jsonb for efficient JSON storage with indexing
Arrays and custom types
Full-text search capabilities

Schema Files

Removed:

src/db/schema.sqlite.ts - SQLite-specific schema
drizzle/migrations_sqlite/ - SQLite migration history
Multi-database conditional logic throughout codebase

Retained:

src/db/schema.ts - PostgreSQL-only schema (renamed from schema.postgres.ts)
drizzle/migrations/ - PostgreSQL migration files
src/db/schema-tables/ - Modular table definitions

Migration Directory Structure

services/backend/
├─ drizzle/
   ├─ migrations/              # PostgreSQL migrations only
       ├─ 0000_perfect_rogue.sql
       ├─ 0001_wild_selene.sql
       ├─ meta/
├─ src/db/
    ├─ schema.ts                # PostgreSQL schema (single source of truth)
    ├─ schema-tables/           # Modular table definitions
       ├─ auth.ts
       ├─ teams.ts
       ├─ mcp-catalog.ts
       ├─ ...
    ├─ config.ts                # PostgreSQL configuration only
    ├─ index.ts                 # Database initialization

Technical Implementation Changes

1. Database Configuration

Removed Multi-Database Selection:

// Before: Complex type switching
type DatabaseType = 'sqlite' | 'turso' | 'postgresql';
interface DatabaseConfig {
  type: DatabaseType;
  // ... conditional fields based on type
}

Simplified PostgreSQL Configuration:

// After: PostgreSQL-only configuration
interface DatabaseConfig {
  type: 'postgresql';
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
  ssl: boolean;
}

2. Query Result Handling

Removed Conditional Logic:

// Before: Handle different result formats
const deleted = (result.changes || result.rowsAffected || 0) > 0;

PostgreSQL-Specific Pattern:

// After: Use PostgreSQL's rowCount
const deleted = (result.rowCount || 0) > 0;

3. Session Table Schema Fix

Fixed a critical bug introduced during multi-database support where Drizzle ORM property names didn't match usage:

Schema Definition (Correct):

export const authSession = pgTable('authSession', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull(),      // TypeScript property: userId
  expiresAt: bigint('expires_at').notNull() // TypeScript property: expiresAt
});

Code Usage (Was Incorrect):

// Before (WRONG - used snake_case):
await db.insert(authSession).values({
  id: sessionId,
  user_id: userId,        // L Wrong! Should be userId
  expires_at: expiresAt   // L Wrong! Should be expiresAt
});

// After (CORRECT - use camelCase):
await db.insert(authSession).values({
  id: sessionId,
  userId: userId,         //  Matches TypeScript property
  expiresAt: expiresAt    //  Matches TypeScript property
});

This affected:

`registerEmail.ts` - User registration
`loginEmail.ts` - Email login
`github.ts` - GitHub OAuth (2 locations)

4. Plugin System Updates

Plugin Table Access:

Plugin tables are created dynamically in the database but not exported from the schema. Updated plugins to use raw SQL:

// Before: Tried to access from schema (failed)
const schema = getSchema();
const table = schema[`${pluginId}_${tableName}`]; // L TypeScript error 7053

// After: Use raw SQL queries
const tableName = `${pluginId}_example_entities`;
const result = await db.execute(
  sql.raw(`SELECT COUNT(*) as count FROM "${tableName}"`)
);

Plugin Schema Type Inference:

The mock column builder auto-detects types based on column names:

Column name contains "id" INTEGER type
Column name contains "at" or "date" TIMESTAMP type
Default TEXT type

Fixed plugin seed data to match inferred types:

// Before (WRONG - text ID for INTEGER column):
VALUES ('example1', 'Example Entity', ...)

// After (CORRECT - integer ID):
VALUES (1, 'Example Entity', ...)

5. API Spec Generation

Added flags to skip database/plugin initialization during OpenAPI spec generation:

// Skip database initialization for API spec generation
process.env.SKIP_DATABASE_INIT = 'true';
process.env.SKIP_PLUGIN_INIT = 'true';

This allows spec generation without database connectivity and excludes plugin routes.

Performance Characteristics

Write Performance Comparison

PostgreSQL (After):

Thousands of writes/second baseline
Scales with CPU cores (parallel writes)
No artificial timeout limits
No blocking errors under normal load

Satellite Workload Handling

Scenario: 100 satellites at moderate activity

Estimated load: 50-200 writes/second
SQLite/Turso: At the edge of recommended limits
PostgreSQL: Well within comfortable operating range

Complexity Reduction

Removed Code

Multi-Database Abstraction Layer:

Conditional driver selection logic
Result format normalization
Type system compatibility layers
SQLite-specific workarounds

SQLite Migration Files:

18 migration files removed from drizzle/migrations_sqlite/
Migration metadata and journal files removed
SQLite schema definition removed

Configuration Complexity:

Removed database type selection logic
Removed conditional environment variable handling
Simplified database setup flow

Simplified Maintenance

Single Migration Path:

One migration directory (drizzle/migrations/)
One schema file (src/db/schema.ts)
One set of type definitions
One testing strategy

Reduced Testing Surface:

No multi-database test matrix
No driver compatibility testing
No type conversion testing
Focus on PostgreSQL-specific optimizations

Operational Impact

Development Workflow

Before:

Choose database type (SQLite/Turso/PostgreSQL)
Configure appropriate environment variables
Handle type differences in code
Test across multiple database backends

After:

Configure PostgreSQL environment variables
Run migrations
Use consistent PostgreSQL patterns
Test single database backend

Deployment Changes

Environment Variables:

# Before: Type selection required
DATABASE_TYPE=turso
TURSO_DATABASE_URL=libsql://...
TURSO_AUTH_TOKEN=...

# After: PostgreSQL only
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=postgres
POSTGRES_PASSWORD=password
POSTGRES_SSL=false

Docker Compose:

# Before: Optional SQLite, required for Turso/PostgreSQL
services:
  backend:
    environment:
      DATABASE_TYPE: turso
      TURSO_DATABASE_URL: ${TURSO_DATABASE_URL}

# After: PostgreSQL service always required
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: deploystack
      POSTGRES_USER: deploystack
      POSTGRES_PASSWORD: password
    volumes:
      - postgres_data:/var/lib/postgresql/data

  backend:
    environment:
      POSTGRES_HOST: postgres
      POSTGRES_PORT: 5432
      POSTGRES_DATABASE: deploystack

Monitoring and Observability

PostgreSQL Advantages:

pg_stat_statements for query performance analysis
Native metrics export to Prometheus/Grafana
Complete tooling (pgAdmin, DataGrip, pganalyze)
Better visibility into concurrent operations