Database Schema Reference

The database schema is defined using Drizzle ORM in the database package.

It's an SQLite database designed for Cloudflare D1, compatible with SQLite for local development.

Quick Commands

Apply migrations (remote):

wrangler d1 migrations apply DB --remote --config packages/api/wrangler.production.toml

Apply migrations (local):

wrangler d1 migrations apply pixflare-db-local --local --config packages/api/wrangler.dev.toml
# or
pnpm migrate:d1:local

Visual database explorer:

cd packages/database && pnpm drizzle-kit studio --config drizzle.local.config.ts
# or
make db-explorer

Entity Relationship Diagram

erDiagram
    images ||--o{ user_settings : has
    images ||--o{ custom_domains : has
    images ||--o{ crypto_events : has

    users {
        text owner PK
        text cf_sub UK
        text authjs_user_id
        text display_name
        text email
        text plan_tier
        text stripe_customer_id
        text stripe_subscription_id
        text subscription_status
        text subscription_current_period_end
        text billing_cycle
        integer tmk_version
        text tmk_kv_key
        text name
        text profile_picture
        text bio
        text website
        text github
        text created_at
        text updated_at
    }

    authjs_users {
        text id PK
        text name
        text email UK
        text image
        text userId
        text type
        text provider
        text providerAccountId
        text refresh_token
        text access_token
        integer expires_at
        text token_type
        text scope
        text id_token
        text session_state
    }

    authjs_accounts {
        text userId
        text type
        text provider
        text providerAccountId
        text refresh_token
        text access_token
        integer expires_at
        text token_type
        text scope
        text id_token
        text session_state
    }

    authjs_sessions {
        text sessionToken PK
        text userId
        text identifier
        text token
    }

    authjs_verification_tokens {
        text identifier
        text token
    }

    albums {
        text owner
        text album_slug
        text title
        text description
        text permission
        text created_at
        text updated_at
    }

    images {
        text id PK
        text owner
        text filename
        text album_slug
        text content_type
        integer size_bytes
        integer width
        integer height
        text alt
        integer is_private
        integer strip_metadata
        integer nsfw
        text license
        text visibility
        text permission
        text expiration_date
        integer uploaded
        text variants_ready
        text deleted_at
        text created_at
        text updated_at
        text last_accessed_at
        text backup_status
        text backup_synced_at
        text backup_s3_key
        text backup_error
        integer is_encrypted
        integer key_version
    }

    tags {
        text owner
        text tag_slug
        text description
        text color
        text created_at
        text updated_at
    }

    image_tags {
        text image_id
        text owner
        text tag_slug
    }

    favourites {
        text owner
        text image_id
        text created_at
    }

    image_ai {
        text owner
        text image_id
        text analyzed_at
        text labels_json
        text label_scores_json
        text caption
        text colors_json
        real nsfw_score
        integer nsfw_flagged
        text model_version
        integer processing_time_ms
    }

    api_keys {
        text key_id PK
        text owner
        text key_hash
        text name
        text scopes
        text created_at
        text expires_at
        text revoked_at
        text last_used_at
        text allowed_ips
        text owner
        integer enabled
        text s3_endpoint
        text s3_region
        text s3_bucket
        text s3_path_prefix
        text s3_access_key_id_hash
        text last_tested_at
        text test_status
        text created_at
        text updated_at
        text last_sync_at
        text alias_owner
        text alias_album_slug
        text alias_filename
        text image_id
        text created_at
    }

    user_backup_configs {
        text owner
        integer enabled
        text s3_endpoint
        text s3_region
        text s3_bucket
        text s3_path_prefix
        text s3_access_key_id_hash
        text last_tested_at
        text test_status
        text created_at
        text updated_at
        text last_sync_at
        text alias_owner
        text alias_album_slug
        text alias_filename
        text image_id
        text created_at
    }

    image_aliases {
        text alias_owner
        text alias_album_slug
        text alias_filename
        text image_id
        text created_at
    }

    audit_logs {
        text id PK
        text owner
        text created_at
        text action
        text resource_type
        text resource_id
        text status
        integer affected_count
        text metadata
    }

    user_settings {
        text owner
        text theme
        text language
        text accessibility_settings
        text upload_defaults
        text image_view_mode
        text embed_url_format
        integer embed_use_custom_domain
        text embed_enabled_formats
        text created_at
        text updated_at
        text id PK
        text owner
        text hostname UK
        text status
        text cloudflare_id
        text verification_method
        text cname_target
        text txt_name
        text txt_value
        text last_error
        text created_at
        text updated_at
        text id PK
        text owner
        text image_id FK
        text type
        text detail
        text created_at
    }

    custom_domains {
        text id PK
        text owner
        text hostname UK
        text status
        text cloudflare_id
        text verification_method
        text cname_target
        text txt_name
        text txt_value
        text last_error
        text created_at
        text updated_at
        text id PK
        text owner
        text image_id FK
        text type
        text detail
        text created_at
    }

    crypto_events {
        text id PK
        text owner
        text image_id FK
        text type
        text detail
        text created_at
    }

    webhooks {
        text id PK
        text owner
        text url
        text events
        text description
        text secret
        integer enabled
        integer failure_count
        text last_triggered_at
        text created_at
        text updated_at
    }

    webhook_deliveries {
        text id PK
        text webhook_id
        text event_type
        text payload
        integer status
        text response
        integer duration_ms
        integer attempt
        text delivered_at
    }

Table Reference

users

Column	Type & Constraints
owner	text - PK, NOT NULL
cf_sub	text - UNIQUE Cloudflare Access subject (UUID) - unique identifier
authjs_user_id	text Auth.js user ID - for authjs mode
display_name	text
email	text
plan_tier	text - NOT NULL, DEFAULT: free Usage plan: free, starter, pro
stripe_customer_id	text Stripe customer ID
stripe_subscription_id	text Stripe subscription ID
subscription_status	text Stripe subscription status (active, canceled, past_due, etc)
subscription_current_period_end	text ISO 8601 date when current period ends
billing_cycle	text Billing cycle: monthly or annual
tmk_version	integer - NOT NULL, DEFAULT: 1 Tenant Master Key version for encryption
tmk_kv_key	text KV key where wrapped TMK is stored (e.g., "tmk:alice:v1")
name	text Public profile name
profile_picture	text Public profile picture URL
bio	text Public profile bio (256 chars max)
website	text Public profile website URL
github	text Public profile GitHub username/URL
created_at	text - NOT NULL
updated_at	text - NOT NULL

Indexes:

• idx_users_plan_tier (index) on plan_tier
• idx_users_stripe_customer (index) on stripe_customer_id
• idx_users_stripe_subscription (index) on stripe_subscription_id

---

authjs_users

Column	Type & Constraints
id	text - PK
name	text
email	text - UNIQUE, NOT NULL
image	text
userId	text
type	text - NOT NULL
provider	text - NOT NULL
providerAccountId	text - NOT NULL
refresh_token	text
access_token	text
expires_at	integer
token_type	text
scope	text
id_token	text
session_state	text

---

authjs_accounts

Column	Type & Constraints
userId	text
type	text - NOT NULL
provider	text - NOT NULL
providerAccountId	text - NOT NULL
refresh_token	text
access_token	text
expires_at	integer
token_type	text
scope	text
id_token	text
session_state	text

---

authjs_sessions

Column	Type & Constraints
sessionToken	text - PK
userId	text
identifier	text - NOT NULL
token	text - NOT NULL

---

authjs_verification_tokens

Column	Type & Constraints
identifier	text - NOT NULL
token	text - NOT NULL

---

albums

Column	Type & Constraints
owner	text
album_slug	text - NOT NULL
title	text - NOT NULL
description	text
permission	text - NOT NULL, DEFAULT: private 'public' or 'private'
created_at	text - NOT NULL
updated_at	text - NOT NULL

Indexes:

• idx_albums_permission (index) on owner, permission

---

images

Column	Type & Constraints
id	text - PK, NOT NULL
owner	text
filename	text - NOT NULL
album_slug	text - NOT NULL
content_type	text
size_bytes	integer
width	integer
height	integer
alt	text
is_private	integer - NOT NULL, DEFAULT: 0
strip_metadata	integer - NOT NULL, DEFAULT: 0
nsfw	integer - NOT NULL, DEFAULT: 0
license	text - NOT NULL, DEFAULT: none
visibility	text - NOT NULL, DEFAULT: unlisted
permission	text - NOT NULL, DEFAULT: inherit 'public', 'private', or 'inherit'
expiration_date	text
uploaded	integer - NOT NULL, DEFAULT: 0
variants_ready	text - NOT NULL, DEFAULT: []
deleted_at	text
created_at	text - NOT NULL
updated_at	text - NOT NULL
last_accessed_at	text
backup_status	text - DEFAULT: pending
backup_synced_at	text
backup_s3_key	text
backup_error	text
is_encrypted	integer - NOT NULL, DEFAULT: 0 Whether image is encrypted at rest
key_version	integer TMK version used to wrap the CEK

Indexes:

• images_owner_filename_unique (uniqueIndex) on owner, filename
• idx_images_permission (index) on owner, permission

---

tags

Column	Type & Constraints
owner	text
tag_slug	text - NOT NULL
description	text
color	text
created_at	text - NOT NULL
updated_at	text - NOT NULL

Indexes:

• tags_owner_idx (index) on owner

---

image_tags

Column	Type & Constraints
image_id	text
owner	text
tag_slug	text - NOT NULL

Indexes:

• image_tags_owner_tag_slug_idx (index) on owner, tag_slug

---

favourites

Column	Type & Constraints
owner	text
image_id	text
created_at	text - NOT NULL

Indexes:

• favourites_owner_idx (index) on owner
• favourites_image_id_idx (index) on image_id
• favourites_created_at_idx (index) on created_at

---

image_ai

Column	Type & Constraints
owner	text
image_id	text
analyzed_at	text - NOT NULL
labels_json	text
label_scores_json	text
caption	text
colors_json	text
nsfw_score	real
nsfw_flagged	integer - NOT NULL, DEFAULT: 0
model_version	text
processing_time_ms	integer

Indexes:

• image_ai_owner_idx (index) on owner
• image_ai_nsfw_idx (index) on nsfw_flagged
• image_ai_analyzed_at_idx (index) on analyzed_at

---

api_keys

Column	Type & Constraints
key_id	text - PK, NOT NULL
owner	text
key_hash	text - NOT NULL
name	text
scopes	text - NOT NULL
created_at	text - NOT NULL
expires_at	text
revoked_at	text
last_used_at	text
allowed_ips	text
owner	text
enabled	integer - NOT NULL, DEFAULT: 0
s3_endpoint	text - NOT NULL
s3_region	text - NOT NULL
s3_bucket	text - NOT NULL
s3_path_prefix	text - NOT NULL
s3_access_key_id_hash	text - NOT NULL
last_tested_at	text
test_status	text
created_at	text - NOT NULL
updated_at	text - NOT NULL
last_sync_at	text
alias_owner	text - NOT NULL
alias_album_slug	text - NOT NULL
alias_filename	text - NOT NULL
image_id	text
created_at	text - NOT NULL

---

user_backup_configs

Column	Type & Constraints
owner	text
enabled	integer - NOT NULL, DEFAULT: 0
s3_endpoint	text - NOT NULL
s3_region	text - NOT NULL
s3_bucket	text - NOT NULL
s3_path_prefix	text - NOT NULL
s3_access_key_id_hash	text - NOT NULL
last_tested_at	text
test_status	text
created_at	text - NOT NULL
updated_at	text - NOT NULL
last_sync_at	text
alias_owner	text - NOT NULL
alias_album_slug	text - NOT NULL
alias_filename	text - NOT NULL
image_id	text
created_at	text - NOT NULL

---

image_aliases

Column	Type & Constraints
alias_owner	text - NOT NULL
alias_album_slug	text - NOT NULL
alias_filename	text - NOT NULL
image_id	text
created_at	text - NOT NULL

---

audit_logs

Column	Type & Constraints
id	text - PK, NOT NULL
owner	text
created_at	text - NOT NULL
action	text - NOT NULL 'create', 'update', 'delete', 'restore'
resource_type	text - NOT NULL 'image', 'album', 'tag', etc.
resource_id	text Can be null for bulk ops or failed attempts
status	text - NOT NULL 'success', 'failed'
affected_count	integer - NOT NULL, DEFAULT: 1
metadata	text JSON string

Indexes:

• idx_audit_logs_owner_created (index) on owner, created_at
• idx_audit_logs_created (index) on created_at
• idx_audit_logs_resource (index) on resource_type, resource_id
• idx_audit_logs_action (index) on owner, action
• idx_audit_logs_unique_event (uniqueIndex) on owner, action, resource_type, resource_id, status, created_at

---

user_settings

Column	Type & Constraints
owner	text
theme	text - NOT NULL, DEFAULT: auto
language	text - NOT NULL, DEFAULT: en
accessibility_settings	text - NOT NULL
upload_defaults	text - NOT NULL
image_view_mode	text - NOT NULL, DEFAULT: regular
embed_url_format	text - NOT NULL, DEFAULT: full
embed_use_custom_domain	integer - NOT NULL, DEFAULT: 0
embed_enabled_formats	text
created_at	text - NOT NULL
updated_at	text - NOT NULL
id	text - PK, NOT NULL
owner	text
hostname	text - UNIQUE, NOT NULL
status	text - NOT NULL, DEFAULT: pending
cloudflare_id	text
verification_method	text
cname_target	text
txt_name	text
txt_value	text
last_error	text
created_at	text - NOT NULL
updated_at	text - NOT NULL
id	text - PK, NOT NULL
owner	text
image_id	text - FK → images.id Nullable for owner-level ops
type	text - NOT NULL 'tmk_create', 'tmk_rotate', 'img_encrypt', 'bundle_export'
detail	text JSON blob with operation-specific metadata
created_at	text - NOT NULL

Indexes:

• idx_crypto_events_owner_created (index) on owner, created_at
• idx_crypto_events_type (index) on owner, type
• idx_crypto_events_image (index) on image_id

---

custom_domains

Column	Type & Constraints
id	text - PK, NOT NULL
owner	text
hostname	text - UNIQUE, NOT NULL
status	text - NOT NULL, DEFAULT: pending
cloudflare_id	text
verification_method	text
cname_target	text
txt_name	text
txt_value	text
last_error	text
created_at	text - NOT NULL
updated_at	text - NOT NULL
id	text - PK, NOT NULL
owner	text
image_id	text - FK → images.id Nullable for owner-level ops
type	text - NOT NULL 'tmk_create', 'tmk_rotate', 'img_encrypt', 'bundle_export'
detail	text JSON blob with operation-specific metadata
created_at	text - NOT NULL

Indexes:

• idx_crypto_events_owner_created (index) on owner, created_at
• idx_crypto_events_type (index) on owner, type
• idx_crypto_events_image (index) on image_id

---

crypto_events

Column	Type & Constraints
id	text - PK, NOT NULL
owner	text
image_id	text - FK → images.id Nullable for owner-level ops
type	text - NOT NULL 'tmk_create', 'tmk_rotate', 'img_encrypt', 'bundle_export'
detail	text JSON blob with operation-specific metadata
created_at	text - NOT NULL

Indexes:

• idx_crypto_events_owner_created (index) on owner, created_at
• idx_crypto_events_type (index) on owner, type
• idx_crypto_events_image (index) on image_id

---

webhooks

Column	Type & Constraints
id	text - PK, NOT NULL
owner	text
url	text - NOT NULL
events	text - NOT NULL JSON array of event types
description	text
secret	text - NOT NULL
enabled	integer - NOT NULL, DEFAULT: 1
failure_count	integer - NOT NULL, DEFAULT: 0
last_triggered_at	text
created_at	text - NOT NULL
updated_at	text - NOT NULL

Indexes:

• idx_webhooks_owner (index) on owner
• idx_webhooks_enabled (index) on enabled
• idx_webhooks_owner_enabled (index) on owner, enabled

---

webhook_deliveries

Column	Type & Constraints
id	text - PK, NOT NULL
webhook_id	text
event_type	text - NOT NULL
payload	text - NOT NULL JSON payload
status	integer - NOT NULL HTTP status code
response	text Response body (truncated)
duration_ms	integer
attempt	integer - NOT NULL, DEFAULT: 1
delivered_at	text - NOT NULL

Indexes:

• idx_webhook_deliveries_webhook (index) on webhook_id
• idx_webhook_deliveries_delivered (index) on delivered_at

---

Type Exports

Drizzle ORM automatically generates TypeScript types for each table:

// Select types (for reading from database)
import type { User, Image, Album, Tag } from '@database';

// Insert types (for inserting into database)
import type { NewUser, NewImage, NewAlbum, NewTag } from '@database';

See database/schema.ts for all available types.

---

Migrations Reference

Complete history of D1 database migrations.

How to Apply Migrations

Production:

wrangler d1 migrations apply DB --remote --config packages/api/wrangler.production.toml
# or
pnpm migrate:d1

Local:

wrangler d1 migrations apply pixflare-db-local --local --config packages/api/wrangler.dev.toml
# or
pnpm migrate:d1:local

Migration History

#	Migration	Description	Lines
0000	001_init.sql	Pixelflare CDN - Initial Schema Migration	89
0000	002_api_keys_enhancements.sql	Add name, expiration, and IP whitelist support to API keys	7
0000	002_image_aliases.sql	Pixelflare CDN - Image Aliases Migration	13
0000	003_tags_table.sql	Pixelflare CDN - Tags Table Migration	21
0000	004_favourites.sql	Pixelflare CDN - Favourites Table Migration	25
0000	005_image_metadata.sql	Add new metadata fields to images table	10
0000	006_fts5_search.sql	Pixelflare CDN - FTS5 Search Migration	130
0000	007_add_cf_sub.sql	Add cf_sub field to users table for Cloudflare Access subject tracking	6
0000	008_analytics_aggregation.sql	Migration 008: Analytics Aggregation Tables	54
0000	009_fix_analytics_nullable.sql	Migration 009: Fix analytics_daily to use sentinel values instead of NULL	30
0000	010_add_referrer_tracking.sql	Migration 010: Add referrer tracking to analytics_daily	45
0000	010_ai_classification.sql	Migration 010: AI Image Classification	46
0000	011_fts_ai_fields.sql	Migration 011: Add AI-generated fields to FTS5 search	255
0000	012_s3_backup_settings.sql	Add S3 backup configuration and tracking	31
0000	013_audit_logs.sql	Migration: Create audit logs table for tracking user actions	29
0000	014_user_plan_tier.sql	Add plan tier column to users table	8
0000	015_user_settings.sql	User Settings Table	25
0000	016_custom_domains.sql	Custom Domains Table	38
0000	017_image_view_preferences.sql	Migration: Add image view and embed preferences to user settings	9
0000	018_fts_searchable_image_id.sql	Migration 018: Make image_id searchable in FTS5	258
0000	019_authjs.sql	Migration: Replace Better Auth with Auth.js	51
0000	020_saved_custom_variants.sql	Migration: Add saved custom variants to user settings	6
0000	021_encryption.sql	Migration: Add image encryption support	41
0000	022_public_profiles_permissions.sql	Migration 022: Public Profiles and Permissions	21
0000	023_stripe_billing.sql	Migration 023: Stripe Billing Fields	12
0000	024_billing_enhancements.sql	Add billing cycle and cancellation tracking columns	4
0000	025_profile_visibility.sql	Add profile visibility control	7
0000	026_audit_logs_unique_event.sql	Add unique index to prevent duplicate audit log entries	14
0000	027_sponsor_plan.sql	Add support for 'sponsor' plan tier	10
0000	028_webhooks.sql	Webhooks feature	40

Latest Migration: 028_webhooks.sql

---

Documentation Statistics

20 tables · 241 columns · 33 indexes · 3 foreign keys · 30 migrations

Auto-generated from database/schema.ts and database/migrations/. Run pnpm docs:gen to update.

Last updated: 2026-01-21