# MHI Gateway Database Design

## Milestone 5C-6 — orchestration database boundary

Inbound processing orchestration introduces no schema, table, index, constraint, encryption, or repository change. It uses only the 5C-5 claim, authorized read/decryption, `PROCESSED`, and `QUARANTINED` operations. Each orchestration call performs at most one terminal repository mutation; repository-owned transactions, database-clock expiry, ownership fencing, terminal idempotency, encrypted payload storage, and persist-before-acknowledgement ordering remain authoritative and unchanged.

Terminal rows are only protocol-processing lifecycle evidence. They contain no tenant, application, MO message, DLR projection, provider-message correlation, callback, webhook, queue, pricing, wallet, or billing data. Any consumption of terminal outcomes requires a separately approved next-phase use case.

## Milestone 5C-5 — SMPP inbound-envelope lifecycle

The existing `smpp_inbound_envelopes` InnoDB table is extended in place; no second table or repository exists. Nullable `claim_owner`, `claimed_at`, and `claim_expires_at` columns form one all-or-none ownership fence. `claim_owner` is an opaque UUID retained on terminal rows. `terminal_at` is null only while the row remains `available`. Valid statuses are `available`, `processed`, and `quarantined`; terminal rows require both a terminal timestamp and retained owner.

`(status, claim_expires_at, available_at, id)` supports deterministic selection of the oldest eligible row. Claims use an InnoDB locking read and database-authoritative `UTC_TIMESTAMP(6)`. An unclaimed row or a claim whose expiry is less than or equal to the observed time is eligible; an expiry later than that time is not. Authorized reads and transitions lock the identity row and require its matching, unexpired owner. Same-owner, same-terminal-state repetition is idempotent and preserves the first `terminal_at`; conflicting terminal changes fail without mutation.

The existing encrypted `payload`, transport identity, provenance, `available_at`, and `created_at` values are never rewritten by lifecycle operations. Recording still begins at `available` and still commits before acknowledgement. Lifecycle operations reject ambient transactions so callers cannot defer their commit or partially combine them with unrelated work. There is no tenant, MO, DLR, business projection, orchestration, queue, callback, retry, purge, or retention behavior.

## Corrective Milestone 5B-7 — SMPP inbound envelopes

At the 5B-7 recording baseline, `smpp_inbound_envelopes` stored a database identity, provider, positive session generation, positive SMPP sequence number, encrypted exact inbound PDU bytes, the initial lifecycle state `available`, and database-authoritative UTC microsecond timestamps. `(provider, session_generation, sequence_number)` remains unique for deterministic record idempotency. Milestone 5C-5 subsequently adds lifecycle claims and terminal states without changing this recording contract.

Sensitive PDU bytes are encrypted before insert. An exact duplicate returns the original row without replacing ciphertext; conflicting bytes under the same transport identity are rejected. Recording commits before a successful `deliver_sm_resp` is permitted. No tenant identity, application identity, MO message, or DLR projection exists in this table.

## Milestone 5B-3 — SMPP submissions

`smpp_submissions` provides one durable handoff record per provider and message. It stores tenant and message ownership, encrypted source address, encrypted destination address, encrypted submission payload, numeric priority, `pending|claimed` status, availability and claim timestamps, and optional claim owner and lease generation. A unique provider/message key makes enqueue idempotent. The composite claim index supports deterministic provider-scoped selection by status, priority, availability, and ID; a claim-owner index supports current-claim observation. MySQL UTC database time supplies enqueue and claim timestamps. Claim transactions lock and validate the matching unexpired `smpp_session_leases` owner and generation before locking submission rows; rejected ownership leaves submissions unchanged. Submission content is never changed during claim.

## Milestone 5B-2 — SMPP session leases

