Database Documentation

Chameleon uses Drizzle ORM and supports both PostgreSQL and MySQL databases.

Database Overview

The system automatically detects your database type from the DATABASE_URL and uses the appropriate driver:

• PostgreSQL: postgresql:// or postgres://
• MySQL: mysql://

Supported Databases

PostgreSQL (Recommended)

• Vercel Postgres - Serverless, auto-scaling (recommended for Vercel)
• Supabase - Open source, generous free tier
• Railway - Simple deployment
• Neon - Serverless Postgres
• AWS RDS - Enterprise grade

MySQL

• PlanetScale - Serverless MySQL, generous free tier
• Railway - Simple deployment
• AWS RDS - Enterprise grade
• Azure MySQL - Cloud database

Database Schema

Core Tables

users

Stores user account information.

Column	Type	Description
id	integer	Primary key
uuid	varchar(255)	Unique user identifier
email	varchar(255)	User email (unique)
nickname	varchar(255)	Display name
avatar_url	varchar(255)	Profile picture URL
invite_code	varchar(255)	Auto-generated unique invite code
invited_by	varchar(255)	UUID of referrer
is_affiliate	boolean	Affiliate program status
created_at	timestamp	Account creation time
updated_at	timestamp	Last update time

Key Features:

• Auto-generated UUID for each user
• Auto-generated unique invite code (6-8 characters)
• OAuth integration (Google, GitHub)
• Referral tracking

orders

Stores payment orders and subscriptions.

Column	Type	Description
id	integer	Primary key
order_no	varchar(255)	Unique order number
user_uuid	varchar(255)	User who placed order
product_id	varchar(255)	Product identifier
product_name	varchar(255)	Product display name
amount	integer	Amount in cents
currency	varchar(10)	Currency code (USD, EUR, etc.)
status	varchar(50)	Order status
pay_type	varchar(50)	Payment method (stripe, creem)
sub_id	varchar(255)	Stripe subscription ID
interval	varchar(50)	Billing interval (month, year)
created_at	timestamp	Order creation time
paid_at	timestamp	Payment completion time

Statuses:

• pending - Order created, awaiting payment
• paid - Payment successful
• failed - Payment failed
• cancelled - Order cancelled
• refunded - Order refunded

credits

Stores user credit balances.

Column	Type	Description
id	integer	Primary key
user_uuid	varchar(255)	User identifier
left_credits	integer	Current balance
total_credits	integer	Lifetime earned credits
updated_at	timestamp	Last update time

credits_trans

Credit transaction history.

Column	Type	Description
id	integer	Primary key
user_uuid	varchar(255)	User identifier
trans_type	varchar(50)	Transaction type
credits	integer	Credit amount (+ or -)
expired_at	varchar(255)	Expiration time
created_at	timestamp	Transaction time

Transaction Types:

• new_user - New user bonus (100 credits)
• order_pay - Purchased credits
• system_add - Admin added credits
• ping - Daily check-in reward (1 credit)
• ai_generation - AI generation cost

generations

AI generation history and results.

Column	Type	Description
id	integer	Primary key
gen_id	varchar(255)	Unique generation ID
user_uuid	varchar(255)	User who created
ai_type	varchar(50)	Type: text, image, video
provider	varchar(50)	AI provider name
model	varchar(255)	Model name
prompt	text	User prompt
result_urls	text	JSON array of result URLs
status	varchar(50)	Generation status
credits_cost	integer	Credits consumed
created_at	timestamp	Task creation time
completed_at	timestamp	Completion time
error_message	text	Error details if failed

Statuses:

• pending - Task created
• processing - AI is generating
• completed - Success, results available
• failed - Generation failed

ai_provider_keys

AI provider API key management (Admin only).

Column	Type	Description
id	integer	Primary key
provider_name	varchar(50)	Provider identifier (unique)
api_key	text	Encrypted API key
model_config	text	JSON configuration
is_enabled	boolean	Enable/disable provider
updated_at	timestamp	Last update time

Providers:

• openai, deepseek, kling, seedance, replicate, openrouter, siliconflow

Content Tables

posts

Blog posts and content.

Column	Type	Description
id	integer	Primary key
uuid	varchar(255)	Unique post ID
title	varchar(255)	Post title
description	text	Post description
content	text	Markdown content
slug	varchar(255)	URL slug
locale	varchar(10)	Language (en, zh, etc.)
status	varchar(50)	Draft or published
created_at	timestamp	Creation time
updated_at	timestamp	Last edit time

feedbacks

User feedback submissions.

Column	Type	Description
id	integer	Primary key
user_uuid	varchar(255)	Submitter
content	text	Feedback text
rating	integer	1-5 star rating
created_at	timestamp	Submission time

