Database Schema Expert

Database Schema Expert

You are an expert in the database architecture for this influencer discovery platform. This skill provides comprehensive knowledge about the Postgres schema, Drizzle ORM patterns, normalized user tables, and query optimization.

When To Use This Skill

This skill activates when users:

• Ask about database tables or schema design
• Need to write Drizzle ORM queries
• Work with user data across normalized tables
• Debug data consistency issues
• Plan database migrations
• Optimize query performance
• Understand table relationships
• Need to query campaigns, jobs, or creators

Core Knowledge

Schema Architecture

Database: Supabase Postgres ORM: Drizzle ORM Schema File: /lib/db/schema.ts

Key Design Principles:

1. Normalized User Tables: Replaced monolithic user_profiles with 5 focused tables
2. JSONB for Flexibility: features, searchParams, metadata use JSONB
3. UUID Primary Keys: All tables use UUID for distributed system compatibility
4. Timestamp Tracking: createdAt and updatedAt on all tables
5. Soft Relations: Foreign keys with cascade deletes where appropriate

Table Structure

Core Tables:

1. campaigns - User search campaigns

{
  id: uuid (PK)
  userId: text (FK to Clerk ID)
  name: text
  description: text
  searchType: varchar(20) // 'instagram-reels', 'tiktok-keyword', etc.
  status: varchar(20) // 'draft', 'active', 'completed', 'archived'
  createdAt: timestamp
  updatedAt: timestamp
}

2. scraping_jobs - Background search jobs

{
  id: uuid (PK)
  userId: text
  runId: text
  status: varchar(20) // 'pending', 'processing', 'completed', 'error', 'timeout'
  keywords: jsonb
  platform: varchar(50) // 'Instagram', 'TikTok', 'YouTube'
  region: varchar(10) // 'US', 'UK', etc.
  campaignId: uuid (FK to campaigns)
  targetUsername: text
  searchParams: jsonb
  qstashMessageId: text
  processedRuns: integer
  processedResults: integer
  targetResults: integer
  cursor: integer
  progress: numeric
  createdAt: timestamp
  startedAt: timestamp
  completedAt: timestamp
  timeoutAt: timestamp
  updatedAt: timestamp
  error: text
}

3. scraping_results - Creator results from jobs

{
  id: uuid (PK)
  jobId: uuid (FK to scraping_jobs)
  creators: jsonb // Array of creator objects
  createdAt: timestamp
}

4. subscription_plans - Available plans

{
  id: uuid (PK)
  planKey: varchar // 'glow_up', 'viral_surge', 'fame_flex', 'free'
  planName: text
  campaignsLimit: integer // -1 for unlimited
  creatorsLimit: integer // -1 for unlimited
  features: jsonb
  priceMonthly: numeric
  priceYearly: numeric
  stripePriceIdMonthly: text
  stripePriceIdYearly: text
  isActive: boolean
  displayOrder: integer
  createdAt: timestamp
  updatedAt: timestamp
}

Normalized User Tables (5 tables replace user_profiles):

1. users - Core identity

{
  id: uuid (PK, internal)
  userId: text (unique, Clerk ID)
  email: text
  fullName: text
  businessName: text
  brandDescription: text
  industry: text
  onboardingStep: varchar(50) // 'pending', 'step-1', 'step-2', 'completed'
  isAdmin: boolean
  createdAt: timestamp
  updatedAt: timestamp
}

2. user_subscriptions - Trial and subscription state

{
  id: uuid (PK)
  userId: uuid (FK to users.id)
  currentPlan: varchar(50)
  intendedPlan: varchar(50)
  subscriptionStatus: varchar(20)
  trialStatus: varchar(20) // 'pending', 'active', 'expired', 'converted'
  trialStartDate: timestamp
  trialEndDate: timestamp
  trialConversionDate: timestamp
  subscriptionCancelDate: timestamp
  subscriptionRenewalDate: timestamp
  billingSyncStatus: varchar(20)
  createdAt: timestamp
  updatedAt: timestamp
}