`smpp_session_leases` contains one row per logical provider. The unique provider key identifies the lease; an opaque UUID owner token and positive unsigned generation fence logical ownership. Non-null UTC `DATETIME(6)` acquired, heartbeat, expiry, created, and updated values avoid connection-timezone conversion. MySQL `UTC_TIMESTAMP(6)` is read inside every mutation transaction, and expiry is derived from that database time plus a validated duration. Acquisition, renewal, release, and takeover lock the provider row in a short transaction. Release sets heartbeat and expiry to the release time instead of deleting the row, so the next owner increments the retained generation. The expiry index supports operational inspection. The table contains no SMPP endpoint, System ID, password, message address, body, or PDU.

## Milestone 4 wallet schema

`wallets` uses InnoDB and contains an internal bigint ID, public ULID, tenant ID, uppercase three-character currency, `active|frozen|closed` status, non-negative unsigned bigint available and reserved projections, and timestamps. `(tenant_id, currency)` and `(tenant_id, id)` are unique. Wallets have no version column and no soft deletion.

`wallet_reservations` uses InnoDB and connects one tenant-qualified wallet to one tenant-qualified message. It stores public ULID, currency, positive unsigned bigint amount, `active|captured|released` status, lifecycle timestamps, and timestamps. `(tenant_id, message_id)` permits at most one reservation lifecycle per message; `(tenant_id, id)` supports composite ownership. There is no soft deletion.

`wallet_ledger_transactions` uses InnoDB and stores public ULID, tenant-qualified wallet, optional tenant-qualified reservation, fixed operation type, currency, positive unsigned bigint operation amount, operation-aware HMAC key hash, immutable request fingerprint HMAC, `occurred_at`, and `created_at`. It has no message ID, metadata, raw idempotency key, `updated_at`, or soft deletion. `(wallet_id, operation_type, idempotency_key_hash)` is unique.

`wallet_ledger_entries` uses InnoDB and stores public ULID, tenant-qualified wallet and ledger transaction, a fixed account, a non-zero signed bigint delta, and `created_at`. `(ledger_transaction_id, account)` is unique. No balance-after snapshot is stored. Application accounting invariants require exactly two entries per transaction and a zero delta sum.

All ownership foreign keys are tenant-qualified and restrictive on delete. Migration order is wallets, reservations, ledger transactions, then ledger entries; rollback is the reverse. SQLite validates lifecycle and uniqueness behavior but does not prove MySQL row-lock semantics.

Reconciliation uses `SUM(delta_minor)` for the available and reserved account projections, `-SUM(delta_minor)` for total funding credits, `SUM(delta_minor)` for consumed value, and `SUM(amount_minor)` for active reservations. The projected reserved balance must equal both its ledger-derived value and the active-reservation total. Each transaction must contain exactly two entries, sum to zero, and match its fixed operation-specific account pair. These cross-row invariants are verified by the read-only application reconciliation service because ordinary SQL CHECK constraints cannot enforce them across child rows.

## 1. Design Goals

The database design must support multi-tenancy, provider abstraction, wallet-based billing, auditability, asynchronous delivery and privacy-safe operations. Every transactional table must be scoped to a tenant where appropriate, and the ledger must remain the source of truth for billing state.

## 2. Core Principles

- Use integer columns for money values in minor units.
- Keep tenant data isolated through tenant-scoped foreign keys and scoped queries.
- Separate business state from operational metadata.
- Preserve immutable history for billing, delivery and audit events.
- Use soft-delete timestamps for logical deletion and keep business status separate from deletion state.
- Prevent cascading deletion of messaging, billing and audit history.
- Store API secrets as hashed values, never as plaintext.
- Use encryption for truly sensitive fields such as credentials and signing secrets.
- Use hashing only where the value must be compared, not when the platform must later generate signatures.

## 3. Core Design Model

The platform uses the following ownership hierarchy:

- Tenant: the top-level customer or business account.
- Application: a logical client integration under a tenant.
- API Credential: an authenticated identity used to resolve tenant and application context.

Platform-owned resources may be shared across tenants, while tenant-owned resources must remain scoped to a tenant. Tables that may have nullable tenant_id include platform-owned provider definitions, shared provider connections and generic routing or pricing templates where the resource is not specific to one tenant.

