DATA_MODEL — payment-gateway-service

Sibling: DOMAIN_MODEL · APPLICATION_LOGIC · SECURITY_MODEL

Strategic anchors: 06 Data Models · ADR-0002 Multi-tenancy · 10 Payments Architecture

payment-gateway-service runs Cloud SQL Postgres 16 with a schema-per-tenant layout for PCI scope minimization (per ADR-0002). A small set of central tables lives in the payments_central schema for cross-tenant control planes (idempotency, dead-letter webhooks, vendor credential pointers, schema registry, audit). Card data is never stored — we hold tokens, last4, and brand only.

0. Schema layout

Cloud SQL instance: gm-payments-prod
└── database: payments
    ├── schema: payments_central          (small, tenant-fanout control plane)
    │   ├── tenant_schema_registry
    │   ├── vendor_credentials_pointer
    │   ├── idempotency_keys
    │   ├── webhook_inbox                 (raw envelopes, partitioned)
    │   ├── dlq_webhooks
    │   └── outbox                        (transactional outbox)
    └── schema: tnt_<tenantId>            (one per tenant; provisioned by tenant-service)
        ├── transactions
        ├── authorizations
        ├── captures
        ├── refunds
        ├── voids
        ├── payment_methods
        ├── webhooks                      (resolved + applied)
        ├── reconciliations
        ├── reconciliation_lines
        ├── chargebacks
        ├── chargeback_evidence
        ├── fx_contexts
        └── adapter_health_log

search_path is set per request: SET LOCAL search_path = tnt_<id>, payments_central, public; inside every request transaction. The pooled connection role (payments_app) holds GRANTs only on payments_central; per-tenant schemas use a SECURITY DEFINER function (payments_central.set_tenant_context(tenantId)) that escalates briefly to grant access to that tenant's schema for the transaction. This makes tenant cross-talk a structural impossibility, not a check.

1. Central tables (payments_central)

1.1 tenant_schema_registry

CREATE TABLE payments_central.tenant_schema_registry (
  tenant_id        TEXT PRIMARY KEY,
  schema_name      TEXT NOT NULL UNIQUE,
  pci_profile      TEXT NOT NULL DEFAULT 'saq_a',
  cmek_key_uri     TEXT NOT NULL,
  provisioned_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  archived_at      TIMESTAMPTZ
);

1.2 vendor_credentials_pointer

Holds pointers only — actual secrets live in GCP Secret Manager, rotated by tenant-service.

CREATE TABLE payments_central.vendor_credentials_pointer (
  tenant_id          TEXT NOT NULL,
  processor          TEXT NOT NULL,                      -- 'stripe' | 'paypal' | 'hesabpay' | …
  env                TEXT NOT NULL,                      -- 'sandbox' | 'production'
  api_key_secret_uri TEXT NOT NULL,                      -- 'projects/.../secrets/...'
  webhook_secret_uri TEXT NOT NULL,
  account_ref        TEXT,                               -- e.g., Stripe acct_…
  enabled            BOOLEAN NOT NULL DEFAULT TRUE,
  precedence         INT NOT NULL DEFAULT 100,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, processor, env)
);
CREATE INDEX ON payments_central.vendor_credentials_pointer (tenant_id, enabled);

1.3 idempotency_keys

CREATE TABLE payments_central.idempotency_keys (
  tenant_id      TEXT NOT NULL,
  scope          TEXT NOT NULL,                          -- 'authorize' | 'capture' | 'refund' | …
  key            TEXT NOT NULL,                          -- ULID from caller
  request_hash   BYTEA NOT NULL,                         -- sha256 of canonical body
  response_body  JSONB,                                  -- cached for 24h
  status         TEXT NOT NULL,                          -- 'in_flight' | 'completed' | 'failed'
  expires_at     TIMESTAMPTZ NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, scope, key)
);
CREATE INDEX ON payments_central.idempotency_keys (expires_at);

A nightly job purges rows where expires_at < now().

1.4 webhook_inbox (partitioned)

CREATE TABLE payments_central.webhook_inbox (
  id                 TEXT NOT NULL,                       -- ULID
  processor          TEXT NOT NULL,
  external_event_id  TEXT NOT NULL,
  tenant_id          TEXT,                                -- null until resolved
  signature_valid    BOOLEAN NOT NULL,
  raw_payload_ref    TEXT NOT NULL,                       -- gs:// URI; encrypted with CMEK
  headers            JSONB NOT NULL,
  status             TEXT NOT NULL DEFAULT 'received',    -- 'received' | 'dispatched' | 'duplicate'
  attempts           INT NOT NULL DEFAULT 0,
  last_error         TEXT,
  received_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (received_at, id)
) PARTITION BY RANGE (received_at);

