Database Schema

Native Payments Standard

Bridge Payments is built on the Native Payments standard - a comprehensive, production-ready database schema designed for modern payment systems created by the Pubflow Native Payments team. This schema supports multi-provider payments, subscriptions, organizations, guest checkout, and advanced features like coupons and analytics.

Core Tables - Users, Organizations, Authentication
Payment Tables - Payments, Methods, Providers
Product & Order Tables - Products, Orders, Subscriptions
Advanced Features - Coupons, Tax Rates, Analytics
Indexes & Performance - Optimized indexes for scale

Overview

The Native Payments schema is designed with these principles:

✅ Multi-Provider Support - Works with Stripe, PayPal, Authorize.net, and more
✅ Guest Checkout - Full support for anonymous and registered guests
✅ Multi-Tenant - Organization support with role-based access
✅ Soft Deletes - User data retention with GDPR compliance
✅ Unified Pricing - Consistent pricing model across all entities
✅ Extensible - JSON metadata fields for custom data
✅ Performance - Comprehensive indexing strategy

Core Tables

users

The central table for all user information with comprehensive profile support.

Key Features:

Soft delete support with deleted_at and deletion_reason
Multiple contact methods (email, phone, mobile, recovery_email)
Profile information (display_name, bio, gender, dob, timezone)
Security features (2FA, account locking, email verification)
Language preferences and first-time user tracking

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`email`	TEXT	Unique email address (required)
`user_type`	TEXT	'individual', 'business', 'admin'
`name`	TEXT	First name (optional)
`last_name`	TEXT	Last name (optional)
`is_verified`	INTEGER	Email verification status (0/1)
`phone`	TEXT	Primary phone (unique)
`mobile`	TEXT	Alternative mobile number
`recovery_email`	TEXT	Recovery email address
`display_name`	TEXT	Display name for UI
`bio`	TEXT	User biography (max 500 chars)
`gender`	TEXT	ISO 5218: 'm', 'f', 'x'
`dob`	TEXT	Date of birth (YYYY-MM-DD)
`tmz`	TEXT	IANA timezone (e.g., America/New_York)
`is_locked`	INTEGER	Account lock status (0/1)
`two_factor`	INTEGER	2FA enabled status (0/1)
`lang`	TEXT	Language preference ('en', 'es', 'ja')
`first_time`	INTEGER	First-time user flag (0/1)
`deleted_at`	TEXT	Soft delete timestamp (NULL = active)
`deletion_reason`	TEXT	Reason for deletion
`reference_id`	TEXT	External reference ID
`metadata`	TEXT	JSON string for additional data
`created_at`	TEXT	Creation timestamp
`updated_at`	TEXT	Last update timestamp

Indexes:

Email, username, phone (for active users)
Soft delete status
Verification status
Security features (locked accounts, 2FA)
Temporal queries (created_at, updated_at)

View SQL Schema

sql

CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    name TEXT,
    last_name TEXT,
    email TEXT NOT NULL UNIQUE,
    user_type TEXT NOT NULL,
    picture TEXT,
    user_name TEXT UNIQUE,
    password_hash TEXT,
    is_verified INTEGER NOT NULL DEFAULT 0,
    phone TEXT UNIQUE,
    mobile TEXT,
    recovery_email TEXT,
    display_name TEXT,
    bio TEXT,
    gender TEXT,
    dob TEXT,
    tmz TEXT,
    is_locked INTEGER NOT NULL DEFAULT 0,
    two_factor INTEGER NOT NULL DEFAULT 0,
    lang TEXT NULL,
    first_time INTEGER NOT NULL DEFAULT 1,
    deleted_at TEXT NULL,
    deletion_reason TEXT NULL,
    reference_id TEXT,
    metadata TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

tokens

Authentication and security tokens for magic links, password resets, and verification.

Key Features:

Support for email, phone, and username-based tokens
Attempt tracking with remaining attempts
Multiple token types (magic_link, password_reset, email_verification, phone_verification)
Status tracking (active, consumed, expired, revoked)
Optional context for two-factor validation

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`token`	TEXT	Hashed token (unique)
`type`	TEXT	'email', 'phone', 'username'
`identifier_value`	TEXT	The actual identifier value
`token_type`	TEXT	'magic_link', 'password_reset', etc.
`user_id`	TEXT	NULL for guest tokens
`attempts_remaining`	INTEGER	Remaining attempts (default: 1)
`status`	TEXT	'active', 'consumed', 'expired', 'revoked'
`expires_at`	TEXT	Expiration timestamp
`consumed_at`	TEXT	When token was consumed
`context`	TEXT	Optional context (e.g., username change)
`metadata`	TEXT	JSON string

Indexes:

Token lookup (token, status, expires_at)
Identifier lookup (type, identifier_value, status)
User tokens (user_id, token_type, status)
Expiration cleanup

organizations

Multi-tenant organization support for business accounts.

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`name`	TEXT	Organization name (required)
`owner_user_id`	TEXT	Owner user ID (FK to users)
`business_email`	TEXT	Business email address
`business_phone`	TEXT	Business phone number
`tax_id`	TEXT	Tax identification number
`address`	TEXT	Business address
`country`	TEXT	ISO 2-letter country code
`picture`	TEXT	Organization logo URL

Related Table: organization_users

Links users to organizations with role-based access.

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`organization_id`	TEXT	FK to organizations
`user_id`	TEXT	FK to users
`role`	TEXT	'owner', 'admin', 'billing', 'member'

Payment Tables

payment_providers

Configuration for payment providers (Stripe, PayPal, Authorize.net, etc.).

Important Fields:

Field	Type	Description
`id`	TEXT	Provider ID ('stripe', 'paypal', etc.)
`display_name`	TEXT	Human-readable name
`description`	TEXT	Provider description
`picture`	TEXT	Provider logo URL
`is_active`	INTEGER	Active status (0/1)
`supports_subscriptions`	INTEGER	Subscription support (0/1)
`supports_saved_methods`	INTEGER	Saved payment methods support (0/1)
`config`	TEXT	JSON configuration

external_entities

Unified entity management for customers across providers with hierarchical relationships.

Key Features:

Supports both registered users and external guests
Context-based classification (payment, newsletter, events, etc.)
Payment provider customer ID mapping
Hierarchical entity relationships

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`user_id`	TEXT	FK to users (NULL for guests)
`organization_id`	TEXT	FK to organizations
`context_type`	TEXT	'payment', 'newsletter', 'events', etc.
`context_id`	TEXT	Specific context identifier
`payment_provider_id`	TEXT	FK to payment_providers
`payment_provider_customer_id`	TEXT	External provider customer ID
`provider_entity_id`	TEXT	Reference to another entity (hierarchy)
`is_external`	INTEGER	1 for external, 0 for registered
`external_email`	TEXT	Guest email
`external_name`	TEXT	Guest name
`external_phone`	TEXT	Guest phone
`external_alias`	TEXT	Guest alias/nickname
`metadata`	TEXT	JSON string

Unique Constraints:

User + organization + provider + context (for registered users)
Email + organization + provider + context (for guests)

payment_methods

Saved payment methods for users, organizations, and guests.

Key Features:

Support for credit cards, bank accounts, PayPal, wallets (Apple Pay, Google Pay, Samsung Pay)
Guest payment method support
Default payment method tracking
Billing address linking

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`user_id`	TEXT	FK to users
`organization_id`	TEXT	FK to organizations
`provider_id`	TEXT	FK to payment_providers (required)
`provider_payment_method_id`	TEXT	Provider's payment method ID
`customer_id`	TEXT	FK to external_entities
`payment_type`	TEXT	'credit_card', 'bank_account', 'paypal', 'wallet'
`wallet_type`	TEXT	'apple_pay', 'google_pay', 'samsung_pay'
`last_four`	TEXT	Last 4 digits
`expiry_month`	TEXT	Card expiration month
`expiry_year`	TEXT	Card expiration year
`card_brand`	TEXT	'visa', 'mastercard', etc.
`is_default`	INTEGER	Default payment method (0/1)
`billing_address_id`	TEXT	FK to addresses
`alias`	TEXT	User-friendly name
`is_guest`	INTEGER	Guest payment method (0/1)
`guest_email`	TEXT	Guest email
`guest_name`	TEXT	Guest name
`metadata`	TEXT	JSON string