## 4. Core Tables

### tenants
Stores the top-level business entity or client account.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| name | varchar | Tenant display name |
| slug | varchar | Unique identifier |
| status | varchar | active, suspended, trial |
| settings_json | json | Tenant-specific defaults |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### applications
Represents a logical client integration within a tenant.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| code | varchar | Unique application code within tenant |
| name | varchar | Application label |
| status | varchar | active, disabled |
| settings_json | json | Application-specific defaults |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### api_credentials
Authenticated credentials that resolve tenant and application context.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| application_id | bigint | FK to applications |
| name | varchar | Credential label |
| key_prefix | varchar | Prefix used for lookup and display |
| secret_hash | varchar | Hashed secret |
| secret_hint | varchar | Non-sensitive hint |
| type | varchar | bearer, api_key, oauth |
| status | varchar | active, revoked, expired |
| expires_at | datetime | Optional expiration |
| last_used_at | datetime | Audit metadata |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### api_idempotency_keys
Prevents duplicate processing for repeated submissions.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| application_id | bigint | FK to applications |
| credential_id | bigint | FK to api_credentials |
| key | varchar | Client-provided idempotency key |
| resource_type | varchar | message, batch, webhook |
| resource_id | bigint | Original resource id |
| request_hash | varchar | Deterministic request fingerprint |
| status | varchar | pending, completed, expired |
| expires_at | datetime | TTL for key |
| created_at | datetime |
| updated_at | datetime |

### providers
Global vendor or adapter definitions.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| name | varchar | Provider label |
| code | varchar | External provider code |
| type | varchar | smpp, whatsapp, email, push, ussd, voice |
| status | varchar | active, disabled |
| capabilities_json | json | Supported features |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### provider_connections
Concrete connection configuration for a provider, which may be platform-owned or tenant-owned.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | Nullable for platform-owned connections |
| provider_id | bigint | FK to providers |
| name | varchar | Connection label |
| status | varchar | active, maintenance, disabled |
| environment | varchar | development, staging, production |
| host | varchar | Endpoint hostname |
| port | int | Endpoint port |
| bind_mode | varchar | bind mode or strategy |
| system_type | varchar | provider-specific system type |
| interface_version | varchar | protocol/interface version |
| source_ton | int | source TON |
| source_npi | int | source NPI |
| destination_ton | int | destination TON |
| destination_npi | int | destination NPI |
| throughput | int | configured throughput |
| sessions | int | configured session count |
| strategy | varchar | routing or failover strategy |
| connection_config_encrypted | text | Encrypted endpoint/settings |
| credentials_encrypted | text | Encrypted credentials |
| priority | int | Routing preference |
| is_platform_owned | boolean | Platform-owned flag |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### provider_connection_health_checks
Health metrics and status for provider connections.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| provider_connection_id | bigint | FK to provider_connections |
| status | varchar | healthy, degraded, down |
| checked_at | datetime |
| latency_ms | int | Latest latency |
| error_code | varchar | Optional error classification |
| detail_json | json | Diagnostic metadata |
| created_at | datetime |

### sender_ids
Tenant or provider-owned sender aliases for outbound channels.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| application_id | bigint | FK to applications |
| value | varchar | Sender ID value |
| channel | varchar | sms, whatsapp, etc. |
| status | varchar | active, disabled |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### sender_id_approvals
Mapping table for provider-specific sender-ID approvals.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| sender_id_id | bigint | FK to sender_ids |
| provider_connection_id | bigint | FK to provider_connections |
| approval_state | varchar | pending, approved, rejected |
| approved_at | datetime | Approval timestamp |
| created_at | datetime |
| updated_at | datetime |

### wallets
Wallet header for each tenant balance.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| currency | varchar | ISO currency code |
| balance_minor | bigint | Cached balance for fast read |
| status | varchar | active, locked |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### wallet_reservations
Temporary fund reservations for pending or in-flight messages.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| wallet_id | bigint | FK to wallets |
| tenant_id | bigint | FK to tenants |
| message_id | bigint | FK to messages |
| reservation_key | varchar | Stable reservation reference |
| amount_minor | bigint | Reserved amount |
| status | varchar | pending, captured, released, reversed |
| expires_at | datetime | Reservation expiry |
| created_at | datetime |
| updated_at | datetime |

