# Database migrations

104 migrations in `migrations/`, applied in order by `pelty-server migrate` (and on `serve`). The summary is the file's leading comment.

| File | Summary |
|---|---|
| `0001_init.sql` | PeltyCMS initial schema: identity, sessions, RBAC, audit, settings. |
| `0002_extensions.sql` | Plugins, payment gateways, webhook deduplication, minimal payments ledger. |
| `0003_perf_indexes.sql` | Match the admin user list ordering exactly (created_at DESC, id) so the page is an index scan with no sort. |
| `0004_billing.sql` | Milestone 2: billing core. All money is NUMERIC(19,4) + ISO currency; the currency's exponent decides rounding. Never floats. |
| `0005_dashboard_indexes.sql` | Partial indexes backing the dashboard aggregates. |
| `0006_clear_dummy_bank_text.sql` | 0002 seeded a placeholder IBAN into the manual gateway. Bank details now live in system_settings (billing.bank_transfer); drop the placeholder only if the operator never changed it. |
| `0007_email.sql` | Email: operator-editable templates, password resets, email verification. |
| `0008_mfa.sql` | TOTP two-factor auth. Secrets are AES-256-GCM sealed by the app (PELTY_ENCRYPTION_KEY); backup codes and challenge tokens are SHA-256 only. |
| `0009_avatars.sql` | Profile pictures, re-encoded server-side to PNG (256px and 64px squares). |
| `0010_tickets.sql` | Support tickets. |
| `0011_ticket_attachments.sql` | Ticket attachments. Uploaded first (pending, owned by the uploader), then linked to a message when the reply/ticket is posted. Bytes live in the DB so there is no filesystem path handling; served only as downloads. |
| `0030_addons_custom_fields.sql` | Product addons (sold alongside a product) and custom order fields. |
| `0040_coupons.sql` | Coupons / promo codes. Discounts are applied as a negative invoice line and, for recurring coupons, persisted on the service's recurring amount. |
| `0050_affiliates.sql` | Affiliates: referral codes, attribution by tracking cookie at signup, commissions accrued (exact decimals) when a referred customer's invoice is paid, and payout requests against the available balance. |
| `0051_quotes.sql` | Quotes / proposals. Accepting a sent, unexpired quote turns it into a finalized invoice exactly once (quote row locked, invoice_id set). |
| `0052_downloads.sql` | Downloads area: files stored in the database (no filesystem paths), gated by owning an active service of one of the linked products. No linked products = available to every signed-in customer. |
| `0090_content.sql` | Knowledge base and announcements. Content is per-locale; `locale` NULL means "all languages". Only published rows are visible publicly. |
| `0091_client_groups.sql` | Client groups (WHMCS "Client Groups"): label colour, a percentage discount applied by pricing code that opts in, and an optional "exempt from late fees" flag. |
| `0092_api_keys.sql` | Personal API keys. Only the SHA-256 of the secret is stored; `prefix` is the public, non-secret part shown in the UI. A key's effective permissions are its scopes intersected with its owner's current permissions. |
| `0095_import_map.sql` | Idempotent importer bookkeeping: one row per imported source record. |
| `0096_tax.sql` | Tax engine: rules per country/region, inclusive/exclusive pricing (global setting `billing.tax`), compound taxes, EU VAT reverse charge. |
| `0100_provisioning.sql` | Provisioning: panel servers, product → server binding, per-service remote state. |
| `0101_api_keys_admin.sql` | Admins can list and revoke any user's API keys. |
| `0102_reports.sql` | Financial reports (revenue, MRR, churn). Admin and staff can read. |
| `0103_gdpr.sql` | GDPR: data-subject erasure requests (export is on demand, nothing stored). |
| `0104_fraud.sql` | Fraud screening results for client orders (built-in rules + plugin hooks). |
| `0110_service_changes.sql` | Plan changes (upgrade/downgrade with proration) and cancellation requests. |
| `0120_support_suite.sql` | Support suite: canned replies, tags, ratings, SLA first response, merge, escalation rules and inbound e-mail piping. |
| `0121_overdue.sql` | Overdue automation: one row per (invoice, action) so reminders, late fees and suspensions happen exactly once even if the daily job runs twice. |
| `0122_credit.sql` | Customer credit balance as an append-only ledger (per currency). The balance is the sum of entries; it never goes below zero (checked under a row lock on the customer). |
| `0130_domains.sql` | Domains: TLD price list, registered/transferred domains and renewals. |
| `0160_config_option_types.sql` | Configurable option types (WHMCS: dropdown, radio, quantity, yes/no). quantity: the single choice's price is per unit; yesno: the single choice is "yes" (absent = no). |
| `0161_network_issues.sql` | Network / service status issues (WHMCS "Network Issues"), public page + admin. |
| `0162_client_notes_fields_todos.sql` | Staff notes on clients (multiple, sticky), custom client fields and the admin to-do list. |
| `0163_billable_items.sql` | Per-product invoice generation lead time (NULL = global default) and billable items: one-off or recurring charges billed to a client. |
| `0170_plugin_webhooks.sql` | Durable, HMAC-signed event delivery to external plugin webhook URLs. |
| `0171_usage_billing.sql` | Metered / usage billing: per-product metrics, raw usage records grouped into per-service periods that are invoiced when the period (cycle) ends. |
| `0180_login_lockout.sql` | Per-account login backoff: after LOCK_AFTER consecutive failures the account is locked for an exponentially growing window (capped). |
| `0181_refunds.sql` | Refunds against individual payments (partial or full), executed through the payment gateway. Each successful refund issues a credit note. |
| `0185_currency_rates.sql` | Exchange rates (relative to the default currency, which has rate 1), display prefix/suffix and a single default currency. |
| `0186_staff_roles.sql` | Staff role editor: builtin roles cannot be deleted/renamed; admin keeps all permissions. |
| `0187_mail_campaigns.sql` | Mass mail campaigns to client segments, sent by the job queue. |
| `0190_sso_tokens.sql` | One-time client SSO tokens. The token itself is signed (HMAC under the server API key) and never stored; only its nonce, to burn it on use. |
| `0191_bundles.sql` | Product bundles (fixed set of products at a cycle, % off the first invoice) and per-product upsell / cross-sell recommendations. |
| `0192_ticket_statuses.sql` | Custom ticket statuses. Built-in ones keep their workflow meaning (replies move tickets between open/answered/customer_reply/closed); custom ones are set manually by staff. |
| `0193_ticket_rated_at.sql` | When a customer rated a ticket (satisfaction report period filter). |
| `0195_gateway_deliveries.sql` | Webhook delivery log per gateway (admin troubleshooting). Bodies are not stored (may contain personal data); only outcome metadata. |
| `0200_storefront.sql` | Public storefront carts (keyed by an unguessable client token), customer contacts / sub-accounts, email preferences and admin order notes. |
| `0201_autopay.sql` | Saved payment methods (provider tokens only, never card data), auto-pay opt-in per customer and the off-session charge attempts with retries. |
| `0202_invoice_tools_order_approval.sql` | Pre-due invoice reminders (one mail per invoice and threshold). |
| `0210_legal_entities.sql` | Legal entities (selling companies): per-entity bank details, footer/terms, due-date terms and per-year gapless numbering. Without any entity the legacy global INV-/CN- sequence is used unchanged. |
| `0211_gateway_rules.sql` | Per-gateway processing fees, amount limits and currency restrictions. |
| `0220_addons.sql` | Addon registry: provisioning modules, payment gateways, themes and plugins must be installed (and enabled) before they can run. See docs/ADDONS.md. |
| `0221_site_pages.sql` | Public website CMS pages built from declarative blocks (docs/DESIGN_LAGOM_STYLE.md). |
| `0222_site_legal_pages.sql` | Default pages linked from the public footer (Company / Legal). Admins edit them in Website > Pages. |
| `0240_admin_tools.sql` | Admin productivity: personal "My Notes" and outbound staff notification rules (Slack / Discord / Teams / generic webhook), WHMCS "Configure Notifications". |
| `0247_billing_wave2.sql` | Billing wave 2: full ISO 4217 currency list, currency display format, automatic exchange-rate updates (configurable source + markup), invoice merge/split bookkeeping and gift cards / prepaid credit codes. |
| `0261_admin_ops.sql` | Admin ops: per-staff dashboard/quick-link prefs, DB backups, custom code permission. |
| `0262_campaign_schedule.sql` | Mass mail scheduling: a campaign can wait in 'scheduled' until scheduled_at. |
| `0263_affiliate_extras.sql` | Affiliate extras (WHMCS parity): per-product commission overrides, referral count tiers and promotional materials. Product-rated invoice lines replace their share of the base commission. |
| `0265_registrar_addons_domains_ext.sql` | Registrars become addons (kind 'registrar'); domain pricing gets grace / redemption fees and add-on prices; DNS records and domain lifecycle states. |
| `0266_support_ext.sql` | Support extensions (WHMCS parity): department auto-reply and mailbox piping (IMAP/POP3), spam control, canned categories, time tracking, KB tags and contact form messages. |
| `0270_security_suite.sql` | Security / accounts suite: captcha nonces, social login (OAuth2/OIDC), IP rules, admin impersonation, WHMCS-compatible API credentials, versioned terms of service, client close/merge. |
| `0271_refund_to_credit.sql` | Refund a gateway payment to the customer's account credit instead of back through the gateway (WHMCS "Add to client's credit balance"). |
| `0272_add_funds.sql` | Add funds: the client pays an invoice and the amount lands in their credit balance exactly once (trigger on the paid transition, same transaction). |
| `0273_client_group_rules.sql` | Automatic client-group assignment rules. Evaluated in priority order; the first matching rule decides the group. Applied on demand by an admin (dry-run first) and only to customers without a group unless overwrite. |
| `0275_ticket_watchers.sql` | Extra staff who follow a ticket and get its notifications. |
| `0276_promos_free_domain_order_forms.sql` | Promotions: addons/domains scope, recurring for N cycles, per-client/group. |
| `0277_subaccount_perms.sql` | Sub-account permissions gain "orders" and "domains" (WHMCS Users & Accounts). |
| `0278_notify_channels_webhooks.sql` | Telegram, SMS (HTTP gateway or email-to-SMS) notification channels, HMAC signed generic webhooks with a per-attempt delivery log. |
| `0279_server_groups.sql` | Server groups: products bound to a group get a server picked automatically (per module, enabled, under max_services) by the group's strategy. |
| `0280_product_stock.sql` | Product stock control: optional quantity limit with optional backorders. |
| `0281_module_checkout_fields.sql` | Checkout fields declared by a product's provisioning module are synced into product_custom_fields and tagged with the module's field key. |
| `0285_oauth_provider.sql` | PeltyCMS as an OAuth 2.0 / OpenID Connect provider. |
| `0286_live_chat.sql` | Simple live chat: anonymous visitor sessions (bearer token, hashed) and staff replies, polled by both sides. |
| `0287_debit_notes.sql` | Debit notes: an extra charge issued against an existing invoice (the counterpart of a credit note). Linked via credit_for (= "related invoice"). |
| `0288_module_log_health.sql` | Server health (last connection test) and a log of every module call. |
| `0289_saml.sql` | SAML 2.0 Service Provider: identity providers, pending AuthnRequests, and the assertion replay cache. |
| `0290_addon_kv.sql` | Addon-private key/value store for WASM addons (host `kv-*` imports). Values are sealed with the app encryption key when one is configured. |
| `0291_admin_security.sql` | Admin account security: step-up re-authentication, password age, login alerts. |
| `0292_fk_indexes.sql` | Indexes on foreign keys that are filtered by or checked on delete (ON DELETE RESTRICT / CASCADE scans) on the customer, invoice, order, ticket and session paths. Found with a pg_constraint/pg_index scan; see docs/PERFORMANCE.md. |
| `0293_email_log.sql` | WHMCS "Email Message Log": every transactional mail the worker delivered (or failed to), viewable per client and resendable by staff. |
| `0294_card_expiry_banned_emails.sql` | WHMCS "Credit Card Expiry Notices": one notice per saved card. |
| `0295_fixed_term.sql` | Fixed-term products: terminate a service N days after activation (WHMCS "Auto Terminate"). |
| `0296_ssl_checks.sql` | SSL certificate monitoring for client domains (WHMCS "SSL Sync" / SSL status in client area). |
| `0297_ticket_message_edit.sql` | Staff can edit or delete ticket replies (WHMCS UpdateTicketReply / DeleteTicketReply). |
| `0298_proforma_invoices.sql` | Proforma invoices: an issued invoice may stay unnumbered until it is paid. |
| `0299_staff_noticeboard.sql` | Staff noticeboard (WHMCS "Staff Noticeboard" addon): short notices shown to all staff. |
| `0300_security_questions.sql` | WHMCS security questions: staff-defined questions; each user may store one hashed answer, which staff can check when verifying a caller by phone. |
| `0301_link_tracking.sql` | WHMCS-style link tracking: short redirect links that count clicks. |
| `0302_payment_reversals.sql` | Payment reversals (chargebacks): money already taken back by the bank or gateway. Settled like a refund (credit note, payment/invoice status) but without calling the gateway. |
| `0303_inactive_clients.sql` | Last login, kept even after sessions are purged; used by the inactive-client retention job. |
| `0304_projects.sql` | Project management (WHMCS Project Management addon): projects linked to a client, tickets and invoices, with tasks and time tracking billed as invoice lines. |
| `0305_domain_pricing_extras.sql` | Registrar cost sync + margin rules, premium domains, per-TLD custom fields. |
| `0306_overage_import.sql` | Overage billing from server-module usage + server account import. |
| `0307_kyc.sql` | KYC: identity documents uploaded by customers, reviewed by staff. |
| `0308_api_gaps.sql` | WHMCS API gaps: UpdateTransaction (staff note on a payment). |
| `0309_vendor_catalog.sql` | MarketConnect-style vendor resale: catalog synced from a vendor server. |
| `0310_passkeys.sql` | Passkeys (WebAuthn). The credential is the serialized webauthn-rs Passkey (public key + counter only; no secret material). |
| `0311_passkey_discoverable.sql` | Usernameless (discoverable credential) passkey sign-in ceremonies. |
| `0312_tld_field_key_case.sql` | Registrar parameter names are case-sensitive (RegistrantNexus, X-CA-LEGALTYPE, contactRegistrant.nexusCategory): keep the key exactly as typed, but two keys differing only in case are still one field. |
| `0313_registrant_verification.sql` | ICANN Registrar Accreditation Agreement (WHOIS accuracy program): the registrant's e-mail must be verified within 15 days of a registration, transfer or registrant e-mail change. One row per domain (latest request). |
| `0314_translation_overrides.sql` | Translation editor: admin overrides on top of the shipped locale files. |
| `0315_duo.sql` | Duo Universal Prompt: OIDC state between redirect and callback. |