Skip to content

Database Architecture

BidScript's database architecture has evolved significantly, culminating in a unified PostgreSQL solution with Drizzle ORM that serves both frontend and backend applications. This section provides comprehensive documentation about the database layer, migration strategy, and usage patterns across the application stack.

Database Architecture Overview

graph TB
    subgraph "Frontend Layer"
        NextJS[Next.js Application]
        ClientDrizzle[Drizzle Client]
        ClientCache[Client-side Cache]
        end

        subgraph "Backend Layer"
        NestJS[NestJS API Server]
        ServerDrizzle[Drizzle ORM]
        Redis[Redis Cache Layer]
        end

        subgraph "Database Layer"
        PG[(PostgreSQL)]
        PGVector[pgvector Extension]
        Schemas[Database Schemas]
        end

        subgraph "Data Types"
        UserData[User & Auth Data]
        Projects[Project Data]
        Documents[Document Storage]
        Vectors[Vector Embeddings]
        Chat[Chat History]
        end

        NextJS --> ClientDrizzle
        ClientDrizzle --> PG
        ClientCache -.-> NextJS

        NestJS --> ServerDrizzle
        ServerDrizzle --> Redis
        ServerDrizzle --> PG
        Redis -.-> NestJS

        PG --> PGVector
        PG --> Schemas

        Schemas --> UserData
        Schemas --> Projects
        Schemas --> Documents
        PGVector --> Vectors
        Schemas --> Chat

        style NextJS fill:#e3f2fd
        style ClientDrizzle fill:#bbdefb
        style ClientCache fill:#90caf9
        style NestJS fill:#64b5f6
        style ServerDrizzle fill:#42a5f5
        style Redis fill:#2196f3,color:#ffffff
        style PG fill:#1976d2,color:#ffffff
        style PGVector fill:#1565c0,color:#ffffff
        style Schemas fill:#0d47a1,color:#ffffff
        style UserData fill:#e8eaf6
        style Projects fill:#c5cae9
        style Documents fill:#9fa8da
        style Vectors fill:#7986cb
        style Chat fill:#5c6bc0

Current Architecture

Unified PostgreSQL with Drizzle ORM

As of June 2025, BidScript uses a single PostgreSQL database with the pgvector extension, managed through Drizzle ORM. This architecture provides:

  • Single Source of Truth: All application data in one database
  • Vector Capabilities: Native vector storage and similarity search with pgvector
  • Type Safety: Full TypeScript integration across frontend and backend
  • Performance: Optimised for both relational and vector operations
  • Consistency: ACID compliance for all data operations

Data Flow Architecture

sequenceDiagram
    participant User
    participant Frontend as Next.js Frontend
    participant Backend as NestJS Backend
    participant Redis as Redis Cache
    participant DB as PostgreSQL

    User->>Frontend: Request data
    Frontend->>Backend: API call
    Backend->>Redis: Check cache

    alt Cache Hit
        Redis-->>Backend: Return cached data
        Backend-->>Frontend: Return data
    else Cache Miss
        Backend->>DB: Query database
        DB-->>Backend: Return data
        Backend->>Redis: Store in cache
        Backend-->>Frontend: Return data
    end

    Frontend-->>User: Display data

    Note over Backend,DB: Vector operations bypass cache
    Backend->>DB: Vector similarity search
    DB-->>Backend: Return similar documents

Migration Journey

graph LR
    subgraph "Phase 1: Legacy"
        MSSQL[(MSSQL)]
        Prisma1[Prisma ORM]
        end

        subgraph "Phase 2: Multi-DB"
        MSSQL2[(MSSQL)]
        MongoDB[(MongoDB VCore)]
        Prisma2[Prisma ORM]
        VectorDB[Vector Index]
        end

        subgraph "Phase 3: Current"
        PostgreSQL[(PostgreSQL)]
        PGVector2[pgvector Extension]
        Drizzle[Drizzle ORM]
        end

        MSSQL --> MSSQL2
        Prisma1 --> Prisma2
        MSSQL2 --> PostgreSQL
        MongoDB --> PostgreSQL
        Prisma2 --> Drizzle
        VectorDB --> PGVector2

The database architecture evolved through several phases:

  1. Initial Phase: MSSQL with Prisma for user authentication
  2. Multi-Database Phase: Added MongoDB VCore for vector indexing
  3. Current Unified Solution: PostgreSQL with pgvector replacing both systems

Key Migration Benefits

  • Simplified Operations: Single database to manage and monitor
  • Reduced Latency: No cross-database queries or synchronization
  • Cost Efficiency: Single database instance instead of multiple services
  • Enhanced Security: Unified access control and encryption
  • Better Scalability: Clear horizontal and vertical scaling path

Technologies Used

Core Components

  • PostgreSQL 16+: Primary database engine
  • pgvector Extension: Vector similarity search capabilities
  • Drizzle ORM: Type-safe database toolkit
  • Drizzle Kit: Migration and introspection tool

Language Support

  • TypeScript: Full type safety across schema definitions
  • SQL: Direct SQL support when needed
  • Migration Scripts: Programmatic schema changes

Database Sections

Migration Guide

Complete migration documentation from Prisma/MongoDB to Drizzle/PostgreSQL, including step-by-step instructions and troubleshooting.

Frontend Usage

How Drizzle ORM is used in the Next.js frontend application, including configuration, query patterns, and NextAuth.js integration.

Backend Usage

How Drizzle ORM is integrated into the NestJS backend services, including dependency injection, service patterns, and performance optimisation.

Frontend Database ORM Documentation

Complete database architecture documentation covering Redis caching, MongoDB persistence, PostgreSQL migration, and cross-service communication patterns.

API Integration Patterns

Communication patterns between frontend and backend services, including authentication, request handling, and error management.

Key Features

Vector Search Integration

PostgreSQL's pgvector extension enables native vector operations:

-- Vector similarity search example
SELECT title, content_vector <=> $1 as similarity
FROM documents
WHERE owner_id = $2
ORDER BY similarity
LIMIT 10;

Type-Safe Operations

Drizzle provides full TypeScript integration:

// Type-safe query example
const users = await db
  .select()
  .from(userTable)
  .where(eq(userTable.companyId, companyId))
  .leftJoin(companyTable, eq(userTable.companyId, companyTable.id));

Migration Management

Programmatic migrations with version control:

// Migration example
import { migrate } from "drizzle-orm/node-postgres/migrator";
await migrate(db, { migrationsFolder: "./drizzle" });

Environment Configuration

Both frontend and backend use consistent environment variables:

POSTGRES_URL=postgresql://user:password@host:port/database
NODE_ENV=development|production

Next Steps