### wallet_transactions
Immutable ledger entries for reserve, capture, release, refund and reversal.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| public_id | varchar | Public immutable ledger id |
| wallet_id | bigint | FK to wallets |
| tenant_id | bigint | FK to tenants |
| reservation_id | bigint | Nullable FK to wallet_reservations |
| reversal_id | bigint | Nullable FK to wallet_transactions |
| transaction_type | varchar | reserve, capture, release, refund, reversal |
| direction | varchar | debit, credit |
| amount_minor | bigint | Signed amount |
| balance_after_minor | bigint | Ledger balance after transaction |
| currency | varchar | ISO currency code |
| reference_type | varchar | message, topup, refund, adjustment |
| reference_id | bigint | Related resource id |
| description | varchar | Human-readable note |
| created_at | datetime |

### pricing_rules
Tenant or platform pricing rules with rich matching criteria.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | Nullable for platform defaults |
| application_id | bigint | Nullable for application-specific rules |
| provider_connection_id | bigint | Nullable for provider-specific rules |
| channel | varchar | sms, whatsapp, email, push |
| pricing_model | varchar | per_message, per_unit, per_segment |
| unit_type | varchar | message, segment, byte |
| message_type | varchar | transactional, marketing, otp |
| destination_prefix | varchar | Optional prefix filter |
| network_code | varchar | Optional network filter |
| sender_id | varchar | Optional sender ID filter |
| country_code | varchar | Optional region filter |
| customer_price_minor | bigint | Customer price per unit |
| provider_cost_minor | bigint | Provider cost per unit |
| currency | varchar | ISO currency code |
| priority | int | Rule precedence |
| status | varchar | active, disabled |
| valid_from | datetime |
| valid_to | datetime |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### messages
Channel-neutral core message record.

| Column | Type | Notes |
|---|---|---| 
| id | bigint | Primary key |
| public_id | varchar | Public immutable message id |
| tenant_id | bigint | FK to tenants |
| application_id | bigint | FK to applications |
| credential_id | bigint | FK to api_credentials |
| message_key | varchar | External message reference |
| idempotency_key | varchar | Client-supplied idempotency key |
| correlation_id | varchar | End-to-end correlation id |
| direction | varchar | outbound, inbound |
| channel | varchar | Channel family |
| message_status | varchar | received, accepted, queued, processing, sent, failed, cancelled, expired |
| delivery_status | varchar | pending, submitted, delivered, failed, rejected, expired |
| billing_status | varchar | pending, reserved, captured, released, reversed, refunded |
| priority | varchar | low, normal, high, urgent |
| recipient_reference | varchar | Normalized recipient reference |
| content_reference | varchar | Pointer to content payload |
| metadata_json | json | Message metadata |
| submitted_at | datetime | First submission timestamp |
| sent_at | datetime | Dispatch timestamp |
| delivered_at | datetime | Delivery timestamp |
| failed_at | datetime | Failure timestamp |
| refunded_at | datetime | Refund timestamp |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### sms_messages
Channel-specific SMS payload and routing metadata.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| message_id | bigint | FK to messages |
| tenant_id | bigint | FK to tenants |
| sender_id | varchar | Sender ID used |
| recipient_number | varchar | E.164 or normalized number |
| body | text | SMS body |
| encoding | varchar | text, unicode |
| template_id | varchar | Optional template reference |
| network_code | varchar | Optional MNO code |
| recipient_number_encrypted | text | Encrypted recipient number |
| body_encrypted | text | Encrypted message body |
| recipient_masked | varchar | Masked recipient representation |
| body_masked | varchar | Masked message body |
| retention_until | datetime | Retention cutoff |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### message_attempts
Per-attempt submission state for retries and failover.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| public_id | ulid, nullable | Stable identifier for operational attempts; legacy rows may remain null |
| message_id | bigint | FK to messages |
| tenant_id | bigint | FK to tenants |
| provider_connection_id | bigint, nullable | Reserved for future provider-connection persistence; no FK |
| provider_code | varchar, nullable | Sanitized application provider identifier |
| attempt_number | int | Sequence number |
| status | varchar | Submission lifecycle enum, including pending, submitting, submitted, rejected, failed, unconfirmed |
| provider_reference | varchar | Provider submission reference |
| provider_response_code | varchar | Sanitized provider response code |
| error_category | varchar | Sanitized failure category only |
| error_metadata_json | json | Safe operational evidence only |
| request_transmitted | boolean | Whether transport reports request transmission |
| provider_acknowledged | boolean | Whether transport reports a provider response |
| dispatch_token | uuid, nullable | Unique ownership token while submitting |
| next_attempt_at | datetime | Retry scheduling |
| started_at | datetime |
| submitted_at | datetime |
| accepted_at | datetime |
| failed_at | datetime |
| completed_at | datetime |
| created_at | datetime |
| updated_at | datetime |

