App DB Package

Overview

@zooly/app-db is the database access package for the main application. This package manages all database interactions using Drizzle ORM and enforces multi-tenant data access patterns.

Package Details

• Package Name: @zooly/app-db
• Location: packages/db
• Type: Database access layer

Why Drizzle ORM?

We use Drizzle ORM for database access for several important reasons:

• Better support for join and complex queries: Drizzle provides excellent support for complex SQL queries including joins, subqueries, and advanced query patterns that are essential for multi-tenant applications
• PostgreSQL Vector support: Drizzle has strong support for pgvector, which is crucial for applications that need vector similarity search and AI/ML features

These features make Drizzle the ideal choice for our use case, providing both type safety and the flexibility needed for complex database operations.

Key Features

• Multi-tenant enforcement: All database access is mediated through access functions that require an accountId parameter
• No direct DB exposure: The database object itself is never exported directly to other packages
• Drizzle ORM: Uses Drizzle ORM for type-safe database queries with excellent support for complex queries and pgvector
• Access layer pattern: All database operations go through the access package within the db package
• Payment system support: Includes payment-related schemas and access functions for the payment system

Architecture

The package follows a strict access pattern:

packages/db/
├── src/
│   ├── access/      # Access functions (enforce tenant filtering)
│   │   ├── stripePayments.ts          # Payment CRUD operations
│   │   ├── payOut.ts                  # Payout operations
│   │   ├── paymentShareTracking.ts    # Revenue distribution tracking
│   │   ├── accountPayoutRoutes.ts     # Payout route configuration
│   │   ├── accountPaymentSettings.ts  # Payment settings
│   │   ├── paymentSystemLogs.ts      # Audit logging
│   │   ├── ppu.ts          # Purchase code management
│   │   └── accountAgents.ts          # Agent relationships
│   ├── schema/      # Database schema definitions
│   │   ├── paymentEnums.ts           # Payment-related enums
│   │   ├── stripePayments.ts         # Payment transaction records
│   │   ├── payOut.ts                 # Payout records
│   │   ├── paymentShareTracking.ts   # Revenue distribution tracking
│   │   └── ...                       # Other schemas
│   ├── db.ts        # Database connection (not exported)
│   └── index.ts     # Public API (only exports access functions)

Access Pattern: No Direct Schema or DB Exports

This design pattern serves two critical purposes:

1. Clean Separation: By keeping the database connection and schema definitions internal to the package, we maintain a clean separation of concerns. Other packages cannot directly access the database, ensuring all interactions go through the intended access layer.

2. Multi-Tenant Enforcement: Access functions require an accountId parameter and automatically apply tenant filtering. This ensures that all queries are multi-tenant aware by design—it's impossible to bypass tenant filtering when using the access functions.

The build will fail if you attempt to use an access function without providing the required accountId parameter, making multi-tenant support a compile-time guarantee rather than a runtime check.

See Multi-tenant Design in the introduction for more details on how this pattern is enforced across the platform.

Database Operations

All database operations are performed through access functions that:

• Require an accountId parameter
• Automatically apply tenant filtering
• Fail at build time if accountId is missing

Connection Architecture (Dual Driver)

The db.ts module uses two different Drizzle drivers depending on the DATABASE_URL:

Detection is automatic — the code checks if the connection string contains neon.tech and picks the right driver. The rest of the codebase is unaffected; both drivers expose the same Drizzle API.

Why not just postgres.js everywhere?

postgres.js maintains a persistent TCP connection pool. On Vercel, every serverless cold-start creates a new pool (default max: 10 connections). Under concurrent traffic, dozens of function instances each holding 10 connections quickly exhaust the database's max_connections limit, causing pool exhaustion errors.

The @neondatabase/serverless driver solves this: regular queries go over stateless HTTP and interactive transactions use WebSocket — no persistent pool that survives across cold-starts.

Pool configuration

Neon (production):

Local dev (postgres.js):

db.execute() result helpers

Because the two drivers return different shapes from db.execute() (postgres-js returns rows directly as an array; neon-serverless wraps them in { rows: [...], rowCount }) the package exports two helpers:

• extractRows<T>(result) — returns the rows array regardless of driver.
• extractRowCount(result) — returns the affected-row count regardless of driver.

Migrations

Schema migrations (packages/db/src/migrate.ts) always use a dedicated postgres.js client with max: 1, regardless of which driver the app uses. This is intentional — Neon recommends direct connections for DDL operations.

Dependencies

• Drizzle ORM
• @neondatabase/serverless — Neon serverless driver (production)
• ws — WebSocket polyfill for Node (used by the Neon driver for transactions)
• postgres — postgres.js driver (local development + migrations)
• @zooly/types for shared types

Database Migrations

Working with Migrations

Drizzle uses migrations to manage database schema changes. Here's how to work with them:

Generate Migrations

After making changes to your schema files in src/schema/, generate migration files:

npm run db:generate

This command:

• Analyzes your schema files
• Compares them with the current database state
• Generates migration SQL files in the migrations directory
• Creates the necessary migration metadata

Apply Migrations

After making changes to your schema files in packages/db/src/schema/, generate migration files:

cd packages/db
npm run db:generate
# or
set -a && source ../../../.env.local && npm run db:generate

This command:

• Analyzes your schema files
• Compares them with the current database state
• Generates migration SQL files in packages/db/drizzle/
• Creates the necessary migration metadata

Important: Migration SQL files are committed to git so they can be applied during production deployments.

Apply Migrations Locally

For local development, apply migrations manually:

cd packages/db
npm run db:migrate:deploy
# or
set -a && source ../../../.env.local && npm run db:migrate:deploy

This will:

• Execute pending migrations in order
• Update the database schema
• Record migration history in the drizzle.__drizzle_migrations table

Development Best Practices

Scripts

• db:generate - Generate migration files from schema changes
• db:migrate - Apply pending migrations to the database
• db:push - Push schema changes directly (development only)
• db:studio - Open Drizzle Studio for database inspection