Database Management - DeployStack - Enterprise Control Plane for MCP

Overview

DeployStack uses PostgreSQL as its database backend, providing enterprise-grade reliability, ACID compliance, and excellent performance. The system leverages Drizzle ORM for type-safe database operations with a modern, developer-friendly experience. PostgreSQL provides:

ACID Compliance - Full transactional support with rollback capabilities
Connection Pooling - Efficient connection management via node-postgres
Native Type System - Boolean, timestamp with timezone, JSONB, arrays, and more
Horizontal Scaling - Read replicas and partitioning for production deployments

Database Setup and Configuration

The backend uses an environment-based configuration system where database credentials are provided via environment variables, and the database is initialized through the setup API.

Setup Instructions: For step-by-step setup instructions, see the Database Setup Guide.

PostgreSQL Technical Guide: For detailed technical information, see the PostgreSQL Development Guide.

Environment Variables

Configure PostgreSQL by setting these environment variables:

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=your_user
POSTGRES_PASSWORD=your_password
POSTGRES_SSL=false  # Set to 'true' for SSL connections

Database Status

Check the current status of the database configuration and initialization:

Endpoint: GET /api/db/status
Method: GET
Response: JSON object with database status information

{
  "configured": true,
  "initialized": true,
  "dialect": "postgresql"
}

Initial Database Setup

The initial database setup is performed through the frontend setup wizard at /setup, which provides a user-friendly interface. The wizard internally calls the backend API:

Frontend URL: https://<your-frontend-url>/setup
Backend Endpoint (called by frontend): POST /api/db/setup
Method: POST
Request Body: JSON object specifying the database type

Note for Developers: While you can call the API endpoint directly for testing, end-users should always use the frontend setup wizard for proper initialization.

Setup Request

{
  "type": "postgresql"
}

API Response

The setup endpoint returns a JSON response indicating success and restart requirements: Successful Setup:

{
  "message": "Database setup successful. All services have been initialized and are ready to use.",
  "restart_required": false,
  "database_type": "postgresql"
}

Setup with Restart Required (Fallback):

{
  "message": "Database setup successful, but some services may require a server restart to function properly.",
  "restart_required": true,
  "database_type": "postgresql"
}

Database Selection File

The database configuration is stored in:

services/backend/persistent_data/db.selection.json (relative to the backend service directory)

This file is automatically created and managed by the setup API when users complete the frontend setup wizard at https://<your-frontend-url>/setup. Manual editing is not recommended. Example content:

{
  "type": "postgresql",
  "selectedAt": "2025-01-02T18:22:15.000Z",
  "version": "1.0"
}

Important: This file is created during the initial setup process through the frontend wizard, which internally calls the /api/db/setup endpoint.

Architecture

Key Components

Drizzle ORM: Type-safe ORM with native driver support
node-postgres (pg): Native PostgreSQL driver with connection pooling
Environment Configuration: Database credentials via environment variables
Automatic Migrations: Migrations applied on server startup

Database Driver

The system uses the native PostgreSQL driver for optimal performance:

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

// PostgreSQL connection pool
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 });

Database Connection Patterns

When accessing the database in route handlers, always use getDb() to obtain the database connection:

import { getDb } from '../../../db';
import { YourService } from '../../../services/yourService';

export default async function yourRoute(server: FastifyInstance) {
  const db = getDb();
  const yourService = new YourService(db);
  // ... rest of route handler
}

Why this pattern is required:

server.db may be null during certain initialization states
getDb() always returns the active database connection
This ensures consistent behavior across all endpoints

Avoid: Direct usage of server.db as it can cause “Cannot read properties of null” errors.

Database Operations

Working with Query Results

PostgreSQL operations (INSERT, UPDATE, DELETE) return a result object with rowCount indicating the number of affected rows:

// PostgreSQL result object
{
  rowCount: 1,           // Number of affected rows
  rows: [],              // Returned rows from SELECT queries
  command: 'DELETE',     // SQL command type
  oid: 0,
  fields: []
}

Standard Patterns

Delete Operations:

export class McpInstallationService {
  async deleteInstallation(id: string): Promise<boolean> {
    const result = await this.db
      .delete(mcpServerInstallations)
      .where(eq(mcpServerInstallations.id, id));

    return (result.rowCount || 0) > 0;
  }
}

Update Operations:

export class TeamService {
  async updateTeamName(id: string, name: string): Promise<boolean> {
    const result = await this.db
      .update(teams)
      .set({ name, updated_at: new Date() })
      .where(eq(teams.id, id));

    return (result.rowCount || 0) > 0;
  }
}

Counting Affected Rows:

export class TokenService {
  async revokeExpiredTokens(): Promise<number> {
    const result = await this.db
      .delete(oauthAccessTokens)
      .where(lt(oauthAccessTokens.expires_at, Date.now()));

    return result.rowCount || 0;
  }
}

Database Structure

DeployStack uses PostgreSQL-native types and features:

Schema Files

src/db/schema.ts - PostgreSQL schema definition

Native PostgreSQL types (boolean, timestamp with timezone, jsonb)
Proper foreign key relationships and constraints
Migration directory: drizzle/migrations/