### delivery_receipts
Dedicated, idempotent DLR table.

| Column | Type | Notes |
|---|---|---| 
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| message_id | bigint | FK to messages |
| attempt_id | bigint | FK to message_attempts |
| provider_connection_id | bigint | FK to provider_connections |
| external_event_id | varchar | Provider event id for idempotency |
| payload_hash | varchar | Hash of provider payload for dedupe |
| receipt_type | varchar | delivered, failed, rejected, pending |
| provider_receipt_id | varchar | Provider-issued receipt id |
| status | varchar | pending, applied, duplicate, ignored |
| receipt_payload_json | json | Provider payload |
| occurred_at | datetime |
| created_at | datetime |
| updated_at | datetime |

### message_events
Immutable normalized timeline of message lifecycle changes.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| message_id | bigint | FK to messages |
| event_type | varchar | accepted, queued, attempted, delivered, failed, refunded |
| message_status | varchar | Normalized message state |
| delivery_status | varchar | Normalized delivery state |
| billing_status | varchar | Normalized billing state |
| detail_json | json | Event payload |
| occurred_at | datetime |
| created_at | datetime |

### message_charges
Immutable pricing snapshots for each message.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| message_id | bigint | FK to messages |
| pricing_rule_id | bigint | FK to pricing_rules |
| provider_connection_id | bigint | FK to provider_connections |
| charge_type | varchar | base, surcharge, refund, adjustment |
| status | varchar | pending, applied, reversed |
| provider_cost_minor | bigint | Provider-side cost |
| customer_price_minor | bigint | Customer charge |
| billable_units | bigint | Number of units billed |
| unit_price_minor | bigint | Price per unit |
| reservation_id | bigint | Nullable FK to wallet_reservations |
| wallet_transaction_id | bigint | Nullable FK to wallet_transactions |
| currency | varchar | ISO currency code |
| created_at | datetime |

### routing_rules
Tenant or platform routing policies.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | Nullable for platform defaults |
| application_id | bigint | Nullable for application-specific rules |
| name | varchar | Rule name |
| priority | int | Rule precedence |
| status | varchar | active, disabled |
| valid_from | datetime |
| valid_to | datetime |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### routing_rule_targets
Concrete provider targets for a routing rule.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| routing_rule_id | bigint | FK to routing_rules |
| provider_connection_id | bigint | FK to provider_connections |
| priority | int | Priority within rule |
| weight | int | Weighted selection value |
| health_threshold | varchar | Health threshold |
| failover_group | varchar | Group for failover |
| status | varchar | active, disabled |
| created_at | datetime |
| updated_at | datetime |

