The pluggable database layer

Zero SQL in core

As of today, the SupaProxy core package contains zero SQL. No SELECT statements. No INSERT queries. No migration files. No MySQL driver in its dependency tree. The core defines what data it needs through TypeScript interfaces. A separate package provides the actual database implementation.

This is the second extraction in our composability work. Auth was first. Database is harder because it touches every layer of the system. Eleven repository interfaces. Thirty migration versions. Row mappers, pool factories, status constants. All of it had to move out of core without breaking a single test.

It did not break a single test. All 369 pass. Here is how we did it and, more importantly, why.

The problem with owning your persistence layer

When core owns the MySQL implementation, it owns your database choice. If you run PostgreSQL, you cannot use SupaProxy without rewriting the persistence layer inside the server. If you run SQLite for edge deployments, the same problem. If your company mandates a specific database for compliance reasons, you are stuck.

This is not hypothetical. Every AI operations platform we have looked at makes the same trade-off. They pick one database and bake it in. The documentation says "requires PostgreSQL 14+" or "requires MongoDB 6.0+". If that does not match your stack, you either adopt their database or you do not adopt their platform.

We had the same problem. SupaProxy required MySQL 8. That was a decision we made on day one because MySQL was what we knew. It was not a decision our users should be forced to live with.

The DatabaseAdapter contract

Core now exports a single interface called DatabaseAdapter. It lists every repository a database backend must provide. Each repository is itself an interface with typed methods. TypeScript enforces the contract at compile time.

import type { DatabaseAdapter } from '@supaproxy/core/ports/database'

export interface DatabaseAdapter {
  orgRepo: OrganisationRepository
  workspaceRepo: WorkspaceRepository
  conversationRepo: ConversationRepository
  auditRepo: AuditLogRepository
  modelRepo: ModelRepository
  promptTemplateRepo: PromptTemplateRepository
  guardrailEventRepo: GuardrailEventRepository
  guardrailPolicyRepo: GuardrailPolicyRepository
  integrationRepo: IntegrationRepository
  entryPointRepo: EntryPointRepository
  knowledgeChunkRepo: KnowledgeChunkRepository
}

Each repository interface lives in its own domain module. OrganisationRepository defines findById, create, updateName, findUserByEmail, and so on. WorkspaceRepository defines findById, create, update, listNonArchived, findConnections, findTools. The full surface area is over a hundred methods across the repository interfaces.

The database adapter package implements all 120 methods with actual SQL. Core never sees the SQL. It calls orgRepo.findById and gets an OrgData object back. Whether that object came from a MySQL query, a PostgreSQL query, or a flat file is invisible to every use case, every route handler, and every test.

How it composes

The server entrypoint is now a composition root. It imports the pieces it needs and wires them together.

import { createContainer, createApp } from '@supaproxy/core'
import { createAuthRoutes } from '@supaproxy/auth'
import { createMysqlInfra, runMigrations } from '@supaproxy/mysql'

const pool = getPool()
await runMigrations(pool)

const infra = createMysqlInfra(pool)
const { routes, requireAuth } = createAuthRoutes({ ... })
const container = createContainer(infra, { pool, authRoutes: routes, requireAuth, ... })
const app = createApp(container)
serve({ fetch: app.fetch, port: 3001 })

To switch from MySQL to PostgreSQL, you change two lines. The import and the factory call. Everything else stays the same. The container does not know. The use cases do not know. The routes do not know.

// Swap MySQL for PostgreSQL. Two lines change.
import { createPostgresInfra, runMigrations } from '@supaproxy/postgres'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
await runMigrations(pool)
const infra = createPostgresInfra(pool)

What the reference implementation looks like

The @supaproxy/mysql package is not just an adapter. It is a reference implementation. We designed it so that someone building @supaproxy/postgres can copy the structure file by file.

Every repository follows the same pattern. Import the domain interface from core. Import row types from a companion mapper file. Implement the interface with SQL. Map database rows to domain types. The mapper file owns the row type definitions and the transformation functions. The repository file owns the queries.

// MysqlOrganisationRepository.ts
import type { OrganisationRepository, OrgData } from '@supaproxy/core/domain/organisation'
import type { OrgRow, UserRow, SettingRow, ... } from './OrganisationRowMappers.js'

export class MysqlOrganisationRepository implements OrganisationRepository {
  constructor(private readonly pool: mysql.Pool) {}

  async findById(id: string): Promise<OrgData | null> {
    const [rows] = await this.pool.execute<OrgRow[]>(
      'SELECT id, name, slug, created_at FROM organisations WHERE id = ?', [id]
    )
    return rows[0] || null
  }
}

The pattern is consistent across all eleven repositories. Nine have companion mapper files. The AuditLogRepository is the exception because it has a single method with no row types. Someone building a new adapter starts with the audit log repo to learn the pattern, then works through the rest.

Migrations belong to the adapter

This was the design decision that took the longest to make. Who owns the database schema? Does core define what tables must exist? Or does the adapter define its own schema?

We chose the adapter. The @supaproxy/mysql package ships 30 versioned migrations. They create the tables, add columns, migrate data, build indexes. A @supaproxy/postgres package would ship equivalent migrations in PostgreSQL dialect. A @supaproxy/sqlite package would ship SQLite-compatible DDL.

Core defines the schema contract implicitly through the repository interfaces. If OrganisationRepository has a findById method that returns an OrgData with id, name, slug, and created_at, then the database must have those columns somewhere. Core does not care if it is a single table, a view, a join, or a NoSQL document. The adapter makes that decision.

