Database Schema & PostgreSQL ORM

Relevant source files

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

• apps/api/src/db/schema/index.ts
• DATABASE_LAYER_COMPLETE.md
• AGENTS.md
• CODEBASE_IMPROVEMENTS.md
• SWEEPBOT_DASHBOARD.html
• CLAUDE.md

The Database Schema and PostgreSQL ORM layer in SweepBot provides a robust, type-safe foundation for data persistence and management. The system utilizes PostgreSQL 16 (hosted via Supabase) as the primary relational engine, managed through Drizzle ORM for type-safe TypeScript interactions and schema definitions. This layer is responsible for tracking user portfolios, real-time jackpot snapshots, platform trust metrics, and complex NLP-driven automation flows.

The architecture emphasizes scalability and performance through the use of monthly and quarterly table partitioning for high-volume data like transactions and jackpot snapshots. It also incorporates Redis (via Upstash) for distributed rate limiting, session caching, and job queuing through BullMQ.
Sources: AGENTS.md:120-130, CLAUDE.md:104-108, DATABASE_LAYER_COMPLETE.md:113-120

Architecture and Core Components

The database layer is structured into modular Drizzle schemas categorized by functional domain. These schemas are centrally exported to provide a unified interface for the Fastify API.

Core Schema Modules

Module	Scope and Purpose
Identity & Billing	Handles user profiles, subscriptions, and Stripe-related metadata.
Gameplay & Tracking	Manages platforms, sessions, and high-frequency transactions.
Data Collection	Tracks time-series data: jackpot_snapshots, tos_snapshots, and health checks.
Automation Engine	Stores NLP-interpreted flows and their respective flow_executions.
Gamification	Manages achievements, personal_records, big_wins, and user_streaks.
Communication	Handles the in-app notifications inbox and push_subscriptions.

Sources: DATABASE_LAYER_COMPLETE.md:5-30, SWEEPBOT_DASHBOARD.html:450-480

Entity Relationship Overview

The following diagram illustrates the primary relationships between core entities, highlighting how user profiles connect to sessions, platforms, and automation.

Data Management & Performance

Partitioning Strategy

To handle the scale of a high-frequency tracking system, SweepBot implements database-level partitioning:

• Transactions Table: Partitioned by month.
• Jackpot Snapshots Table: Partitioned by quarter to accommodate high-volume time-series data (estimated 10GB/year).
  Sources: AGENTS.md:120-125, CLAUDE.md:106, DATABASE_LAYER_COMPLETE.md:118

Query Optimization & Caching

The system utilizes Redis and specialized query helpers to ensure sub-50ms response times for critical endpoints.

• Cached Queries: Utilizes Redis with TTL (e.g., platforms list cached for 5 minutes, analytics for 30 minutes).
• Batch Operations: Hot-path endpoints like POST /sessions/transactions/batch allow inserting up to 500 transactions in a single call with an O(1) session update.
• Zod Integration: All database inputs are validated against shared Zod schemas in packages/types before being processed by Drizzle.
  Sources: CODEBASE_IMPROVEMENTS.md:148-165, SWEEPBOT_DASHBOARD.html:400-410, CLAUDE.md:88-92

Schema Implementation Details

Profile and Subscription Logic

SweepBot uses triggers and Supabase metadata for identity management.

• handle_new_user(): Trigger that auto-creates profiles rows upon Supabase Auth signup.
• handle_new_profile(): Trigger that auto-creates user_settings and subscriptions.
• Tier Enforcement: User subscription tiers (Free, Starter, Pro, etc.) are synchronized via Stripe webhooks to the subscriptions table.
  Sources: AGENTS.md:125-130, SWEEPBOT_DASHBOARD.html:410-420

Drizzle Configuration

The ORM is configured via drizzle.config.ts to manage migrations and introspection.

// apps/api/drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema/index.ts',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Sources: DATABASE_LAYER_COMPLETE.md:32-40, CLAUDE.md:52-55

Database Security

Security is enforced primarily through Supabase Row Level Security (RLS), which is enabled on all user-facing tables to prevent unauthorized data access between players. Additionally, the system employs Zod schema validation for every API request and Stripe webhook signature verification for all billing-related database updates.
Sources: AGENTS.md:120-122, CLAUDE.md:104-105, SWEEPBOT_DASHBOARD.html:610-620

Conclusion

The SweepBot database layer is a sophisticated multi-tier architecture combining the safety of Drizzle ORM, the scale of PostgreSQL partitioning, and the speed of Redis caching. By modularizing schemas and enforcing strict type-safety across the monorepo, the project maintains a production-ready environment capable of handling complex casino analytics and automated player flows.