Database Schema & Drizzle ORM

Relevant source files

The following files were used as context for generating this wiki page:

• lib/db/schema.ts
• lib/db/actions.ts
• drizzle.config.ts
• drizzle/0000_m1_core.sql
• README.md
• package.json
• drizzle/meta/0015_snapshot.json

Vantage uses Drizzle ORM with a Neon Postgres backend to manage a multi-tenant social listening and lead capture platform. The schema defines the complete lifecycle of lead generation, starting from raw content collection to AI-assisted drafting and outcome tracking.

The architecture prioritizes workspace-level isolation, append-only audit logs for system changes, and a robust cost ledger for tracking outbound provider usage.

Sources: README.md:4-15, lib/db/schema.ts:18-30

Core Data Model

The core data model organizes information around the workspace entity. Every document, lead, and source belongs to a specific workspace to ensure multi-tenant security.

Primary Entities

The following diagram illustrates the relationships between the core entities in the M1 milestone.

The diagram shows how raw documents from sources are processed into leads, clustered into opportunities, and eventually used for drafting.

Sources: lib/db/schema.ts:88-250, drizzle/0000_m1_core.sql:7-50

Table Summary

Table	Description	Primary Key	Constraints
workspace	Defines the tenant, including their subscription plan, monthly budget, and BYOK keys.	id (UUID)	None
source	Configures external feeds (HN, RSS, Reddit, etc.) with stateful cursors and etags.	id (UUID)	Unique index on workspace_id and name.
document	Stores normalized content collected from sources.	id (UUID)	Unique index on workspace_id and content_hash.
lead	Represents a single document evaluated for intent and confidence scores.	id (UUID)	FK to document and workspace.
opportunity	Clusters multiple documents into a high-level actionable item.	id (UUID)	Unique index on workspace_id and cluster_key.

Sources: lib/db/schema.ts:69-215, drizzle/meta/0015_snapshot.json:426-880

Pipeline & Lifecycle States

Vantage tracks the health of external sources and the status of leads through specific PostgreSQL enums.

Source & Lead Enums

The system uses specific enums to manage the state machine of data ingestion and processing.

• source_type: Supported platforms including hn, rss, substack, reddit, web_search, producthunt, youtube, x, github, stackoverflow, and linkedin.
• source_health: Tracks connectivity and budget states: healthy, degraded, failing, paused, access_pending, budget_limited, blocked, and failed.
• lead_status: Tracks the progression of a lead: new, queued, reviewing, approved, rejected, posted, expired.
• opportunity_status: Categorizes opportunities: ignore, monitor, opportunity, review.

Sources: lib/db/schema.ts:32-67, drizzle/0000_m1_core.sql:1-6

Infrastructure & Cost Management

Vantage includes a comprehensive system for tracking operational costs and managing plan limits across workspaces.

Cost Ledger & Budgets

The system records every outbound call to external providers (e.g., Scavio, TinyFish, AI Gateways) to enforce budgets.

• cost_event: Records individual billable events, including units used, unit cost, and the workspace responsible.
• cost_daily: Provides a daily rollup of costs per source, provider, and action for reporting.
• budget_day: Enforces a global daily dollar cap for unauthenticated public visitor scans.
• provider_budget_day: Enforces separate daily caps for authenticated paid calls.

Sources: lib/db/schema.ts:413-485, drizzle/meta/0015_snapshot.json:10-230

Global Source Control

The source_switch system allows operators to pause or block specific platforms globally.

The flow ensures that every global state change is audited in source_switch_log before updating the active switch.

Sources: lib/db/schema.ts:375-400, test/source-switch-admin.test.ts:168-185

ORM Configuration

Drizzle is configured to manage migrations and provide type-safe queries. The project uses drizzle-kit for schema generation and management.

Configuration Snippet

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./lib/db/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Sources: drizzle.config.ts:1-12, package.json:15-18

Data Persistence Actions

Server actions in lib/db/actions.ts handle the creation and management of core entities while ensuring authentication constraints.

• createWorkspaceForCurrentUser: Creates a new workspace and associates it with the authenticated Neon Auth user ID.
• deleteWorkspace: Removes a workspace and triggers cascading deletes for all related sources, documents, and leads.

Sources: lib/db/actions.ts:1-35, lib/db/schema.ts:93-138

Summary

The Vantage database schema provides a multi-tenant foundation that separates raw document collection from high-level opportunity analysis. By integrating a cost ledger and global source switches directly into the schema, the platform maintains operational control over external API usage while Drizzle ORM ensures type safety across the collector pipeline and user interface.

Sources: lib/db/schema.ts:18-30, README.md:20-30