addresses

Billing and shipping addresses for users, organizations, and guests.

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`user_id`	TEXT	FK to users
`organization_id`	TEXT	FK to organizations
`address_type`	TEXT	'billing', 'shipping', 'both'
`is_default`	INTEGER	Default address (0/1)
`name`	TEXT	Recipient name
`line1`	TEXT	Address line 1 (required)
`line2`	TEXT	Address line 2
`city`	TEXT	City (required)
`state`	TEXT	State/Province
`postal_code`	TEXT	Postal code (required)
`country`	TEXT	ISO 2-letter country code (required)
`phone`	TEXT	Contact phone
`email`	TEXT	Contact email
`alias`	TEXT	User-friendly name (e.g., "Home", "Office")
`is_guest`	INTEGER	Guest address (0/1)
`guest_email`	TEXT	Guest email
`guest_name`	TEXT	Guest name
`metadata`	TEXT	JSON string

payments

The core payments table with unified pricing system.

Key Features:

Unified pricing: total_cents = subtotal_cents + tax_cents - discount_cents
Support for order payments, subscription payments, and direct payments (donations)
Guest payment support
Manual/legacy payment support
Coupon tracking
Multiple payment statuses

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`order_id`	TEXT	FK to orders (optional)
`subscription_id`	TEXT	FK to subscriptions (optional)
`user_id`	TEXT	FK to users (optional for guests)
`organization_id`	TEXT	FK to organizations
`payment_method_id`	TEXT	FK to payment_methods
`provider_id`	TEXT	FK to payment_providers
`provider_payment_id`	TEXT	Final payment ID from provider
`provider_intent_id`	TEXT	Intent ID (e.g., Stripe payment intent)
`client_secret`	TEXT	Client secret for frontend confirmation
Pricing Fields
`subtotal_cents`	INTEGER	Base amount before taxes/discounts (required)
`tax_cents`	INTEGER	Applied taxes (default: 0)
`discount_cents`	INTEGER	Applied discounts (default: 0)
`total_cents`	INTEGER	Final amount (required)
`currency`	TEXT	Currency code (default: 'USD')
Status & Tracking
`status`	TEXT	'pending', 'requires_confirmation', 'requires_action', 'processing', 'succeeded', 'failed', 'refunded'
`description`	TEXT	Payment description
`error_message`	TEXT	Error message if failed
`concept`	TEXT	Human-readable concept
`reference_code`	TEXT	Machine-readable code for analytics
`category`	TEXT	High-level category
`tags`	TEXT	Comma-separated tags
Manual Payments
`is_manual_payment`	INTEGER	Manual/legacy payment flag (0/1)
`manual_payment_method`	TEXT	'cash', 'check', 'bank_transfer', 'legacy_system'
`manual_payment_reference`	TEXT	Reference for manual payment
`manual_payment_date`	TEXT	Actual date of manual payment
Guest Support
`is_guest_payment`	INTEGER	Guest payment flag (0/1)
`guest_data`	TEXT	JSON string with guest info
`guest_email`	TEXT	Guest email (indexed)
Coupons
`applied_coupons`	TEXT	JSON string of applied coupons
`metadata`	TEXT	JSON string
`completed_at`	TEXT	Completion timestamp

Pricing Validation:

sql

CHECK (total_cents = subtotal_cents + tax_cents - discount_cents)

Business Rules:

Must belong to a user, organization, or be a guest payment
Can be linked to an order, subscription, or be standalone (donations)
Manual payments don't require provider_id

Product & Order Tables

products

Products and subscription plans with base pricing.

Key Features:

Support for physical, digital, service, and subscription products
Product variations (size, color, etc.)
Category hierarchy
Gallery images
Recurring billing configuration

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`name`	TEXT	Product name (required)
`description`	TEXT	Product description
`product_type`	TEXT	'physical', 'digital', 'service', 'subscription'
`is_recurring`	INTEGER	Recurring product flag (0/1)
`subtotal_cents`	INTEGER	Base price before tax (required)
`currency`	TEXT	Currency code (default: 'USD')
`billing_interval`	TEXT	'monthly', 'yearly', null for one-time
`trial_days`	INTEGER	Trial period days (default: 0)
`image`	TEXT	Main product image URL
`gallery`	TEXT	JSON array of additional images
`category_id`	TEXT	FK to product_categories
`parent_product_id`	TEXT	FK to products (for variations)
`variations`	TEXT	JSON array of variation options
`metadata`	TEXT	JSON string
`is_active`	INTEGER	Active status (0/1)

Related Table: product_categories

Hierarchical product categories with parent-child relationships.

orders

Customer orders with unified pricing and guest support.

Key Features:

Anonymous guest order support
Unified pricing system
Billing and shipping addresses (JSON)
Order status tracking

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`order_number`	TEXT	Human-readable order number (unique)
`user_id`	TEXT	FK to users
`organization_id`	TEXT	FK to organizations
`customer_id`	TEXT	FK to external_entities
`is_guest_order`	INTEGER	Anonymous guest order flag (0/1)
`guest_data`	TEXT	JSON string with guest info
`guest_email`	TEXT	Guest email (indexed)
`status`	TEXT	'pending', 'paid', 'cancelled', 'refunded'
`subtotal_cents`	INTEGER	Base amount (required)
`tax_cents`	INTEGER	Applied taxes (default: 0)
`discount_cents`	INTEGER	Applied discounts (default: 0)
`total_cents`	INTEGER	Final amount (required)
`currency`	TEXT	Currency code (default: 'USD')
`billing_address`	TEXT	JSON string
`shipping_address`	TEXT	JSON string
`metadata`	TEXT	JSON string
`completed_at`	TEXT	Completion timestamp

Related Table: order_items

Individual items within an order with quantity and pricing.

subscriptions

Recurring subscriptions with automatic billing and guest support.

Key Features:

Automatic billing with retry logic
Unified pricing system
Trial period support
Flexible billing intervals (daily, weekly, monthly, yearly)
Guest subscription support
Enhanced tracking (description, concept, reference_code, category, tags)

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`user_id`	TEXT	FK to users
`organization_id`	TEXT	FK to organizations
`customer_id`	TEXT	FK to external_entities (required)
`product_id`	TEXT	FK to products (optional for custom)
`payment_method_id`	TEXT	FK to payment_methods
`provider_id`	TEXT	FK to payment_providers (required)
`provider_subscription_id`	TEXT	Provider's subscription ID
`status`	TEXT	'active', 'cancelled', 'past_due', 'trialing', 'incomplete', 'incomplete_expired'
`current_period_start`	TEXT	Current billing period start
`current_period_end`	TEXT	Current billing period end
`cancel_at_period_end`	INTEGER	Cancel at period end flag (0/1)
`trial_end`	TEXT	Trial end date
Pricing Fields
`subtotal_cents`	INTEGER	Base subscription price (required)
`tax_cents`	INTEGER	Applied taxes (default: 0)
`discount_cents`	INTEGER	Applied discounts (default: 0)
`total_cents`	INTEGER	Final price (required)
`currency`	TEXT	Currency code (default: 'USD')
Billing Automation
`billing_interval`	TEXT	'daily', 'weekly', 'monthly', 'yearly'
`interval_multiplier`	INTEGER	Multiplier (e.g., 2 for every 2 months)
`next_billing_date`	TEXT	Next billing date (ISO 8601)
`last_billing_attempt`	TEXT	Last billing attempt (ISO 8601)
`billing_retry_count`	INTEGER	Failed billing attempts (default: 0)
`max_retry_attempts`	INTEGER	Max retry attempts (default: 3)
`billing_status`	TEXT	'active', 'past_due', 'suspended', 'cancelled'
Tracking Fields
`description`	TEXT	Human-readable description
`concept`	TEXT	Human-readable concept
`reference_code`	TEXT	Machine-readable code
`category`	TEXT	High-level category
`tags`	TEXT	Comma-separated tags
Guest Support
`is_guest_subscription`	INTEGER	Guest subscription flag (0/1)
`guest_data`	TEXT	JSON string with guest info
`guest_email`	TEXT	Guest email (indexed)
`metadata`	TEXT	JSON string

