Database Schema & Drizzle ORM
Relevant source files
The following files were used as context for generating this wiki page:
The Kuest prediction market utilizes a PostgreSQL database managed through the Drizzle Object-Relational Mapper (ORM). This architecture ensures type-safe database interactions and facilitates complex queries for market data, user management, and administrative metrics. The schema is designed to support high-frequency updates from external subgraphs and decentralized market activity.
The system handles core entities such as events, market conditions, orders, and user authentication. It leverages Drizzle's relational features to maintain referential integrity across decentralized identities and traditional database records.
Sources: src/lib/drizzle.ts:1-12, src/lib/db/schema/index.ts:1-20, package.json:55
Core Data Model & ER Diagram
The database architecture is structured to link prediction market events with their underlying blockchain conditions and outcomes. It also integrates with "Better Auth" for user session management and decentralized wallet identification.
This diagram illustrates the primary relationships between authentication tables and market operation tables. Note that sessions, accounts, and wallets maintain pluralized relation keys for experimental join support.
Sources: tests/unit/authSchemaRelations.test.ts:1-25, src/app/api/sync/resolution/route.ts:25-30, src/lib/db/queries/admin-dashboard.ts:50-80
Drizzle ORM Configuration
The project uses drizzle-orm in conjunction with the postgres driver. The configuration supports both connection pooling for application routes and non-pooling connections specifically for database migrations.
Database Connection Management
The application initializes a singleton database instance exported as db. This instance uses a PostgreSQL client configured with environment variables.
| Environment Variable | Description | Requirement |
|---|---|---|
POSTGRES_URL | Primary connection string for the database | Required for runtime |
POSTGRES_URL_NON_POOLING | Connection string bypassing pooling | Required for migrations |
Sources: src/lib/drizzle.ts:1-12, scripts/migrate.ts:468-472
Schema Exports
The schema is organized into modular files and re-exported through a central index. This allows the Drizzle adapter to recognize all tables and their relations for type-safe querying.
import * as schema from '@/lib/db/schema'
// Used in extractTablesRelationalConfig for type-safe relations
Sources: tests/unit/authSchemaRelations.test.ts:1-10
Migration Workflow
Database updates are handled via a custom migration script (scripts/migrate.ts) that manages SQL file application and schema tracking. It implements an advisory lock to prevent concurrent migrations.
Migration Execution Details
- Advisory Locks: Uses namespace
20817and key1to ensure only one instance runs. - Supabase Compatibility: Rewrites migration SQL to replace
service_rolewithCURRENT_USERwhen not running in Supabase mode. - Cron Jobs: In Supabase mode, the script automatically schedules maintenance tasks like
clean-cron-detailsandclean-jobsusingpg_cron.
Sources: scripts/migrate.ts:3-10, scripts/migrate.ts:153-200, scripts/migrate.ts:358-400
Synchronization & Resolution Schema
The system includes specialized tables and fields for tracking the resolution of market conditions and syncing state from external subgraphs.
Resolution Tracking Fields
The conditions table contains detailed metadata regarding the resolution lifecycle:
| Field | Type | Description |
|---|---|---|
resolved | boolean | Final status of the condition |
resolution_status | string | Current state (e.g., 'posed', 'resolved', 'challenged') |
resolution_price | bigint | The price at which the condition resolved (e.g., 1e18 for YES) |
resolution_deadline_at | timestamp | Expiration of the challenge/liveness period |
resolution_paused | boolean | Flag if resolution is halted |
Sources: src/app/api/sync/resolution/route.ts:470-500
Subgraph Sync State
The subgraph_syncs table tracks the progress of background synchronization jobs to ensure data consistency without redundant processing.
// Cursor update for resolution sync
await db.update(subgraph_syncs)
.set({ cursor_updated_at: BigInt(cursor.timestamp), cursor_id: cursor.id })
.where(and(
eq(subgraph_syncs.service_name, 'resolution_sync'),
eq(subgraph_syncs.subgraph_name, 'resolution')
));
Sources: src/app/api/sync/resolution/route.ts:400-415
Analytics & Administrative Queries
The schema supports complex analytical queries for the admin dashboard, including time-series data for user growth and order volume.
Key Performance Indicators (KPIs)
The AdminDashboardRepository utilizes SQL aggregates to calculate:
- Registered Users: Total count and a 7-day filter on
users.created_at. - Order Volume: Sum of
maker_amountortaker_amountfrom theorderstable, divided by a constant representing USDC decimals (1,000,000). - Daily Series: Uses
date_truncandTO_CHARto group data by UTC day over a 30-day window.
Sources: src/lib/db/queries/admin-dashboard.ts:25-90
Event Attention Queries
Specialized conditions are built to identify events requiring manual intervention:
- Missing Sports Source: Events without configured sports data providers.
- Past Due Unresolved: Events whose
end_datehas passed but remain in an active status.
Sources: src/lib/db/queries/admin-dashboard.ts:55-60, tests/unit/eventResolvedLikeCondition.test.ts:15-30
Summary
The Database Schema and Drizzle ORM implementation provide a robust foundation for the prediction market. By combining modular schema definitions, type-safe relations, and a sophisticated migration system, the project maintains data integrity across authentication and decentralized market operations while enabling real-time synchronization and administrative oversight.