3. user_billing - Stripe payment data

{
  id: uuid (PK)
  userId: uuid (FK to users.id)
  stripeCustomerId: text (unique)
  stripeSubscriptionId: text
  paymentMethodId: text
  cardLast4: varchar(4)
  cardBrand: varchar(20)
  cardExpMonth: integer
  cardExpYear: integer
  billingAddressCity: text
  billingAddressCountry: varchar(2)
  billingAddressPostalCode: varchar(20)
  createdAt: timestamp
  updatedAt: timestamp
}

4. user_usage - Plan limits and usage tracking

{
  id: uuid (PK)
  userId: uuid (FK to users.id)
  planCampaignsLimit: integer
  planCreatorsLimit: integer
  planFeatures: jsonb
  usageCampaignsCurrent: integer
  usageCreatorsCurrentMonth: integer
  enrichmentsCurrentMonth: integer
  usageResetDate: timestamp
  createdAt: timestamp
  updatedAt: timestamp
}

5. user_system_data - System metadata

{
  id: uuid (PK)
  userId: uuid (FK to users.id)
  signupTimestamp: timestamp
  emailScheduleStatus: jsonb
  lastWebhookEvent: varchar(100)
  lastWebhookTimestamp: timestamp
  createdAt: timestamp
  updatedAt: timestamp
}

System Tables:

• events - Event sourcing for audit trail
• background_jobs - Scheduled background tasks
• system_configurations - Hot-reloadable config
• logging_configurations - Runtime logging control

Drizzle ORM Patterns

Database Client: /lib/db/index.ts

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client, { schema });

Common Query Patterns:

1. Select Single Record:

import { db } from '@/lib/db';
import { users } from '@/lib/db/schema';
import { eq } from 'drizzle-orm';

const user = await db.query.users.findFirst({
  where: eq(users.userId, clerkUserId)
});

2. Select with Relations:

const user = await db.query.users.findFirst({
  where: eq(users.userId, clerkUserId),
  with: {
    subscriptions: true,
    billing: true,
    usage: true,
    systemData: true
  }
});

3. Insert Record:

const [campaign] = await db.insert(campaigns)
  .values({
    userId: clerkUserId,
    name: 'My Campaign',
    searchType: 'instagram-reels',
    status: 'draft'
  })
  .returning();

4. Update Record:

await db.update(scrapingJobs)
  .set({
    status: 'completed',
    completedAt: new Date(),
    processedResults: 1000
  })
  .where(eq(scrapingJobs.id, jobId));

5. Complex Query with Aggregation:

import { count, and, gte } from 'drizzle-orm';

const [result] = await db
  .select({ count: count() })
  .from(campaigns)
  .where(and(
    eq(campaigns.userId, userId),
    gte(campaigns.createdAt, startOfMonth)
  ));

6. Join Query:

const jobsWithResults = await db
  .select()
  .from(scrapingJobs)
  .leftJoin(scrapingResults, eq(scrapingJobs.id, scrapingResults.jobId))
  .where(eq(scrapingJobs.userId, userId));

7. Transaction:

await db.transaction(async (tx) => {
  const [campaign] = await tx.insert(campaigns).values({...}).returning();
  await tx.insert(scrapingJobs).values({
    campaignId: campaign.id,
    ...
  });
});

User Data Query Helpers

Helper Functions: /lib/db/queries/user-queries.ts

// Get full user profile (normalized)
export async function getUserProfile(clerkUserId: string) {
  return await db.query.users.findFirst({
    where: eq(users.userId, clerkUserId),
    with: {
      subscriptions: true,
      billing: true,
      usage: true,
      systemData: true
    }
  });
}

// Update user profile (handles normalization)
export async function updateUserProfile(clerkUserId: string, data: any) {
  // Intelligently updates correct normalized table
  // See implementation for details
}

// Get user by Stripe customer ID
export async function getUserByStripeCustomerId(customerId: string) {
  return await db.query.users.findFirst({
    where: eq(userBilling.stripeCustomerId, customerId),
    with: { /* ... */ }
  });
}

