PeltyCMS Developer docs/Platform reference
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. |