### webhook_endpoints
Customer webhook endpoints and their configuration.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | FK to tenants |
| application_id | bigint | FK to applications |
| url | varchar | Callback endpoint |
| signing_secret_encrypted | text | Encrypted signing secret |
| status | varchar | active, disabled |
| event_subscriptions_json | json | Subscribed event types |
| retry_count | int | Retry policy count |
| retry_interval_seconds | int | Retry interval |
| deleted_at | datetime | Soft delete timestamp |
| created_at | datetime |
| updated_at | datetime |

### provider_webhook_receipts
Inbound provider webhook receipts.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| tenant_id | bigint | Nullable for platform-level receipts |
| provider_connection_id | bigint | FK to provider_connections |
| event_type | varchar | delivery, status, callback |
| payload_json | json | Raw provider payload |
| signature | varchar | Verification signature |
| status | varchar | received, verified, failed |
| processing_status | varchar | pending, processed, failed |
| processing_attempts | int | Number of processing attempts |
| last_error | varchar | Last processing error |
| processed_at | datetime | Processing timestamp |
| created_at | datetime |
| updated_at | datetime |

### webhook_delivery_attempts
Delivery attempts for customer webhooks.

| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key |
| webhook_endpoint_id | bigint | FK to webhook_endpoints |
| tenant_id | bigint | FK to tenants |
| attempt_number | int | Sequence number |
| status | varchar | pending, sent, failed |
| response_code | int | HTTP response code |
| error_message | varchar | Sanitized error |
| created_at | datetime |
| updated_at | datetime |

### transactional_outbox_events
Reliable database-to-queue publication log.

| Column | Type | Notes |
|---|---|---| 
| id | bigint | Primary key |
| tenant_id | bigint | Nullable when event is platform-wide |
| aggregate_type | varchar | Message, Billing, Webhook |
| aggregate_id | bigint | Related aggregate id |
| event_type | varchar | queued, published, failed |
| publication_status | varchar | pending, published, failed |
| payload_json | json | Queue payload |
| queue_name | varchar | Destination queue |
| available_at | datetime | Delivery time |
| published_at | datetime | Publish timestamp |
| attempts | int | Publish attempts |
| locked_at | datetime | Lock timestamp |
| lock_owner | varchar | Worker identifier |
| error_message | varchar | Last publish error |
| created_at | datetime |
| updated_at | datetime |

### audit_logs
Immutable record of security, billing and operational actions.

| Column | Type | Notes |
|---|---|---| 
| id | bigint | Primary key |
| tenant_id | bigint | Nullable for platform-level system actions |
| application_id | bigint | Nullable for application-scoped actions |
| credential_id | bigint | Nullable for credential-related actions |
| request_id | varchar | Request correlation id |
| correlation_id | varchar | End-to-end correlation id |
| actor_type | varchar | user, system, provider, api_credential, application |
| actor_id | bigint | Related actor id |
| actor_ip | varchar | Source IP address |
| action | varchar | created, updated, deleted, charged, refunded |
| entity_type | varchar | message, wallet, provider_connection, webhook_endpoint |
| entity_id | bigint | Related entity |
| before_json | json | Redacted previous state |
| after_json | json | Redacted new state |
| created_at | datetime |

## 5. Relationships

- One tenant has many applications, credentials, wallets, sender IDs, pricing rules, messages, webhook endpoints and audit logs.
- One application belongs to one tenant and has many credentials and webhook endpoints.
- One api credential belongs to one application and one tenant.
- One provider has many provider connections.
- One provider connection belongs to one provider and may belong to one tenant or be platform-owned.
- One message has many message attempts, message events, delivery receipts and message charges.
- One wallet has many wallet reservations and wallet transactions.
- One pricing rule can be referenced by many message charges.
- One webhook endpoint has many delivery attempts.

## 6. Indexing and Uniqueness Strategy

Recommended indexes and constraints:

