Database Tables
users
User table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| avatar_url | TEXT | Avatar URL | |
| first_name | VARCHAR(255) | First name | |
| last_name | VARCHAR(255) | Last name | |
| username | VARCHAR(255) | Username | |
| password | TEXT | Password (argon2 hashed) | |
| role | VARCHAR(20) | member | System role (owner / member) |
| status | VARCHAR(20) | active | Status (active / disabled) |
| created_at | BIGINT | now | Created at |
| updated_at | BIGINT | now | Updated at |
| deleted_at | BIGINT | NULL | Soft delete timestamp |
user_emails
User emails table. Supports multiple emails per user.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| user_id | UUID | User ID | |
| TEXT | |||
| is_primary | BOOLEAN | false | Primary email |
| verified | BOOLEAN | false | Email verified |
| via | VARCHAR(80) | email:password | Signup source (email:password / google) |
| created_at | BIGINT | now | Created at |
| updated_at | BIGINT | now | Updated at |
Unique constraints: (user_id, email), (user_id, is_primary)
user_provider_customers
Third-party payment customer ID mapping.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| user_id | UUID | User ID | |
| user_email_id | UUID | User email ID | |
| provider | VARCHAR(50) | Payment provider (stripe) | |
| customer_id | VARCHAR(255) | Provider customer ID | |
| created_at | BIGINT | now | Created at |
Unique constraint: (user_id, provider)
user_email_preferences
Email subscription preferences.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| user_id | UUID | User ID | |
| email_type | VARCHAR(50) | Email type (marketing / product_updates / payment_receipts) | |
| subscribed | BOOLEAN | true | Subscribed |
| created_at | BIGINT | now | Created at |
| updated_at | BIGINT | now | Updated at |
Unique constraint: (user_id, email_type)
tenants
Tenant (organization) table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| name | VARCHAR(255) | Org name | |
| slug | VARCHAR(255) | URL slug (unique) | |
| logo_url | TEXT | Logo | |
| owner_id | UUID | Creator ID | |
| is_default | BOOLEAN | false | Default org |
| deleted_at | BIGINT | Soft delete timestamp | |
| created_at | BIGINT | now | Created at |
| updated_at | BIGINT | now | Updated at |
tenant_members
Tenant membership table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| tenant_id | UUID | Org ID | |
| user_id | UUID | User ID | |
| role | VARCHAR(20) | member | Role (owner / member / custom) |
| created_at | BIGINT | now | Joined at |
| updated_at | BIGINT | now | Updated at |
Unique constraint: (tenant_id, user_id)
tenant_invitations
Tenant invitation table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| tenant_id | UUID | Org ID | |
| VARCHAR(255) | Invitee email | ||
| role | VARCHAR(20) | member | Assigned role |
| invited_by | UUID | Inviter ID | |
| token | VARCHAR(255) | Invitation token (unique) | |
| status | VARCHAR(20) | pending | Status (pending / accepted) |
| credit_limit | INTEGER | -1 | Credit limit (-1 = unlimited) |
| expires_at | BIGINT | Expiration time | |
| created_at | BIGINT | now | Created at |
Unique constraint: (tenant_id, email)
tenant_member_credit_limits
Member credit limit table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| tenant_id | UUID | Org ID | |
| user_id | UUID | Member ID | |
| credit_limit | INTEGER | -1 | Credit limit (-1 = unlimited) |
| used_credits | INTEGER | 0 | Credits used |
| created_at | BIGINT | now | Created at |
| updated_at | BIGINT | now | Updated at |
Unique constraint: (tenant_id, user_id)
subscriptions
Subscription table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| provider | VARCHAR(50) | stripe | Payment provider |
| user_id | UUID | User ID | |
| tenant_id | UUID | Org ID | |
| plan_id | VARCHAR(255) | Plan ID | |
| status | VARCHAR(20) | pending | Status (active / pending / canceled / past_due) |
| amount | INTEGER | Amount (cents) | |
| currency | VARCHAR(10) | usd | Currency |
| provider_subscription_id | VARCHAR(255) | Stripe subscription ID (unique) | |
| provider_customer_id | VARCHAR(255) | Stripe customer ID | |
| provider_price_id | VARCHAR(255) | Stripe price ID | |
| current_period_start | BIGINT | Current period start | |
| current_period_end | BIGINT | Current period end | |
| canceled_at | BIGINT | NULL | Canceled at |
| raw_data | JSONB | Stripe raw data | |
| created_at | BIGINT | now | Created at |
| updated_at | BIGINT | now | Updated at |
credits
Credits table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| provider | VARCHAR(50) | stripe | Payment provider |
| user_id | UUID | User ID | |
| tenant_id | UUID | Org ID | |
| plan_id | VARCHAR(255) | Plan ID | |
| mode | VARCHAR(100) | Source (free / subscription / one_time:credits) | |
| total_credits | BIGINT | Total credits | |
| remaining_credits | BIGINT | Remaining credits | |
| amount | INTEGER | 0 | Amount (cents) |
| currency | VARCHAR(10) | usd | Currency |
| priority | INTEGER | 50 | Consumption priority (higher = consumed first) |
| source_type | VARCHAR(100) | Event type (checkout.session.completed, etc.) | |
| source_id | VARCHAR(255) | Event ID | |
| provider_price_id | VARCHAR(255) | Stripe price ID | |
| provider_customer_id | VARCHAR(255) | Stripe customer ID | |
| expires_at | BIGINT | NULL | Expiration (NULL = never) |
| effective_at | BIGINT | Effective from | |
| raw_data | JSONB | Stripe raw data | |
| created_at | BIGINT | now | Created at |
Unique constraint: (user_id, source_id)
credit_transactions
Credit transaction records.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| user_id | UUID | User ID | |
| tenant_id | UUID | Org ID | |
| credit_id | UUID | Credit package ID | |
| credits | INTEGER | Credit amount | |
| type | VARCHAR(50) | Type (add:purchase / deduct / deduct:overdraft) | |
| description | TEXT | Description | |
| created_at | BIGINT | now | Created at |
credit_overdrafts
Credit overdraft records.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| user_id | UUID | User ID | |
| tenant_id | UUID | Org ID | |
| credits | BIGINT | Overdraft credits | |
| created_at | BIGINT | now | Created at |
| updated_at | BIGINT | now | Updated at |
lifetime
Lifetime deal table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| provider | VARCHAR(50) | stripe | Payment provider |
| user_id | UUID | User ID | |
| tenant_id | UUID | Org ID | |
| plan_id | VARCHAR(255) | Plan ID | |
| amount | INTEGER | Amount (cents) | |
| currency | VARCHAR(10) | usd | Currency |
| provider_price_id | VARCHAR(255) | Stripe price ID | |
| provider_customer_id | VARCHAR(255) | Stripe customer ID | |
| provider_payment_intent_id | VARCHAR(255) | Stripe PaymentIntent ID | |
| provider_checkout_session_id | VARCHAR(255) | Stripe Session ID (unique) | |
| raw_data | JSONB | Stripe raw data | |
| created_at | BIGINT | now | Created at |
invoices
Invoice table.
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| provider | VARCHAR(50) | stripe | Payment provider |
| user_id | UUID | User ID | |
| tenant_id | UUID | Org ID | |
| plan_id | VARCHAR(255) | Plan ID | |
| amount | INTEGER | Amount (cents) | |
| currency | VARCHAR(10) | usd | Currency |
| billing_reason | VARCHAR(50) | Reason (subscription_create / subscription_cycle / manual) | |
| provider_invoice_id | VARCHAR(255) | Stripe invoice ID (unique) | |
| provider_price_id | VARCHAR(255) | Stripe price ID | |
| provider_customer_id | VARCHAR(255) | Stripe customer ID | |
| invoice_pdf | TEXT | Invoice PDF URL | |
| receipt_pdf | TEXT | Receipt PDF URL | |
| hosted_invoice_url | TEXT | Online view URL | |
| raw_data | JSONB | Stripe raw data | |
| paid_at | BIGINT | Paid at |
prices
Price table (synced from Stripe).
| Column | Type | Default | Description |
|---|---|---|---|
| id | UUID | auto | Primary key |
| provider | VARCHAR(50) | stripe | Payment provider |
| provider_price_id | VARCHAR(255) | Stripe Price ID | |
| product_id | VARCHAR(255) | Stripe Product ID | |
| active | BOOLEAN | true | Active |
| currency | VARCHAR(3) | Currency | |
| unit_amount | INTEGER | Unit price (cents) | |
| billing_scheme | VARCHAR(50) | Billing scheme (per_unit) | |
| type | VARCHAR(50) | Type (recurring / one_time) | |
| interval | VARCHAR(20) | Interval (month / year) | |
| interval_count | INTEGER | Interval count | |
| trial_period_days | INTEGER | Trial days | |
| usage_type | VARCHAR(50) | Usage type (licensed / metered) | |
| metadata | JSONB | Metadata | |
| raw_data | JSONB | Stripe raw data | |
| created_at | TIMESTAMP | NOW() | Created at |
| updated_at | TIMESTAMP | NOW() | Updated at |
Unique constraint: (provider, provider_price_id)