Migration Strategy

Migration Tool: Drizzle Kit Migration Files: /drizzle/ directory

Create Migration:

npx drizzle-kit generate:pg

Run Migration:

npx drizzle-kit push:pg

Manual Migration Script:

node scripts/run-single-migration.js

Migration Best Practices:

1. Always generate migration, never edit schema directly in production
2. Test migration on local database first
3. Backup production data before migration
4. Use transactions for multi-step migrations
5. Have rollback plan ready

Common Patterns

Pattern 1: Querying Normalized User Data

// Good: Use helper function
import { getUserProfile } from '@/lib/db/queries/user-queries';

const profile = await getUserProfile(userId);
// Returns denormalized view of user across all 5 tables

// Access any field:
profile.email // from users
profile.currentPlan // from user_subscriptions
profile.stripeCustomerId // from user_billing
profile.planCampaignsLimit // from user_usage
profile.lastWebhookEvent // from user_system_data

When to use: Anytime you need user data (most API endpoints)

Pattern 2: Paginated Results

// Good: Server-side pagination
import { desc, asc } from 'drizzle-orm';

const pageSize = 20;
const offset = (page - 1) * pageSize;

const campaigns = await db.query.campaigns.findMany({
  where: eq(campaigns.userId, userId),
  orderBy: [desc(campaigns.createdAt)],
  limit: pageSize,
  offset: offset
});

When to use: Listing campaigns, jobs, or search results

Pattern 3: JSONB Query

// Good: Query nested JSONB data
import { sql } from 'drizzle-orm';

const jobs = await db.select()
  .from(scrapingJobs)
  .where(sql`${scrapingJobs.keywords}::jsonb @> '["fitness"]'::jsonb`);

// Or check if key exists
const jobs = await db.select()
  .from(scrapingJobs)
  .where(sql`${scrapingJobs.searchParams}::jsonb ? 'targetAudience'`);

When to use: Searching within JSONB columns

Anti-Patterns (Avoid These)

Anti-Pattern 1: N+1 Query Problem

// BAD: N+1 queries
const jobs = await db.query.scrapingJobs.findMany({
  where: eq(scrapingJobs.userId, userId)
});

for (const job of jobs) {
  const results = await db.query.scrapingResults.findMany({
    where: eq(scrapingResults.jobId, job.id)
  });
}

Why it's bad: Makes N additional queries

Do this instead:

// GOOD: Single query with relation
const jobs = await db.query.scrapingJobs.findMany({
  where: eq(scrapingJobs.userId, userId),
  with: { results: true }
});

// Access results: jobs[0].results

Anti-Pattern 2: Selecting All Columns

// BAD: Selecting huge JSONB columns unnecessarily
const jobs = await db.select().from(scrapingJobs);
// Returns all jobs with full searchParams and keywords JSONB

Why it's bad: Waste of network and memory for large JSONB

Do this instead:

// GOOD: Select only needed columns
const jobs = await db.select({
  id: scrapingJobs.id,
  status: scrapingJobs.status,
  createdAt: scrapingJobs.createdAt
}).from(scrapingJobs);

Anti-Pattern 3: Direct user_profiles Query

// BAD: Querying old monolithic table
const user = await db.query.userProfiles.findFirst({
  where: eq(userProfiles.userId, userId)
});

Why it's bad: user_profiles is deprecated, data is in 5 normalized tables

Do this instead:

// GOOD: Use normalized query helper
import { getUserProfile } from '@/lib/db/queries/user-queries';
const user = await getUserProfile(userId);

Troubleshooting Guide

Problem: "relation does not exist" Error

Symptoms:

• error: relation "campaigns" does not exist
• Query works in dev but fails in production

Diagnosis:

1. Check if migrations ran
2. Verify table exists in Supabase dashboard
3. Check connection string points to correct database

Solution:

# Check migration status
node scripts/test-migration-status.js

# Run pending migrations
npx drizzle-kit push:pg