- tenants.slug unique
- applications.tenant_id, code unique
- api_credentials.tenant_id, application_id, type, status
- api_credentials.key_prefix unique
- api_idempotency_keys.tenant_id, application_id, credential_id, key unique
- api_idempotency_keys.resource_type, resource_id, status
- providers.code unique
- provider_connections.provider_id, tenant_id, status
- provider_connections.tenant_id nullable index for platform-owned and tenant-owned connections
- provider_connection_health_checks.provider_connection_id, checked_at
- sender_ids.tenant_id, application_id, value unique
- sender_id_approvals.sender_id_id, provider_connection_id unique
- wallets.tenant_id, currency unique
- wallet_reservations.wallet_id, status, expires_at
- wallet_transactions.wallet_id, created_at
- wallet_transactions.reservation_id, reference_type, reference_id
- pricing_rules.tenant_id, application_id, provider_connection_id, channel, priority
- messages.tenant_id, application_id, status, created_at
- messages.public_id unique
- messages.tenant_id, idempotency_key unique where appropriate
- sms_messages.message_id unique
- sms_messages.tenant_id, created_at
- message_attempts.message_id, attempt_number unique
- message_attempts.provider_connection_id, status, next_attempt_at
- delivery_receipts.provider_connection_id, external_event_id unique where appropriate
- delivery_receipts.provider_connection_id, payload_hash unique where appropriate
- delivery_receipts.status, occurred_at
- message_events.message_id, occurred_at
- message_charges.message_id, charge_type, status
- webhook_endpoints.tenant_id, application_id, status
- provider_webhook_receipts.provider_connection_id, status, created_at
- webhook_delivery_attempts.webhook_endpoint_id, attempt_number unique
- transactional_outbox_events.queue_name, publication_status, available_at
- audit_logs.tenant_id, created_at
- audit_logs.request_id, correlation_id

## 7. Data Handling Rules

- Money must be stored as integer minor units.
- Provider credentials and webhook signing secrets must be encrypted and never stored in plaintext.
- Message content and recipient data should be retained according to privacy and compliance policies.
- Recipient numbers and message bodies should be encrypted at rest, with masked versions for non-sensitive display, and retained only until the configured retention window expires.
- Billing, messaging and audit history must not be deleted by cascading operations.
- Soft-delete timestamps must be stored separately from business status.
- Secrets must never be stored in audit_logs, message events or webhook payload snapshots.
- Hashing is appropriate for API credential comparison and idempotency fingerprints; encryption is required where the platform must later regenerate signatures or inspect secrets.

## 8. Foreign-Key and Deletion Rules

Recommended behavior:

- tenant delete should be blocked or converted to archival when dependent billing or messaging history exists
- application delete should be restricted if messages, credentials or webhook endpoints still exist
- credential delete should be soft-deleted and revoked before hard removal
- provider delete should be restricted while provider connections or billing history still reference it
- provider_connection delete should be soft-deleted and should not cascade to messages or billing history
- message delete should be soft-deleted only; hard delete should be avoided for operational history
- wallet delete should be disallowed when ledger or reservation rows exist
- billing and audit records should use restrict or no-action semantics rather than cascade delete

## 9. Migration Strategy

The initial migration set should introduce the following in order:

1. tenant, application and api credential tables
2. provider and provider_connection tables
3. wallet, wallet_reservation and wallet_transaction tables
4. pricing, sender_id, sender_id_approval and routing tables
5. message, sms_message, message_attempt and delivery_receipt tables
6. webhook endpoint and outbox tables

### Milestone 1 migration order and ownership constraints

Milestone 1 executes in this order: tenants; the users platform-role column; tenant memberships; applications; API credentials; and audit logs. Tenant business status is one of `trial`, `active`, `suspended`, or `closed`; `deleted_at` remains separate.

Applications have `unique(tenant_id, code)` and the matching composite index `unique(tenant_id, id)`. MySQL can therefore enforce `api_credentials(tenant_id, application_id) -> applications(tenant_id, id)`. Credential prefixes, tenant slugs, and tenant/user membership pairs are unique. Historical relationships restrict deletion instead of cascading. Audit logs have `created_at` only, no soft deletion, and no normal update/delete workflow.

Automated tests use SQLite in memory. MySQL composite foreign-key creation and enforcement must also be verified in the integration environment before release.

