Database Architecture

The Ivyi platform uses PostgreSQL as its primary database, leveraging Drizzle ORM for type-safe database operations and PgBoss for queue management. This combination provides robust data persistence, ACID compliance, and powerful background job processing capabilities.

📑 Table of Contents

📖 Overview

Why PostgreSQL?

PostgreSQL was chosen as the database for the Ivyi platform for several key reasons:

Key Benefits

  1. Queue Integration: PgBoss leverages PostgreSQL for reliable job queuing
  2. Type Safety: Drizzle provides compile-time type checking
  3. Performance: Optimized for complex queries and transactions
  4. Reliability: Proven track record in production environments
  5. Flexibility: Supports both relational and document data patterns

🏗️ Architecture

Database Layer Architecture

┌─────────────────────────────────────────────────────────┐
│                 Application Layer                        │
│                                                         │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐     │
│  │   Features  │  │  Workflows  │  │   Workers    │     │
│  │             │  │             │  │             │     │
│  │ • Users     │  │ • Gifting   │  │ • PgBoss     │     │
│  │ • Gifts     │  │ • Process   │  │ • Jobs       │     │
│  │ • Relations │  │ • State     │  │ • Events     │     │
│  └─────────────┘  └─────────────┘  └─────────────┘     │
│         │                 │                 │         │
│         ▼                 ▼                 ▼         │
│  ┌─────────────────────────────────────────────────┐ │
│  │              Drizzle ORM Layer                   │ │
│  │                                                 │ │
│  │ • Type Safety • Schema Validation • Migrations │ │
│  │ • Query Builder • Transactions • Relations     │ │
│  └─────────────────────────────────────────────────┘ │
│                         │                             │
│                         ▼                             │
│  ┌─────────────────────────────────────────────────┐ │
│  │              PostgreSQL Database                  │ │
│  │                                                 │ │
│  │ • Tables • Indexes • Constraints • PgBoss       │ │
│  │ • JSON/JSONB • Enums • Triggers • Views         │ │
│  └─────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────┘

Database Configuration

// lib/drizzle/index.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { dbConfig } from "@/config";

const pool = new Pool({
  connectionString: dbConfig.postgres.url,
});

export const db = drizzle(pool, {
  schema: {
    ...combinedUsersSchema,
    ...combinedOccasionsSchema,
    ...combinedGiftsSchema,
    ...combinedGiftProcessSchema,
    ...combinedNotificationsSchema,
    ...combinedRelationshipSchema,
    ...combinedProductSchema,
    ...combinedPartnerSchema,
    ...combinedClassifierSchema,
  },
  logger: process.env.NODE_ENV !== "production",
});

🗄️ PostgreSQL Setup

Database Configuration

The PostgreSQL database is configured with the following key settings:

PgBoss Integration

PgBoss uses PostgreSQL as its job queue backend, providing:

Database Extensions

-- Essential extensions for Ivyi platform
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";    -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pg_trgm";       -- Text search
CREATE EXTENSION IF NOT EXISTS "btree_gin";     -- GIN indexes
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"; -- Query statistics

🔧 Drizzle ORM Integration

Schema Organization

Drizzle schemas are organized by domain and workflow:

// Feature schemas (self-contained domains)
feature/
├── users/users.schema.ts           # User management
├── gifts/gifts.schema.ts           # Gift catalog
├── occasions/occasions.schema.ts    # Event management
├── relationships/relationships.schema.ts  # User connections
├── notifications/notifications.schema.ts    # Notifications
├── products/products.schema.ts     # Product catalog
├── partners/partners.schema.ts     # Partner integration
└── classifier/classifier.schema.ts # Gift classification

// Workflow schemas (cross-domain processes)
workflows/
└── gifting-process/
    └── gifting-process.schema.ts   # Gifting workflow state

Schema Definition Pattern

Each schema follows a consistent pattern:

// Example: users.schema.ts
import { pgTable, uuid, timestamp, text, boolean } from "drizzle-orm/pg-core";

export const usersSchema = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").unique().notNull(),
  name: text("name").notNull(),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Combined schema for export
export const combinedUsersSchema = {
  usersSchema,
};

Type Safety Benefits

Drizzle provides compile-time type checking:

// Type-safe queries
const user = await db
  .select()
  .from(usersSchema)
  .where(eq(usersSchema.email, "user@example.com"))
  .limit(1);

// Type-safe inserts
const newUser = await db
  .insert(usersSchema)
  .values({
    email: "new@example.com",
    name: "New User",
  })
  .returning();

📊 Schema Organization

Feature Schemas

Users Schema

Gifts Schema

Relationships Schema

Classifier Schema

Workflow Schemas

Gifting Process Schema

Schema Relationships