# Verify in Supabase
# Go to Table Editor and check if table exists

Problem: JSONB Query Not Working

Symptoms:

• JSONB query returns no results
• Error: operator does not exist

Diagnosis:

1. Check JSONB syntax (needs ::jsonb cast)
2. Verify JSONB structure matches query
3. Check for null values

Solution:

// Correct JSONB query syntax
import { sql } from 'drizzle-orm';

// Contains check (array)
const jobs = await db.select()
  .from(scrapingJobs)
  .where(sql`${scrapingJobs.keywords}::jsonb @> '["fitness"]'::jsonb`);

// Key exists check (object)
const jobs = await db.select()
  .from(scrapingJobs)
  .where(sql`${scrapingJobs.searchParams}::jsonb ? 'platform'`);

// Nested value check
const jobs = await db.select()
  .from(scrapingJobs)
  .where(sql`${scrapingJobs.searchParams}::jsonb->>'platform' = 'instagram'`);

Problem: Slow Queries

Symptoms:

• Query takes >1 second
• Timeout errors on large datasets

Diagnosis:

1. Check if indexes exist
2. Look for full table scans
3. Verify JSONB queries use GIN indexes

Solution:

-- Add index for common queries
CREATE INDEX idx_campaigns_user_id ON campaigns(user_id);
CREATE INDEX idx_scraping_jobs_status ON scraping_jobs(status);
CREATE INDEX idx_scraping_jobs_user_id_created_at ON scraping_jobs(user_id, created_at DESC);

-- GIN index for JSONB
CREATE INDEX idx_scraping_jobs_keywords ON scraping_jobs USING GIN(keywords);

Use script:

node scripts/add-search-indexes.js

Problem: User Data Not Found After Normalization

Symptoms:

• getUserProfile returns null
• User exists in Clerk but not in database
• Fields like currentPlan are null

Diagnosis:

1. Check if user row exists in users table
2. Verify foreign key relationships
3. Check if Clerk webhook created user

Solution:

# Inspect user state
node scripts/inspect-user-state.js --email user@example.com

# Manually create user if missing
node scripts/test-auto-create-user.js user_xxx

Related Files

• /lib/db/schema.ts - Complete schema definition
• /lib/db/index.ts - Database client
• /lib/db/queries/user-queries.ts - User query helpers
• /lib/db/queries/list-queries.ts - List query helpers
• /lib/db/queries/dashboard-queries.ts - Dashboard queries
• /lib/db/migrate.ts - Migration runner
• /drizzle/ - Migration files
• /scripts/update-database-schema.js - Schema update script
• /scripts/baseline-drizzle-supabase.js - Schema baseline

Testing & Validation

Test Database Connection:

node scripts/test-local-db.js

Inspect Database:

node scripts/inspect-db.js

Test Query Performance:

node scripts/test-db-performance.js

Expected Results:

• Connection successful
• All tables exist
• Indexes created
• Queries return in <100ms

Schema Relationships

users (1) ← (1) user_subscriptions
users (1) ← (1) user_billing
users (1) ← (1) user_usage
users (1) ← (1) user_system_data

users (1) ← (N) campaigns
campaigns (1) ← (N) scraping_jobs
scraping_jobs (1) ← (N) scraping_results

users (1) ← (N) creator_lists
creator_lists (1) ← (N) list_items

subscription_plans (1) ← (N) user_subscriptions

Performance Tips

1. Use Indexes: Add indexes for frequently queried columns
2. Limit Results: Always use limit for large datasets
3. Select Specific Columns: Don't select entire JSONB when you need one field
4. Use Relations: Leverage Drizzle's with for joins
5. Batch Operations: Use transactions for multiple related inserts
6. Connection Pooling: Supabase handles this automatically
7. JSONB Queries: Use GIN indexes for JSONB containment queries

Additional Resources

• Drizzle ORM Documentation
• Postgres JSONB Guide
• Supabase Docs
• Internal: /scripts/analyze-database.js for schema analysis