App DB Package

PreviousNext

Database access layer for the main app

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

Dependencies

  • Drizzle ORM
  • PostgreSQL driver
  • @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