The schema contains:

Core application tables (users, teams, MCP configurations, etc.)
Background job queue tables (queueJobs and queueJobBatches) - see Background Job Queue
Plugin table definitions (populated dynamically)
Proper foreign key relationships and constraints

Making Schema Changes

Follow these steps to add or modify database tables:

Modify Schema Definitions Edit src/db/schema-tables/[table-group].ts:

// Example: src/db/schema-tables/teams.ts
import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';

export const projects = pgTable('projects', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

Generate Migration Run the migration generation command:
npm run db:generate
This generates SQL migration files in drizzle/migrations/.
Review Migrations Examine the generated SQL files in drizzle/migrations/ to ensure they match your intended changes.
Apply Migrations Migrations are automatically applied on server startup. You can also run them manually:
npm run db:up

Use the New Schema Update your application code to use the new tables:

// Example: Using the new table in a route
app.get('/api/projects', async (request, reply) => {
  const projects = await request.db.select().from(schema.projects);
  return projects;
});

Migration Management

Automatic Tracking: Migrations tracked in __drizzle_migrations table
Incremental Application: Only new migrations are applied
Transaction Safety: Migrations applied in transactions for consistency
Execution Timing: Migrations are applied automatically on server startup, but only after the database has been initialized through the setup process

Important: Migrations cannot run until the database exists. The initial setup (via frontend wizard at /setup) must be completed first to create the database, then migrations will apply on subsequent server startups.

Global Settings Integration

During database setup, DeployStack automatically initializes global settings that configure the application:

Automatic Initialization

The global settings system:

Loads setting definitions from all modules in src/global-settings/
Creates setting groups for organizing configuration options
Initializes default values for all settings with proper encryption
Uses efficient batch operations with PostgreSQL connection pooling

Global Settings Documentation: For detailed information about global settings, see the Global Settings Guide.

Plugin Database Extensions

Plugins can extend the database with their own tables and settings. For detailed information about plugin database integration, including table definitions, dynamic table creation, and security boundaries, see the Plugin System Guide. Key plugin database features:

Dynamic table creation at runtime (separate from core migrations)
Automatic table prefixing with plugin IDs
Security isolation between core and plugin tables
Global settings integration for plugin configuration

Development Workflow

Environment Setup: Configure PostgreSQL environment variables
Initial Setup: Complete the frontend setup wizard at /setup (for first-time setup)
- This creates persistent_data/db.selection.json
- Initializes the PostgreSQL database
- For development, you can also directly call POST /api/db/setup
Schema Changes: Modify src/db/schema-tables/ directory
Generate Migrations: Run npm run db:generate
Apply Changes: Restart server or run npm run db:up
Update Code: Use the modified schema in your application

PostgreSQL-Specific Features

Connection Pooling

Efficient connection management via node-postgres
Configurable pool size and timeout settings
Automatic connection recycling

Native Types

Boolean columns with native boolean type
Timestamps with timezone support
JSONB for efficient JSON storage
Arrays and custom types

Advanced Features

Multi-version concurrency control (MVCC)
Point-in-time recovery and continuous archiving
Full-text search capabilities
Horizontal scaling with read replicas

Inspecting the Database

# Using psql CLI
psql -h localhost -U your_user -d deploystack

# Common psql commands
\dt              # List all tables
\d tablename     # Describe table structure
\q               # Quit

# Using pgAdmin (GUI)
# Download from: https://www.pgadmin.org/

Environment Configuration Examples

Development

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=postgres
POSTGRES_PASSWORD=development_password
POSTGRES_SSL=false

Production with SSL

POSTGRES_HOST=production-host.example.com
POSTGRES_PORT=5432
POSTGRES_DATABASE=deploystack
POSTGRES_USER=deploystack_user
POSTGRES_PASSWORD=secure_production_password
POSTGRES_SSL=true

Docker Compose

services:
  postgres:
    image: postgres:18-alpine
    environment:
      POSTGRES_DB: deploystack
      POSTGRES_USER: deploystack
      POSTGRES_PASSWORD: your_secure_password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

  backend:
    build: ./services/backend
    environment:
      POSTGRES_HOST: postgres
      POSTGRES_PORT: 5432
      POSTGRES_DATABASE: deploystack
      POSTGRES_USER: deploystack
      POSTGRES_PASSWORD: your_secure_password
      POSTGRES_SSL: false
    depends_on:
      - postgres

volumes:
  postgres_data:

​Overview

​Database Setup and Configuration

​Environment Variables

​Database Status

​Initial Database Setup

​Setup Request

​API Response

​Database Selection File

​Architecture

​Key Components

​Database Driver

​Database Connection Patterns

​Database Operations

​Working with Query Results

​Standard Patterns

​Database Structure

​Schema Files

​Making Schema Changes

​Migration Management

​Global Settings Integration

​Automatic Initialization

​Plugin Database Extensions

​Development Workflow

​PostgreSQL-Specific Features

​Connection Pooling

​Native Types

​Advanced Features

​Inspecting the Database

​Environment Configuration Examples

​Development

​Production with SSL

​Docker Compose