Pricing Validation:

sql

CHECK (total_cents = subtotal_cents + tax_cents - discount_cents)

Billing Interval Validation:

sql

CHECK (billing_interval IN ('daily', 'weekly', 'monthly', 'yearly'))
CHECK (interval_multiplier IS NULL OR (interval_multiplier > 0 AND interval_multiplier <= 12))

invoices

Invoices for orders and subscriptions with payment link support.

Key Features:

Unified pricing system
Payment link generation
Guest invoice support
Coupon tracking
Multiple invoice statuses

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`invoice_number`	TEXT	Human-readable invoice number (unique)
`order_id`	TEXT	FK to orders
`subscription_id`	TEXT	FK to subscriptions
`payment_id`	TEXT	FK to payments (updated after payment)
`user_id`	TEXT	FK to users
`organization_id`	TEXT	FK to organizations
`customer_id`	TEXT	FK to external_entities
`status`	TEXT	'draft', 'open', 'paid', 'void', 'uncollectible'
`subtotal_cents`	INTEGER	Base amount (required)
`tax_cents`	INTEGER	Applied taxes (default: 0)
`discount_cents`	INTEGER	Applied discounts (default: 0)
`total_cents`	INTEGER	Final amount (required)
`currency`	TEXT	Currency code (default: 'USD')
`issue_date`	TEXT	Invoice issue date (required)
`due_date`	TEXT	Payment due date (required)
`paid_date`	TEXT	Payment completion date
`is_guest_invoice`	INTEGER	Guest invoice flag (0/1)
`guest_data`	TEXT	JSON string with guest info
`guest_email`	TEXT	Guest email (indexed)
`reference_code`	TEXT	Reference code for payments
`payment_link_url`	TEXT	Unique payment URL
`payment_link_expires_at`	TEXT	Payment link expiration
`payment_method_id`	TEXT	FK to payment_methods (after payment)
`billing_address`	TEXT	JSON string
`provider_id`	TEXT	FK to payment_providers
`provider_invoice_id`	TEXT	Provider's invoice ID
`invoice_url`	TEXT	Friendly invoice URL
`applied_coupons`	TEXT	JSON string of applied coupons
`metadata`	TEXT	JSON string

Advanced Features

discount_coupons

Flexible discount coupon system with advanced features.

Key Features:

Multiple discount types (percentage, fixed_amount, free_shipping, buy_x_get_y)
Usage limits (total and per customer)
Validity dates
Product/category restrictions
User type restrictions
Stackable coupons
Auto-apply support
Campaign tracking

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`code`	TEXT	Unique coupon code (e.g., "SUMMER2024")
`name`	TEXT	Friendly name (required)
`description`	TEXT	Detailed description
`discount_type`	TEXT	'percentage', 'fixed_amount', 'free_shipping', 'buy_x_get_y'
`discount_value`	REAL	Discount value (required)
`currency`	TEXT	Currency for fixed amounts (default: 'USD')
`minimum_amount_cents`	INTEGER	Minimum amount to apply (default: 0)
`maximum_discount_cents`	INTEGER	Maximum discount cap
`usage_limit`	INTEGER	Total usage limit (NULL = unlimited)
`usage_limit_per_customer`	INTEGER	Per customer limit (NULL = unlimited)
`current_usage_count`	INTEGER	Current usage counter (default: 0)
`starts_at`	TEXT	Start date (NULL = immediate)
`expires_at`	TEXT	Expiration date (NULL = no expiration)
`applicable_to`	TEXT	'all', 'products', 'categories', 'subscriptions'
`applicable_product_ids`	TEXT	JSON string of product IDs
`applicable_category_ids`	TEXT	JSON string of category IDs
`excluded_product_ids`	TEXT	JSON string of excluded products
`applicable_user_types`	TEXT	JSON string of user types
`applicable_customer_segments`	TEXT	JSON string of segments
`first_time_customers_only`	INTEGER	New customers only flag (0/1)
`is_active`	INTEGER	Active status (0/1)
`is_stackable`	INTEGER	Can combine with other coupons (0/1)
`auto_apply`	INTEGER	Auto-apply if conditions met (0/1)
`campaign_id`	TEXT	Marketing campaign ID
`source`	TEXT	Coupon source ('email', 'social', 'affiliate')
`tags`	TEXT	Comma-separated tags
`buy_x_get_y_config`	TEXT	JSON config for buy X get Y offers
`tier_discounts`	TEXT	JSON config for tiered discounts
`metadata`	TEXT	JSON string
`created_by`	TEXT	User who created the coupon

Related Table: coupon_usage

Tracks every coupon usage with detailed information.

tax_rates

Optional dynamic tax calculation system.

Key Features:

Geographic-based tax rates (country, state, city, postal code)
Product/category-based applicability
Priority-based conflict resolution
Effective date ranges
Multiple tax types (percentage, fixed_amount)

Important Fields:

Field	Type	Description
`id`	TEXT	Primary key (UUID)
`name`	TEXT	Tax name (e.g., "Sales Tax", "IVA", "GST", "VAT")
`description`	TEXT	Detailed description
`rate`	REAL	Tax rate (0.0360 = 3.6%)
`type`	TEXT	'percentage', 'fixed_amount'
`country`	TEXT	ISO 2-letter country code
`state_province`	TEXT	State/Province code
`city`	TEXT	City name
`postal_code`	TEXT	Specific postal codes
`applicable_categories`	TEXT	JSON string of category IDs
`applicable_product_types`	TEXT	JSON string of product types
`excluded_categories`	TEXT	JSON string of excluded categories
`excluded_product_types`	TEXT	JSON string of excluded types
`is_active`	INTEGER	Active status (0/1)
`priority`	INTEGER	Priority (higher wins in conflicts)
`effective_from`	TEXT	Effective start date
`effective_until`	TEXT	Effective end date
`metadata`	TEXT	JSON string
`created_by`	TEXT	User who created this rate

Analytics Tables

Optional analytics and reporting tables.

analytics_snapshots

Daily revenue snapshots
Active subscription counts
Metric tracking with breakdown
Calculation performance tracking

analytics_events

Detailed event tracking
Revenue attribution
Conversion tracking
User session tracking

user_cohorts

Cohort analysis support
First purchase tracking
First subscription tracking
Monthly cohort grouping

Indexes & Performance

The Native Payments schema includes comprehensive indexing for optimal performance:

User Indexes

Functional: email, username, phone (active users only)
Soft Delete: active/deleted status
Authentication: email + verification status
Security: locked accounts, 2FA enabled
Temporal: created_at, updated_at
Profile: mobile, recovery_email, display_name, gender, dob, timezone
Composite: user_type + is_verified, email + user_type

Payment Indexes

Guest Payments: guest_email + is_guest_payment + created_at
Status: payment status, subscription status
Provider: provider_intent_id
Tracking: reference_code, category, concept
Temporal: created_at for analytics

Subscription Indexes

Billing: next_billing_date + billing_status
Retry Logic: last_billing_attempt + billing_retry_count
Tracking: reference_code, category, concept
Guest: guest_email + is_guest_subscription

Coupon Indexes

Lookup: coupon code
Status: is_active, expires_at
Campaign: campaign_id
Usage: current_usage_count + usage_limit
Analytics: discount_amount_cents + currency

Performance Features