### Milestone 2A migration order and messaging constraints

Milestone 2A executes using follow-up migrations only:

1. add `api_credentials(tenant_id, application_id, id)` ownership index
2. create `messages`
3. create `sms_messages`
4. create `message_attempts`
5. create `message_events`
6. create `transactional_outbox_events`

Every newly created table explicitly uses InnoDB. Messaging state columns are VARCHAR columns with PHP enum casts, not native MySQL ENUM columns.

The authoritative Milestone 2A `messages` schema uses `api_credential_id`, separate channel/direction/type/message/delivery/billing/priority states, acceptance and completion timestamps, sanitized metadata, and soft deletion. Database idempotency is `unique(tenant_id, application_id, idempotency_key)`. MySQL permits multiple rows when the idempotency key is NULL; non-null keys are unique per tenant/application, while the same key remains valid for a different tenant or application. Request fingerprints, expiry, and original-response replay remain responsibilities of the future `api_idempotency_keys` API milestone.

Credential ownership is enforced by:

`messages(tenant_id, application_id, api_credential_id) -> api_credentials(tenant_id, application_id, id)`

Tenant IDs on child messaging tables are intentional denormalization for database-enforced ownership:

- `sms_messages(tenant_id, message_id) -> messages(tenant_id, id)`
- `message_attempts(tenant_id, message_id) -> messages(tenant_id, id)`
- `message_events(tenant_id, message_id) -> messages(tenant_id, id)`

`sms_messages` stores a validated sender value because sender-ID persistence is not yet available. Recipient and body ciphertext use Laravel randomized encryption; the keyed HMAC-SHA256 `recipient_hash` is the only deterministic recipient lookup field. `content_expires_at` records retention intent, but content-purge automation is deferred.

`message_attempts` contains submission state only. Its nullable `provider_connection_id` is deliberately unassigned and has no foreign key in Milestone 2A. A follow-up migration must add that foreign key after provider-connection persistence exists.

`message_events` has a public ULID, `created_at` only, and no update, delete, or soft-delete workflow. Direct query-builder mutation bypasses Eloquent append-only guards and is prohibited in application code. The outbox stores public aggregate identifiers and safe payloads; publisher locking and publication fields exist, but no publisher worker exists in this milestone.
## RC1-5 delivery receipt evidence

`message_delivery_receipts` is the immutable internal evidence store for correlated SMPP delivery receipts. Each row belongs to exactly one tenant, message, and outbound attempt and retains the provider code, provider message identifier, validated SMPP state, normalized internal delivery status, database receipt time, and a one-way evidence fingerprint. The store contains no message body or public API representation.

Exact attempt/state evidence and fingerprints are unique. Provider-reference and message-time indexes support deterministic correlation and message history. Composite foreign keys preserve tenant/message/attempt ownership. MySQL check constraints close the accepted SMPP-state and delivery-status vocabularies. Receipt insertion, legal message projection, and message-event insertion occur in one repository transaction; conflicting terminal evidence remains append-only without mutating terminal message state.

## RC1-6 tenant delivery webhooks

`tenant_webhook_configurations` stores exactly one configuration per tenant. It contains enabled state, one HTTPS URL, encrypted active-secret ciphertext, optional encrypted previous-secret ciphertext, the 24-hour overlap expiry, and database-authoritative microsecond timestamps. Secrets are never exposed through public resources or ORM serialization.

`tenant_webhook_events` stores one immutable public terminal outcome per message and preserves tenant ownership, public message identity, optional client reference, terminal status, public timestamps, and safe correlation lineage. Its message identity is unique, so duplicate delivery receipts cannot create duplicate events.

`tenant_webhook_delivery_attempts` stores at most one attempt per event, its URL, bounded outcome, optional HTTP status or sanitized failure category, database-authoritative attempt/completion timestamps, and the 90-day retention boundary. It stores no secret, signature, provider identifier, SMPP evidence, or message content. All three tables use InnoDB, tenant foreign keys, restrictive deletion, and storage-level lifecycle constraints.