erDiagram
    users ||--o{ occasions : creates
    users ||--o{ relationships : has
    users ||--o{ gift_processes : recipient
    occasions ||--o{ gift_processes : triggers
    gifts ||--o{ gift_processes : selected_in
    relationships ||--o{ gift_processes : context
    classifier_questions ||--o{ process_answers : answered_in

🔄 Database Operations

Query Patterns

Basic CRUD Operations

// Create
const user = await db
  .insert(usersSchema)
  .values({
    email: "user@example.com",
    name: "John Doe",
  })
  .returning();

// Read
const users = await db
  .select()
  .from(usersSchema)
  .where(eq(usersSchema.isActive, true));

// Update
const updated = await db
  .update(usersSchema)
  .set({ name: "Jane Doe" })
  .where(eq(usersSchema.id, userId))
  .returning();

// Delete
const deleted = await db
  .delete(usersSchema)
  .where(eq(usersSchema.id, userId))
  .returning();

Complex Queries

// Join operations
const usersWithGifts = await db
  .select({
    userId: usersSchema.id,
    userName: usersSchema.name,
    giftTitle: giftsSchema.title,
  })
  .from(usersSchema)
  .leftJoin(
    giftProcessesSchema,
    eq(usersSchema.id, giftProcessesSchema.recipientId),
  )
  .leftJoin(giftsSchema, eq(giftProcessesSchema.selectedGiftId, giftsSchema.id))
  .where(eq(usersSchema.isActive, true));

// Aggregations
const giftStats = await db
  .select({
    category: giftCategoriesSchema.name,
    count: count(giftsSchema.id),
    avgPrice: avg(giftsSchema.price),
  })
  .from(giftsSchema)
  .innerJoin(
    giftCategoriesSchema,
    eq(giftsSchema.categoryId, giftCategoriesSchema.id),
  )
  .groupBy(giftCategoriesSchema.id);

Transaction Support

// Transaction for complex operations
await db.transaction(async (tx) => {
  // Create user
  const user = await tx.insert(usersSchema).values(userData).returning();

  // Create initial relationship
  await tx.insert(relationshipsSchema).values({
    userId: user[0].id,
    relatedUserId: systemUserId,
    relationshipType: "SYSTEM",
  });

  // Send welcome notification
  await tx.insert(notificationsSchema).values({
    userId: user[0].id,
    type: "WELCOME",
    message: "Welcome to Ivyi!",
  });
});

🌱 Seeding & Migrations

Database Seeding

The platform uses a structured seeding system:

// lib/drizzle/seed.ts
async function getSeedFiles(): Promise<string[]> {
  const files = await glob("src/feature/**/*.seed.ts", {
    cwd: process.cwd(),
  });
  return files.sort();
}

async function runSeed(seedFile: string): Promise<void> {
  return new Promise((resolve, reject) => {
    const child = spawn("tsx", [seedFile], {
      stdio: "inherit",
      cwd: process.cwd(),
    });
    // ... error handling
  });
}

Seed File Structure

Each feature can include its own seed file:

// feature/users/users.seed.ts
import { db } from "@/lib/drizzle";
import { usersSchema } from "./users.schema";

const seedUsers = [
  {
    email: "john@example.com",
    name: "John Doe",
  },
  {
    email: "jane@example.com",
    name: "Jane Smith",
  },
];

export async function seedUsers() {
  console.log("🌱 Seeding users...");

  for (const userData of seedUsers) {
    await db.insert(usersSchema).values(userData).onConflictDoNothing();
  }

  console.log("✅ Users seeded successfully");
}

Migration Strategy

Drizzle provides automatic migration generation:

# Generate migration from schema changes
npx drizzle-kit generate

# Run migrations
npx drizzle-kit migrate

# Drop and recreate (development only)
npx drizzle-kit drop
npx drizzle-kit generate
npx drizzle-kit migrate

🚀 Performance & Optimization

Indexing Strategy

Primary Indexes

Foreign Key Indexes

Specialized Indexes

-- Full-text search for gift names
CREATE INDEX idx_gifts_name_fts ON gifts USING gin(to_tsvector('english', name));

-- Composite index for workflow queries
CREATE INDEX idx_processes_status_recipient
  ON gift_processes(status, recipient_id, created_at);

-- JSONB index for flexible metadata
CREATE INDEX idx_users_preferences ON users USING gin(preferences);

Query Optimization

Connection Pooling

const pool = new Pool({
  connectionString: dbConfig.postgres.url,
  max: 20, // Maximum connections
  min: 5, // Minimum connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Query Patterns

Monitoring & Analytics

Query Performance

-- Enable query statistics
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Monitor slow queries
SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Database Metrics

🔐 Security & Backup

Security Measures

Data Protection

Connection Security

// Production database configuration
const pool = new Pool({
  connectionString: dbConfig.postgres.url,
  ssl: {
    rejectUnauthorized: true,
    ca: process.env.DB_CA_CERT,
  },
  max: 20,
  idleTimeoutMillis: 30000,
});

Backup Strategy

Automated Backups

Backup Commands

# Full backup
pg_dump -h localhost -U postgres -d ivyi_prod > backup_$(date +%Y%m%d).sql

# Compressed backup
pg_dump -h localhost -U postgres -d ivyi_prod | gzip > backup_$(date +%Y%m%d).sql.gz

# Restore
psql -h localhost -U postgres -d ivyi_prod < backup_20240101.sql

Data Retention

Cleanup Policies

Retention Implementation

// Scheduled cleanup job
export const cleanupOldData = async () => {
  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

  await db
    .delete(pgbossJobSchema)
    .where(lt(pgbossJobSchema.createdOn, thirtyDaysAgo));
};

🔗 Related Documentation