-- Monthly partitions; 90-day retention then archived to GCS.
CREATE UNIQUE INDEX webhook_inbox_dedupe
  ON payments_central.webhook_inbox (processor, external_event_id);
CREATE INDEX webhook_inbox_pending
  ON payments_central.webhook_inbox (status, received_at) WHERE status = 'received';

1.5 dlq_webhooks

CREATE TABLE payments_central.dlq_webhooks (
  id                 TEXT PRIMARY KEY,
  webhook_id         TEXT NOT NULL,
  tenant_id          TEXT,
  processor          TEXT NOT NULL,
  external_event_id  TEXT NOT NULL,
  attempts           INT NOT NULL,
  last_error_code    TEXT NOT NULL,
  last_error_message TEXT,
  payload_ref        TEXT NOT NULL,
  routed_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  resolved_at        TIMESTAMPTZ,
  resolution_note    TEXT
);
CREATE INDEX ON payments_central.dlq_webhooks (resolved_at) WHERE resolved_at IS NULL;

1.6 outbox

Transactional outbox feeding Pub/Sub.

CREATE TABLE payments_central.outbox (
  id            TEXT PRIMARY KEY,
  tenant_id     TEXT NOT NULL,
  topic         TEXT NOT NULL,
  type          TEXT NOT NULL,
  envelope      JSONB NOT NULL,
  ordering_key  TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  published_at  TIMESTAMPTZ
);
CREATE INDEX outbox_pending ON payments_central.outbox (created_at) WHERE published_at IS NULL;

---

2. Per-tenant tables (tnt_<tenantId>)

