How to architect robust database schemas that power modern AI applications with credit systems, multi-provider authentication, and user-generated content at scale
The Challenge
Building AI applications requires rethinking traditional database design patterns. Unlike conventional web applications where operations complete instantly, AI systems process tasks asynchronously over extended periods while managing complex credit systems, tracking computational costs, and storing diverse content types. These unique requirements demand sophisticated database architectures that balance performance, consistency, and cost efficiency.
Modern AI applications face several critical database design challenges:
Complex State Management: AI tasks transition through multiple states over their lifecycle. Databases must efficiently track these transitions while maintaining consistency across distributed systems.
Credit System Complexity: Usage-based billing requires precise tracking of credit allocations, consumption patterns, and expiration dates across potentially millions of transactions.
Multi-Provider Authentication: Supporting various authentication methods while maintaining a unified user model creates schema complexity that traditional designs struggle to accommodate.
Performance at Scale: AI applications generate massive amounts of metadata, requiring careful indexing strategies and query optimization to maintain sub-100ms response times.
Core Schema Architecture
The foundation of a scalable AI application database starts with thoughtful table design that anticipates future growth patterns while maintaining operational simplicity.
User Management with Flexible Authentication
Modern applications require supporting multiple authentication providers while maintaining a single source of truth for user identity:
export const users = sqliteTable('users', {
id: integer('id').primaryKey({ autoIncrement: true }),
uuid: text('uuid').notNull().unique(),
nickname: text('nickname'),
email: text('email').notNull().unique(),
avatar: text('avatar'),
password: text('password'), // Nullable for third-party auth
accountProvider: text('account_provider'), // 'google', 'github', 'email'
providerAccountId: text('provider_account_id'),
ipAddress: text('ip_address'), // IPv6 support
accountStatus: text('account_status', {
enum: ['active', 'suspended', 'deleted']
}).default('active'),
createdAt: integer('created_at').notNull().default(sql`(unixepoch())`),
updatedAt: integer('updated_at').notNull().default(sql`(unixepoch())`)
}, (table) => ({
emailIdx: uniqueIndex('users_email_idx').on(table.email),
uuidIdx: uniqueIndex('users_uuid_idx').on(table.uuid),
providerIdx: uniqueIndex('users_provider_idx').on(
table.accountProvider,
table.providerAccountId
),
accountStatusIdx: index('users_account_status_idx').on(table.accountStatus)
}));
This schema design provides several key benefits:
UUID Strategy: Using UUIDs alongside auto-incrementing IDs enables secure external references while maintaining efficient internal joins. UUIDs prevent enumeration attacks and facilitate distributed system integration.
Provider Flexibility: The nullable password field and provider columns support both traditional email authentication and OAuth providers without schema duplication.
Compound Indexing: The provider index ensures unique combinations of provider and account ID, preventing duplicate accounts while maintaining fast lookup performance.
Credit System Architecture
Building a robust credit system requires separating income and expense tracking for accurate reconciliation:
export const userCreditIncome = sqliteTable('user_credit_income', {
id: integer('id').primaryKey({ autoIncrement: true }),
uuid: text('uuid').notNull().unique(),
userUuid: text('user_uuid').notNull(),
creditsAmount: integer('credits_amount').notNull(),
incomeType: text('income_type', {
enum: ['purchase_one_time', 'purchase_monthly', 'purchase_yearly',
'promotion', 'refund', 'admin_grant']
}).notNull(),
sourceRelationUuid: text('source_relation_uuid'), // Links to orders
validStartTime: integer('valid_start_time').notNull(),
validEndTime: integer('valid_end_time'), // NULL for no expiration
remarks: text('remarks'),
createdAt: integer('created_at').notNull().default(sql`(unixepoch())`),
updatedAt: integer('updated_at').notNull().default(sql`(unixepoch())`)
}, (table) => ({
userUuidIdx: index('user_credit_income_user_uuid_idx').on(table.userUuid),
userExpireIdx: index('user_credit_income_user_expire_idx').on(
table.userUuid,
table.validEndTime
),
typeCreatedIdx: index('user_credit_income_type_created_idx').on(
table.incomeType,
table.createdAt
)
}));
export const userCreditExpense = sqliteTable('user_credit_expense', {
id: integer('id').primaryKey({ autoIncrement: true }),
uuid: text('uuid').notNull().unique(),
userUuid: text('user_uuid').notNull(),
creditsAmount: integer('credits_amount').notNull(),
expenseType: text('expense_type', {
enum: ['generate_work', 'premium_feature', 'admin_deduct']
}).notNull(),
sourceRelationUuid: text('source_relation_uuid'),
businessScenario: text('business_scenario'),
remarks: text('remarks'),
createdAt: integer('created_at').notNull().default(sql`(unixepoch())`),
updatedAt: integer('updated_at').notNull().default(sql`(unixepoch())`)
}, (table) => ({
userUuidIdx: index('user_credit_expense_user_uuid_idx').on(table.userUuid),
userCreatedIdx: index('user_credit_expense_user_created_idx').on(
table.userUuid,
table.createdAt
)
}));
The credit system architecture implements several sophisticated patterns:
Temporal Credits: The validity period system enables promotional credits that expire, subscription-based allocations, and permanent purchases within a single schema.
Audit Trail: Separate income and expense tables create an immutable audit trail. This design prevents accidental data loss and simplifies financial reconciliation.
Performance Optimization: The compound index on user and expiration time enables efficient queries for available credits without scanning expired allocations.
User-Generated Content Management
AI applications generate diverse content types requiring flexible storage and efficient retrieval:
export const userWorks = sqliteTable('user_works', {
id: integer('id').primaryKey({ autoIncrement: true }),
uuid: text('uuid').notNull().unique(),
userUuid: text('user_uuid').notNull(),
workType: text('work_type', {
enum: ['text_to_text', 'text_to_image', 'image_to_image']
}).notNull(),
inputContent: text('input_content').notNull(),
inputImageUrl: text('input_image_url'),
workResult: text('work_result').notNull(),
generationDuration: integer('generation_duration'),
creditsConsumed: integer('credits_consumed').notNull(),
generationStatus: text('generation_status', {
enum: ['generating', 'completed', 'failed']
}).notNull(),
managementStatus: text('management_status', {
enum: ['active', 'deleted']
}).default('active'),
isPublic: integer('is_public', { mode: 'boolean' }).default(false),
likesCount: integer('likes_count').default(0),
downloadsCount: integer('downloads_count').default(0),
createdAt: integer('created_at').notNull().default(sql`(unixepoch())`),
updatedAt: integer('updated_at').notNull().default(sql`(unixepoch())`)
}, (table) => ({
publicCreatedIdx: index('user_works_public_created_idx').on(
table.isPublic,
table.createdAt
),
userCreatedIdx: index('user_works_user_created_idx').on(
table.userUuid,
table.createdAt
),
typeCreatedIdx: index('user_works_type_created_idx').on(
table.workType,
table.createdAt
)
}));
This content management schema addresses several requirements:
Soft Deletion: The management status field enables soft deletion while preserving data for analytics and potential recovery.
Public Gallery Support: Boolean indexing on public status combined with creation time enables efficient gallery queries without full table scans.
Performance Metrics: Tracking generation duration provides valuable insights for capacity planning and performance optimization.
Advanced Indexing Strategies
Strategic indexing transforms database performance from adequate to exceptional. The key lies in understanding query patterns and optimizing for real-world usage.
Compound Index Design
Compound indexes dramatically improve query performance for common access patterns:
// Efficient user order history queries
index('orders_user_created_idx').on(
table.userUuid,
table.orderCreatedAt
)
// Fast public gallery with pagination
index('user_works_public_created_idx').on(
table.isPublic,
table.createdAt
)
// Credit expiration management
index('user_credit_income_user_expire_idx').on(
table.userUuid,
table.validEndTime
)
These compound indexes serve specific optimization purposes:
Query Coverage: Each index covers complete query patterns, eliminating additional row lookups and reducing query execution time by 80% or more.
Sort Optimization: Including timestamp fields in indexes eliminates sorting operations, crucial for paginated APIs returning chronological data.
Selective Indexing: Indexes on discriminating columns like isPublic reduce the search space dramatically for filtered queries.
Index Maintenance Patterns
Efficient index design requires balancing query performance against write overhead:
// High-write tables use minimal indexing
export const auditLogs = sqliteTable('audit_logs', {
id: integer('id').primaryKey({ autoIncrement: true }),
eventType: text('event_type').notNull(),
userUuid: text('user_uuid'),
metadata: text('metadata'),
createdAt: integer('created_at').notNull().default(sql`(unixepoch())`)
}, (table) => ({
// Only essential indexes for write-heavy tables
userUuidIdx: index('audit_logs_user_uuid_idx').on(table.userUuid)
}));
// Read-heavy tables use comprehensive indexing
export const cachedResults = sqliteTable('cached_results', {
id: integer('id').primaryKey({ autoIncrement: true }),
cacheKey: text('cache_key').notNull().unique(),
resultData: text('result_data').notNull(),
expiresAt: integer('expires_at').notNull(),
accessCount: integer('access_count').default(0)
}, (table) => ({
// Multiple indexes for various access patterns
cacheKeyIdx: uniqueIndex('cached_results_cache_key_idx').on(table.cacheKey),
expiryIdx: index('cached_results_expiry_idx').on(table.expiresAt),
popularityIdx: index('cached_results_popularity_idx').on(table.accessCount)
}));
Order Processing and Subscription Management
Complex billing scenarios require sophisticated order tracking:
export const orders = sqliteTable('orders', {
id: integer('id').primaryKey({ autoIncrement: true }),
uuid: text('uuid').notNull().unique(),
orderNumber: text('order_number').notNull().unique(),
userUuid: text('user_uuid').notNull(),
orderAmount: real('order_amount').notNull(),
orderCurrency: text('order_currency').notNull().default('USD'),
productUuid: text('product_uuid').notNull(),
productName: text('product_name').notNull(), // Snapshot for history
productPriceSnapshot: real('product_price_snapshot').notNull(),
creditsAmountSnapshot: integer('credits_amount_snapshot').notNull(),
paymentTime: integer('payment_time'),
orderStatus: text('order_status', {
enum: ['pending', 'paid', 'failed', 'refunded', 'cancelled']
}).notNull(),
paymentMethod: text('payment_method'),
paymentPlatformOrderId: text('payment_platform_order_id'),
customerId: text('customer_id'), // For subscriptions
subscriptionId: text('subscription_id'),
subscriptionCycle: text('subscription_cycle', {
enum: ['monthly', 'yearly']
}),
subscriptionStartTime: integer('subscription_start_time'),
subscriptionEndTime: integer('subscription_end_time'),
refundAmount: real('refund_amount').default(0.0),
refundTime: integer('refund_time'),
remarks: text('remarks'),
orderCreatedAt: integer('order_created_at').notNull().default(sql`(unixepoch())`),
orderUpdatedAt: integer('order_updated_at').notNull().default(sql`(unixepoch())`)
}, (table) => ({
statusCreatedIdx: index('orders_status_created_idx').on(
table.orderStatus,
table.orderCreatedAt
)
}));
The order schema implements several critical patterns:
Data Snapshotting: Product prices and credit amounts are snapshotted at order time, preserving historical accuracy even as prices change.
Subscription Support: Optional subscription fields enable both one-time and recurring billing models within a single table structure.
Payment Platform Integration: External platform IDs facilitate reconciliation and support multiple payment providers simultaneously.
Database Client Optimization
Efficient database client design maximizes connection reuse and minimizes overhead:
import { drizzle, DrizzleD1Database } from 'drizzle-orm/d1';
import * as schema from './schema';
const cachedDrizzleClientMap = new Map<D1Database, DrizzleD1Database<typeof schema>>();
export function createDrizzleClient(db: D1Database) {
const cached = cachedDrizzleClientMap.get(db);
if (cached) return cached;
const client = drizzle(db, { schema });
cachedDrizzleClientMap.set(db, client);
return client;
}
This client pattern provides several optimizations:
Connection Pooling: The Map-based cache prevents creating multiple Drizzle instances for the same database, reducing memory overhead and connection churn.
Type Safety: Including the schema in the Drizzle configuration enables full TypeScript support, catching query errors at compile time.
Edge Runtime Compatibility: The pattern works seamlessly with Cloudflare Workers' request-scoped database instances.
Query Optimization Patterns
Efficient queries require understanding both the ORM and underlying database characteristics:
// Efficient credit balance calculation
async function getUserAvailableCredits(userUuid: string, db: D1Database) {
const drizzle = createDrizzleClient(db);
const now = Math.floor(Date.now() / 1000);
// Fetch only non-expired income records
const incomeRecords = await drizzle
.select({
creditsAmount: userCreditIncome.creditsAmount,
})
.from(userCreditIncome)
.where(
and(
eq(userCreditIncome.userUuid, userUuid),
or(
isNull(userCreditIncome.validEndTime),
gte(userCreditIncome.validEndTime, now)
)
)
);
// Aggregate all expenses
const expenseRecords = await drizzle
.select({
totalExpense: sql<number>`SUM(${userCreditExpense.creditsAmount})`,
})
.from(userCreditExpense)
.where(eq(userCreditExpense.userUuid, userUuid))
.limit(1);
const totalIncome = incomeRecords.reduce((sum, record) =>
sum + record.creditsAmount, 0
);
const totalExpense = expenseRecords[0]?.totalExpense || 0;
return totalIncome - totalExpense;
}
// Paginated gallery with efficient sorting
async function getPublicGallery(page: number, pageSize: number, db: D1Database) {
const drizzle = createDrizzleClient(db);
const offset = (page - 1) * pageSize;
return await drizzle
.select({
uuid: userWorks.uuid,
workResult: userWorks.workResult,
workType: userWorks.workType,
likesCount: userWorks.likesCount,
createdAt: userWorks.createdAt,
user: {
nickname: users.nickname,
avatar: users.avatar,
},
})
.from(userWorks)
.innerJoin(users, eq(userWorks.userUuid, users.uuid))
.where(
and(
eq(userWorks.isPublic, true),
eq(userWorks.generationStatus, 'completed'),
eq(userWorks.managementStatus, 'active')
)
)
.orderBy(desc(userWorks.createdAt))
.limit(pageSize)
.offset(offset);
}
These query patterns demonstrate several optimizations:
Selective Fetching: Retrieving only necessary columns reduces data transfer and parsing overhead.
Aggregation Push-down: Using SQL aggregation functions processes data at the database level, dramatically reducing network transfer.
Efficient Pagination: Combining LIMIT and OFFSET with proper indexing enables fast pagination even for large datasets.
Migration Strategy and Schema Evolution
Managing schema changes in production requires careful planning:
// Migration naming convention: YYYYMMDD_HHMMSS_description.sql
// Example: 20240315_143022_add_user_preferences.sql
-- Safe column addition with default
ALTER TABLE users
ADD COLUMN preferences TEXT DEFAULT '{}';
-- Index creation without blocking
CREATE INDEX CONCURRENTLY IF NOT EXISTS
idx_users_preferences_theme
ON users((preferences->>'theme'));
-- Data backfill with batching
UPDATE users
SET preferences = json_object('theme', 'light', 'notifications', true)
WHERE preferences = '{}'
AND id IN (
SELECT id FROM users
WHERE preferences = '{}'
LIMIT 1000
);
Migration best practices include:
Non-Breaking Changes: Adding nullable columns or new tables ensures zero-downtime deployments.
Batch Processing: Large data migrations use batched updates to prevent lock escalation and timeout issues.
Rollback Planning: Every migration includes a corresponding rollback script for emergency recovery.
Performance Metrics and Monitoring
Understanding database performance requires comprehensive monitoring:
class DatabaseMetrics {
private queryTimings = new Map<string, number[]>();
async trackQuery<T>(
queryName: string,
queryFn: () => Promise<T>
): Promise<T> {
const startTime = performance.now();
try {
const result = await queryFn();
const duration = performance.now() - startTime;
// Track timing for analysis
if (!this.queryTimings.has(queryName)) {
this.queryTimings.set(queryName, []);
}
this.queryTimings.get(queryName)!.push(duration);
// Alert on slow queries
if (duration > 100) {
console.warn(`Slow query detected: ${queryName} took ${duration}ms`);
}
return result;
} catch (error) {
const duration = performance.now() - startTime;
console.error(`Query failed: ${queryName} after ${duration}ms`, error);
throw error;
}
}
getStatistics(queryName: string) {
const timings = this.queryTimings.get(queryName) || [];
if (timings.length === 0) return null;
const sorted = [...timings].sort((a, b) => a - b);
return {
count: timings.length,
min: sorted[0],
max: sorted[sorted.length - 1],
p50: sorted[Math.floor(sorted.length * 0.5)],
p95: sorted[Math.floor(sorted.length * 0.95)],
p99: sorted[Math.floor(sorted.length * 0.99)],
avg: timings.reduce((a, b) => a + b, 0) / timings.length,
};
}
}