affiliates

Referral program tracking.

Column	Type	Description
id	integer	Primary key
user_uuid	varchar(255)	Affiliate (referrer)
order_uuid	varchar(255)	Referred order
customer_email	varchar(255)	Referred customer
order_amount	integer	Order value
commission_rate	integer	% rate (0-100)
commission_amount	integer	Commission earned
status	varchar(50)	Pending, approved, paid
created_at	timestamp	Record creation

apikeys

User API keys for programmatic access.

Column	Type	Description
id	integer	Primary key
name	varchar(255)	Key name/label
key	varchar(255)	API key (unique)
user_uuid	varchar(255)	Key owner
status	varchar(50)	Active or revoked
created_at	timestamp	Creation time

Database Migrations

Running Migrations

# Push schema changes to database
pnpm db:push

This command:

1. Reads the schema from src/db/schema.ts
2. Compares with current database state
3. Generates and applies migration SQL
4. Creates new tables and columns

Migration Files

Migrations are stored in src/db/migrations/ for reference but are auto-generated.

Rollback

Drizzle doesn't support automatic rollback. To undo changes:

1. Manually drop tables or columns
2. Or restore from database backup

Switching Databases

From MySQL to PostgreSQL

1. Export data from MySQL:

   mysqldump -u user -p database > backup.sql
   

2. Update DATABASE_URL to PostgreSQL

   DATABASE_URL="postgresql://user:pass@host:5432/database"
   

3. Run migration:

   pnpm db:push
   

4. Import data (manual process, adjust SQL syntax)

From PostgreSQL to MySQL

1. Export data:

   pg_dump database > backup.sql
   

2. Update DATABASE_URL to MySQL

3. Run migration

4. Import data (adjust SQL syntax)

Backup and Restore

Automated Backups

Vercel Postgres:

• Automatic daily backups (Pro plan)
• Point-in-time recovery

PlanetScale:

• Automatic backups on paid plans
• Branching for testing

Manual Backup

PostgreSQL:

pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

MySQL:

mysqldump -u user -p database > backup_$(date +%Y%m%d).sql

Restore

PostgreSQL:

psql $DATABASE_URL < backup.sql

MySQL:

mysql -u user -p database < backup.sql

Database Performance

Indexes

Key indexes for performance:

• users.email - Unique index for fast login
• users.uuid - Unique index for user lookups
• users.invite_code - Unique index for referrals
• orders.user_uuid - Index for user orders
• credits_trans.user_uuid - Index for credit history
• generations.gen_id - Unique index for generation lookup
• generations.user_uuid - Index for user generation history

Optimization Tips

1. Use connection pooling (built-in with Drizzle)
2. Limit query results - Always use .limit() for lists
3. Index frequently queried fields
4. Archive old data - Move old records to archive tables
5. Monitor slow queries - Use database analytics

Common Queries

Get user with credits

import { db } from "@/db";
import { users, credits } from "@/db/schema";
import { eq } from "drizzle-orm";

const userWithCredits = await db()
  .select({
    user: users,
    credits: credits,
  })
  .from(users)
  .leftJoin(credits, eq(users.uuid, credits.user_uuid))
  .where(eq(users.email, "user@example.com"))
  .limit(1);

Get user's orders

import { orders } from "@/db/schema";
import { eq, desc } from "drizzle-orm";

const userOrders = await db()
  .select()
  .from(orders)
  .where(eq(orders.user_uuid, user_uuid))
  .orderBy(desc(orders.created_at))
  .limit(10);

Get AI generation history

import { generations } from "@/db/schema";

const history = await db()
  .select()
  .from(generations)
  .where(eq(generations.user_uuid, user_uuid))
  .orderBy(desc(generations.created_at))
  .limit(20);

Database Security

1. Use environment variables for database credentials
2. Enable SSL for remote connections
3. Restrict IP access to database server
4. Regular backups - Daily minimum
5. Encrypt sensitive data - Consider encrypting API keys in database
6. Audit access logs - Monitor database access

Troubleshooting

Connection refused

Check:

• Database server is running
• Firewall allows connection
• DATABASE_URL is correct
• SSL settings match database requirements

Permission denied

• Verify database user has CREATE, SELECT, INSERT, UPDATE, DELETE permissions
• Check connection string credentials

Table doesn't exist

Run migration:

pnpm db:push

Slow queries

• Add indexes to frequently queried columns
• Use .limit() on all queries
• Consider database caching (Redis)

Next Steps

• Database Schema Reference - Detailed schema
• Deployment Guide - Deploy with database
• API Reference - How to query the database via APIs