This means different adapters can make different storage decisions. A PostgreSQL adapter might use JSONB columns where the MySQL adapter uses separate tables. A SQLite adapter might denormalise aggressively because SQLite does not love joins. The repository interface is the boundary. Everything behind it is the adapter author is call.

The benefits we see

The obvious benefit is database portability. But there are three others that matter more in practice.

First, testability. With zero SQL in core, every test mocks at the repository interface boundary. No test database needed. No Docker containers for CI. The mock is five lines per repository. The entire test suite runs in under three seconds. This was true before the extraction because we already mocked at the interface boundary. But now the architecture enforces it. You cannot accidentally write a test that reaches through to MySQL because MySQL does not exist in core.

Second, contributor isolation. Someone building a PostgreSQL adapter does not need to understand the agent loop, the guardrail resolver, or the routing system. They need to understand eleven TypeScript interfaces and the SQL to implement them. The surface area is narrow and well-typed. If they implement the interface and the tests pass, the adapter works.

Third, deployment flexibility. A self-hosted deployment on a Raspberry Pi might use SQLite and skip Redis entirely. A cloud deployment might use Aurora PostgreSQL with read replicas. An enterprise deployment might use their existing Oracle database through a custom adapter. The core does not change. The composition root changes.

The costs and risks

There are real costs. We are not pretending this is free.

The first cost is indirection. When you are debugging a failing query, the stack trace crosses a package boundary. The use case is in core. The SQL is in the adapter. The row mapper is in a separate file in the adapter. Three files across two packages for what used to be one function call in one file. We think this is manageable because the interfaces are well-named and the pattern is consistent. But it is more cognitive load.

The second cost is interface evolution. When we add a new method to WorkspaceRepository, every adapter package must implement it. Today there is only @supaproxy/mysql, so this is trivial. When there are three or four community adapters, a breaking interface change requires coordinated updates. We will version the interfaces and provide migration guides. But the coordination cost is real.

The third cost is the risk of interface leakage. The repository interfaces were designed around MySQL capabilities. findConsumerBoundToChannel uses JSON_CONTAINS in the MySQL implementation. Can every database do that? Probably, with different syntax. But the interface was shaped by what MySQL can do, not by what is universally portable. If we add a method that relies on a MySQL-specific feature like window functions or recursive CTEs, adapter authors will have to find equivalent approaches for their database.

The fourth cost is one we accepted knowingly. Consistency across adapters. Two different adapters implementing the same interface might have subtly different behaviour around edge cases. NULL handling, transaction isolation, JSON parsing, date formatting. The interfaces define the types but not the semantics. We cannot guarantee that switching from MySQL to PostgreSQL preserves every edge case. Integration tests per adapter are the mitigation, but they will never be exhaustive.

Security considerations

Decoupling the database layer has security implications that go both ways.

On the positive side, core now has a smaller attack surface. There is no SQL injection risk in core because there is no SQL. The adapter package owns all query construction. Security audits for SQL injection focus on one package, not the entire codebase. Parameterised queries are the adapter responsibility, enforced by code review at the adapter level.

On the concerning side, community adapters introduce supply chain risk. A malicious or poorly-written adapter could exfiltrate data, skip parameterisation, or introduce vulnerabilities. The DatabaseAdapter interface does not and cannot enforce security properties. It enforces types.

Our mitigation is transparency. The reference adapter is MIT-licensed and publicly auditable. Community adapters are community-maintained. We will document security requirements for adapter authors, specifically parameterised queries, input validation at the SQL boundary, and connection pooling best practices. But we cannot enforce them architecturally. This is the trade-off of an open adapter system.

The validation test suite

To help adapter authors, core exports a test helper that verifies a DatabaseAdapter implementation at runtime.

import { validateDatabaseAdapter } from '@supaproxy/core/testing/validate-adapter'
import { createPostgresInfra } from '../src/index.js'

const pool = getTestPool()
const adapter = createPostgresInfra(pool)
validateDatabaseAdapter(adapter)

The validator checks that every repository key exists and every required method is a function. It does not test behaviour. It tests structure. Behaviour testing is the adapter responsibility using their own database. But the structural test catches the most common mistake, forgetting to implement a method or returning the wrong shape.

What is next

The database layer is extracted. The queue layer is next. BullMQ will move to @supaproxy/bullmq with a QueueService interface. Then LanceDB to @supaproxy/lancedb with a VectorStore interface. Then Redis sessions to @supaproxy/redis with a SessionStore interface.

When all four are done, core will have zero infrastructure dependencies. No mysql2. No bullmq. No ioredis. No lancedb. The core package will contain domain interfaces, use cases, route handlers, and the composition root. Everything else will be a choice.

The composition root itself will eventually move out of core. It will become the host application is responsibility. Core will export createContainer and createApp. The host will import its adapters and wire them. Our open-source index.ts is one host. Our cloud is another. An embedded deployment inside an existing Express app would be a third.

The database extraction is not the end of the composability work. It is the proof that the approach works at the hardest layer. Auth was easier because it has a small surface area. The database touches every use case. If we can extract it cleanly, we can extract anything.

We are publishing this now because the philosophy site is a design journal. These are the decisions we made this week, the architecture we committed to, and the trade-offs we are living with. If you see something we missed or a risk we underestimated, we want to know.