Created from a templated migration set (/migrations/tenant/*.sql) applied by tenant-service during provisioning. Per-tenant schemas guarantee storage-level isolation, removing the need for tenant_id columns on most tables (they remain on a few denormalized read tables for query speed).

2.1 transactions (aggregate root)

CREATE TABLE transactions (
  id                  TEXT PRIMARY KEY,                  -- pay_<ULID>
  reservation_id      TEXT NOT NULL,
  property_id         TEXT NOT NULL,
  guest_id            TEXT NOT NULL,
  amount_micro        NUMERIC(38,0) NOT NULL,
  currency            CHAR(3) NOT NULL,
  method              TEXT NOT NULL,                     -- 'card' | 'cash_on_arrival' | 'mfs_hesabpay' | …
  processor           TEXT NOT NULL,
  status              TEXT NOT NULL,                     -- see DOMAIN_MODEL state machine
  intent              TEXT NOT NULL,                     -- 'authorize_then_capture' | 'capture_immediately'
  fx_context_id       TEXT REFERENCES fx_contexts(id),
  description         TEXT,
  initiated_by        JSONB NOT NULL,                    -- { type, id }
  metadata            JSONB NOT NULL DEFAULT '{}'::jsonb,
  device_context      JSONB,                             -- desktop offline context
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  version             INT NOT NULL DEFAULT 1,
  CHECK (length(currency) = 3),
  CHECK (amount_micro >= 0)
);
CREATE INDEX ON transactions (reservation_id);
CREATE INDEX ON transactions (status, created_at);
CREATE INDEX ON transactions (processor, created_at);
CREATE INDEX ON transactions (guest_id, created_at DESC);

2.2 authorizations

CREATE TABLE authorizations (
  id                TEXT PRIMARY KEY,                    -- auth_<ULID>
  payment_id        TEXT NOT NULL REFERENCES transactions(id) ON DELETE RESTRICT,
  processor_ref     TEXT,
  amount_micro      NUMERIC(38,0) NOT NULL,
  currency          CHAR(3) NOT NULL,
  status            TEXT NOT NULL,                       -- 'requires_action' | 'active' | 'expired' | 'voided'
  three_ds          JSONB,
  expires_at        TIMESTAMPTZ NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON authorizations (payment_id);
CREATE INDEX ON authorizations (status, expires_at);

2.3 captures

CREATE TABLE captures (
  id              TEXT PRIMARY KEY,                      -- cap_<ULID>
  payment_id      TEXT NOT NULL REFERENCES transactions(id) ON DELETE RESTRICT,
  authorization_id TEXT REFERENCES authorizations(id),
  processor_ref   TEXT,
  amount_micro    NUMERIC(38,0) NOT NULL,
  currency        CHAR(3) NOT NULL,
  status          TEXT NOT NULL,                         -- 'captured' | 'pending' | 'failed'
  captured_at     TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON captures (payment_id);

2.4 refunds

CREATE TABLE refunds (
  id              TEXT PRIMARY KEY,                      -- rfd_<ULID>
  payment_id      TEXT NOT NULL REFERENCES transactions(id) ON DELETE RESTRICT,
  capture_id      TEXT REFERENCES captures(id),
  processor_ref   TEXT,
  amount_micro    NUMERIC(38,0) NOT NULL,
  currency        CHAR(3) NOT NULL,
  reason          TEXT NOT NULL,
  note            TEXT,
  status          TEXT NOT NULL,                         -- 'pending' | 'refunded' | 'failed'
  initiated_by    JSONB NOT NULL,
  refunded_at     TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON refunds (payment_id);
CREATE INDEX ON refunds (status, created_at);

2.5 voids

CREATE TABLE voids (
  id               TEXT PRIMARY KEY,                     -- vd_<ULID>
  payment_id       TEXT NOT NULL REFERENCES transactions(id),
  authorization_id TEXT NOT NULL REFERENCES authorizations(id),
  processor_ref    TEXT,
  reason           TEXT NOT NULL,
  voided_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

2.6 payment_methods

CREATE TABLE payment_methods (
  id                  TEXT PRIMARY KEY,                  -- pm_<ULID>
  guest_id            TEXT NOT NULL,
  processor           TEXT NOT NULL,
  kind                TEXT NOT NULL,                     -- 'card' | 'paypal_account' | 'mfs_account'
  processor_token_enc BYTEA NOT NULL,                    -- envelope-encrypted (CMEK), never returned via API
  display             JSONB NOT NULL,                    -- { brand, last4, expMonth, expYear }
  status              TEXT NOT NULL DEFAULT 'active',    -- 'active' | 'detached' | 'expired'
  fingerprint_hash    BYTEA,                             -- HMAC of processor fingerprint, dedupe within tenant
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  detached_at         TIMESTAMPTZ
);
CREATE INDEX ON payment_methods (guest_id, status);
CREATE UNIQUE INDEX ON payment_methods (guest_id, processor, fingerprint_hash) WHERE status = 'active';

A row-level CHECK ensures display ?& array['brand', 'last4'] (no PAN, no full card number ever).

2.7 webhooks (resolved view of central inbox)

CREATE TABLE webhooks (
  id                TEXT PRIMARY KEY,                    -- whk_<ULID>
  inbox_id          TEXT NOT NULL,                       -- payments_central.webhook_inbox.id
  processor         TEXT NOT NULL,
  external_event_id TEXT NOT NULL,
  event_type        TEXT NOT NULL,
  signature_valid   BOOLEAN NOT NULL,
  status            TEXT NOT NULL,                       -- 'received' | 'processing' | 'applied' | 'failed' | 'duplicate'
  applied_to_payment_id TEXT REFERENCES transactions(id),
  attempts          INT NOT NULL DEFAULT 0,
  last_error        TEXT,
  received_at       TIMESTAMPTZ NOT NULL,
  processed_at      TIMESTAMPTZ
);
CREATE UNIQUE INDEX ON webhooks (processor, external_event_id);
CREATE INDEX ON webhooks (status, received_at);

2.8 reconciliations & reconciliation_lines

CREATE TABLE reconciliations (
  id            TEXT PRIMARY KEY,                        -- rec_<ULID>
  processor     TEXT NOT NULL,
  date          DATE NOT NULL,
  status        TEXT NOT NULL,                           -- 'in_progress' | 'completed' | 'failed'
  matched_count INT NOT NULL DEFAULT 0,
  matched_total_micro NUMERIC(38,0) NOT NULL DEFAULT 0,
  unmatched_count INT NOT NULL DEFAULT 0,
  unmatched_total_micro NUMERIC(38,0) NOT NULL DEFAULT 0,
  fees_micro    NUMERIC(38,0) NOT NULL DEFAULT 0,
  net_micro     NUMERIC(38,0) NOT NULL DEFAULT 0,
  currency      CHAR(3) NOT NULL,
  source_report_uri TEXT NOT NULL,
  ingested_at   TIMESTAMPTZ NOT NULL,
  completed_at  TIMESTAMPTZ,
  UNIQUE (processor, date)
);

CREATE TABLE reconciliation_lines (
  id                TEXT PRIMARY KEY,
  reconciliation_id TEXT NOT NULL REFERENCES reconciliations(id) ON DELETE CASCADE,
  side              TEXT NOT NULL,                       -- 'matched' | 'platform_only' | 'vendor_only'
  payment_id        TEXT REFERENCES transactions(id),
  vendor_ref        TEXT,
  amount_micro      NUMERIC(38,0) NOT NULL,
  currency          CHAR(3) NOT NULL,
  fee_micro         NUMERIC(38,0) NOT NULL DEFAULT 0,
  suspected_reason  TEXT,
  resolved_at       TIMESTAMPTZ,
  resolution_note   TEXT
);
CREATE INDEX ON reconciliation_lines (reconciliation_id, side);
CREATE INDEX ON reconciliation_lines (payment_id);

2.9 chargebacks & chargeback_evidence

CREATE TABLE chargebacks (
  id              TEXT PRIMARY KEY,                      -- cbk_<ULID>
  payment_id      TEXT NOT NULL REFERENCES transactions(id),
  processor       TEXT NOT NULL,
  processor_ref   TEXT NOT NULL,
  amount_micro    NUMERIC(38,0) NOT NULL,
  currency        CHAR(3) NOT NULL,
  reason          TEXT NOT NULL,
  status          TEXT NOT NULL,                         -- see state machine
  fraud_signal    BOOLEAN NOT NULL DEFAULT FALSE,
  deadline_at     TIMESTAMPTZ NOT NULL,
  outcome         TEXT,                                  -- 'won' | 'lost' | NULL
  decided_at      TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON chargebacks (status, deadline_at);
CREATE INDEX ON chargebacks (payment_id);

CREATE TABLE chargeback_evidence (
  id              TEXT PRIMARY KEY,
  chargeback_id   TEXT NOT NULL REFERENCES chargebacks(id) ON DELETE CASCADE,
  bundle_ref      TEXT NOT NULL,
  narrative       TEXT NOT NULL,
  ai_assisted     BOOLEAN NOT NULL DEFAULT FALSE,
  ai_provenance_id TEXT,
  submitted_by    TEXT NOT NULL,
  submitted_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

2.10 fx_contexts

CREATE TABLE fx_contexts (
  id          TEXT PRIMARY KEY,                          -- fxs_<ULID>
  base        CHAR(3) NOT NULL,
  quote       CHAR(3) NOT NULL,
  rate        NUMERIC(20,8) NOT NULL,
  source      TEXT NOT NULL,                             -- 'ecb' | 'oanda' | 'cached_24h' | …
  provider    TEXT NOT NULL,
  quoted_at   TIMESTAMPTZ NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON fx_contexts (base, quote, quoted_at DESC);

2.11 adapter_health_log

CREATE TABLE adapter_health_log (
  id              BIGSERIAL PRIMARY KEY,
  processor       TEXT NOT NULL,
  previous_state  TEXT NOT NULL,
  current_state   TEXT NOT NULL,
  error_rate_1m   NUMERIC(6,4) NOT NULL,
  p99_latency_ms  INT NOT NULL,
  reason          TEXT,
  changed_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX ON adapter_health_log (processor, changed_at DESC);

---

3. Encryption, masking, retention

Concern	Treatment
payment_methods.processor_token_enc	Envelope encryption with per-tenant CMEK; DEK rotated on retrieval >90 days old
webhook_inbox.raw_payload_ref	Stored in GCS bucket gm-webhooks-raw, server-side CMEK; row holds URI only
Logs / events	PAN/CVV are never in any column or log; CI scanner blocks regex matches in migrations & test fixtures
Retention — transactions, captures, refunds, voids	10 years (financial record)
Retention — webhooks, adapter_health_log	90 days hot, 2 years archived
Retention — chargeback_evidence	Until processor deadline + 5 years
Tenant deletion	Schema dropped after a 30-day soft-delete window; webhook_inbox rows for that tenant are anonymized (tenant_id = NULL) and retained 90 days for fraud forensics

4. Migrations

• Tool: node-pg-migrate with separate dirs migrations/central/ and migrations/tenant/.
• Tenant migrations are applied by tenant-service per-tenant on provisioning and on deploy (idempotent CREATE TABLE IF NOT EXISTS patterns are forbidden — migrations are versioned per-tenant in payments_central.tenant_migrations).
• Central migrations gated behind a manual approval step in CD for production.

5. Indexing & performance notes

• All transactions queries by reservation are O(log n) via (reservation_id) index.
• Pending-webhook drains use partial index webhooks (status, received_at) WHERE status = 'received'.
• The idempotency_keys partial index (expires_at) powers the GC job without table scans.
• webhook_inbox is partitioned monthly to keep autovacuum cost bounded.

6. Cross-service ownership boundary

• This service writes only to its own schemas. Reads from other services (e.g., billing-service) happen via API or via subscribed events — never by direct SQL into payment-gateway-service schemas. The Cloud SQL IAM grants only payment-gateway-service@… the payments_app role.
• billing-service consumes melmastoon.payment.transaction.captured.v1 to post folio entries; it does not query our DB.