Partial Indexes - Indexes only on active/relevant records
Composite Indexes - Multi-column indexes for common query patterns
Conditional Indexes - WHERE clauses to reduce index size
Automatic Timestamps - Triggers for updated_at fields
Foreign Key Constraints - Data integrity with CASCADE/SET NULL

Unified Pricing System

All monetary entities (payments, orders, subscriptions, invoices) use the same pricing model:

total_cents = subtotal_cents + tax_cents - discount_cents

Benefits:

✅ Consistent pricing across all entities
✅ Database-level validation with CHECK constraints
✅ Easy to understand and maintain
✅ Supports complex pricing scenarios
✅ Audit-friendly with clear breakdown

Example:

json

{
  "subtotal_cents": 10000,  // $100.00
  "tax_cents": 850,          // $8.50 (8.5% tax)
  "discount_cents": 1000,    // $10.00 (coupon)
  "total_cents": 9850        // $98.50 final price
}

Guest Support

The schema provides comprehensive guest support across all entities:

Guest Checkout Flow:

Create guest address (is_guest = 1)
Create guest payment method (is_guest = 1)
Create guest payment (is_guest_payment = 1)
Optional: Convert guest to registered user

Guest Fields:

is_guest / is_guest_* - Boolean flag
guest_email - Indexed for quick lookup
guest_name - Guest name
guest_data - JSON with full guest information

Guest Conversion:

Link guest entities to new user account
Preserve payment history
Maintain address and payment method associations

Multi-Tenant Support

Organizations enable multi-tenant functionality:

Features:

Organization ownership (owner_user_id)
Role-based access (owner, admin, billing, member)
Organization-scoped payments and subscriptions
Shared payment methods and addresses
Business information (tax_id, business_email, business_phone)

Use Cases:

SaaS platforms with team accounts
Marketplace platforms
B2B payment processing
Enterprise billing

Best Practices

Data Retention

Use soft deletes for users (deleted_at, deletion_reason)
Keep payment history for compliance
Archive old analytics data periodically

Security

Hash all tokens before storage
Use HTTPS for all API communication
Implement rate limiting on token endpoints
Validate all pricing calculations

Performance

Use indexes for all foreign keys
Implement pagination for large result sets
Cache frequently accessed data
Monitor slow queries and add indexes as needed

Compliance

GDPR: Support data export and deletion
PCI DSS: Never store full card numbers
Tax compliance: Use tax_rates table for accurate calculations
Audit trails: Use payment_events for tracking

Next Steps

API Reference - Explore the REST API endpoints
Guest Checkout Guide - Implement guest checkout
Subscriptions Guide - Set up recurring billing
Organizations Guide - Multi-tenant setup
Performance Optimization - Scale your implementation

Database Schema ​

📚 Quick Navigation ​

Overview ​

Core Tables ​

users ​

tokens ​

organizations ​

Payment Tables ​

payment_providers ​

external_entities ​

payment_methods ​

addresses ​

payments ​

Product & Order Tables ​

products ​

orders ​

subscriptions ​

invoices ​

Advanced Features ​

discount_coupons ​

tax_rates ​

Analytics Tables ​

Indexes & Performance ​

User Indexes ​

Payment Indexes ​

Subscription Indexes ​

Coupon Indexes ​

Performance Features ​

Unified Pricing System ​

Guest Support ​

Multi-Tenant Support ​

Best Practices ​

Data Retention ​

Security ​

Performance ​

Compliance ​

Next Steps ​

Database Schema

📚 Quick Navigation

Overview

Core Tables

users

tokens

organizations

Payment Tables

payment_providers

external_entities

payment_methods

addresses

payments

Product & Order Tables

products

orders

subscriptions

invoices

Advanced Features

discount_coupons

tax_rates

Analytics Tables

Indexes & Performance

User Indexes

Payment Indexes

Subscription Indexes

Coupon Indexes

Performance Features

Unified Pricing System

Guest Support

Multi-Tenant Support

Best Practices

Data Retention

Security

Performance

Compliance

Next Steps