Pay-In Schema

Owner	Classification	Review Date	Status
CDO Office	Internal	April 2027	Active

-- ============================================================================
-- Simpaisa Pay-Ins SurrealDB Schema
-- Domain: Mobile Money Pay-Ins, OTP Verification, Tokenised Payments,
--         Recurring Subscriptions, Channel Configuration
-- Target: SurrealDB 2.x | SurrealQL
-- Markets: PK, BD, NP, IQ
-- Scale: 270M+ transactions, $1B+ processed
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Namespace & Database
-- ----------------------------------------------------------------------------
-- USE NS simpaisa;
-- USE DB payments;

-- ============================================================================
-- SCOPES & ACCESS
-- ============================================================================

DEFINE SCOPE merchant SESSION 24h
  SIGNUP (
    -- Merchant onboarding handled by platform-svc; stub for completeness
    CREATE merchant_user SET
      email = $email,
      merchantId = $merchantId,
      passwordHash = crypto::argon2::generate($password),
      createdAt = time::now()
  )
  SIGNIN (
    SELECT * FROM merchant_user
    WHERE email = $email
      AND crypto::argon2::compare(passwordHash, $password)
  );

DEFINE SCOPE service SESSION 1h
  SIGNIN (
    SELECT * FROM api_key
    WHERE keyHash = crypto::sha256($apiKey)
      AND active = true
      AND expiresAt > time::now()
  );

-- ============================================================================
-- ENUMS (modelled as strict string checks via ASSERT)
-- ============================================================================

-- Transaction types: CHARGE, REFUND, REVERSAL, TOKEN_CHARGE, RECURRING_CHARGE
-- Statuses: INITIATED, OTP_SENT, OTP_VERIFIED, SUBMITTED, SUCCESS, FAILED,
--           REVERSED, EXPIRED, PENDING_CALLBACK

-- ============================================================================
-- TABLE: pay_in_transaction
-- Core pay-in transaction record
-- ============================================================================

DEFINE TABLE pay_in_transaction SCHEMAFULL
  PERMISSIONS
    FOR select WHERE merchantId = $auth.merchantId OR $scope = 'service'
    FOR create, update WHERE $scope = 'service'
    FOR delete NONE;

DEFINE FIELD id               ON pay_in_transaction TYPE string;
DEFINE FIELD merchantId       ON pay_in_transaction TYPE string
  ASSERT $value != NONE;
DEFINE FIELD operatorId       ON pay_in_transaction TYPE string
  ASSERT $value != NONE
  COMMENT 'Mobile network operator identifier (e.g. jazz_pk, bkash_bd, ncell_np)';
DEFINE FIELD transactionType  ON pay_in_transaction TYPE string
  ASSERT $value IN ['CHARGE', 'REFUND', 'REVERSAL', 'TOKEN_CHARGE', 'RECURRING_CHARGE']
  COMMENT 'Enumerated transaction type';
DEFINE FIELD amount           ON pay_in_transaction TYPE decimal
  ASSERT $value > 0;
DEFINE FIELD currency         ON pay_in_transaction TYPE string
  ASSERT string::len($value) = 3
  COMMENT 'ISO 4217 currency code (PKR, BDT, NPR, IQD)';
DEFINE FIELD msisdn           ON pay_in_transaction TYPE string
  ASSERT string::len($value) >= 10 AND string::len($value) <= 15
  COMMENT 'Subscriber mobile number in E.164 format';
DEFINE FIELD status           ON pay_in_transaction TYPE string
  ASSERT $value IN ['INITIATED', 'OTP_SENT', 'OTP_VERIFIED', 'SUBMITTED', 'SUCCESS', 'FAILED', 'REVERSED', 'EXPIRED', 'PENDING_CALLBACK']
  COMMENT 'Current transaction state';
DEFINE FIELD traceId          ON pay_in_transaction TYPE string
  COMMENT 'Distributed tracing correlation ID';
DEFINE FIELD idempotencyKey   ON pay_in_transaction TYPE string
  ASSERT $value != NONE
  COMMENT 'Client-supplied idempotency key to prevent duplicate charges';
DEFINE FIELD channelRef       ON pay_in_transaction TYPE option<string>
  COMMENT 'Upstream channel/operator reference ID';
DEFINE FIELD failureReason    ON pay_in_transaction TYPE option<string>;
DEFINE FIELD metadata         ON pay_in_transaction TYPE option<object>
  COMMENT 'Arbitrary merchant-supplied metadata (max 4KB)';
DEFINE FIELD createdAt        ON pay_in_transaction TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt        ON pay_in_transaction TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

-- Indexes
DEFINE INDEX idx_payin_merchant_status
  ON pay_in_transaction FIELDS merchantId, status;
DEFINE INDEX idx_payin_idempotency
  ON pay_in_transaction FIELDS idempotencyKey UNIQUE;
DEFINE INDEX idx_payin_msisdn
  ON pay_in_transaction FIELDS msisdn, createdAt;
DEFINE INDEX idx_payin_trace
  ON pay_in_transaction FIELDS traceId;
DEFINE INDEX idx_payin_created
  ON pay_in_transaction FIELDS createdAt;
DEFINE INDEX idx_payin_operator_status
  ON pay_in_transaction FIELDS operatorId, status;

-- Event: State transition audit
DEFINE EVENT payin_state_change ON TABLE pay_in_transaction WHEN $before.status != $after.status THEN {
  CREATE event_store SET
    aggregateId   = $after.id,
    aggregateType = 'pay_in_transaction',
    eventType     = string::concat('PAYIN_', $after.status),
    eventData     = {
      previousStatus: $before.status,
      newStatus:      $after.status,
      amount:         $after.amount,
      currency:       $after.currency,
      merchantId:     $after.merchantId,
      operatorId:     $after.operatorId,
      traceId:        $after.traceId
    },
    sequenceNumber = time::now(),
    createdAt      = time::now()
};

-- ============================================================================
-- TABLE: otp_request
-- OTP verification requests linked to pay-in transactions
-- ============================================================================

DEFINE TABLE otp_request SCHEMAFULL
  PERMISSIONS
    FOR select, create, update WHERE $scope = 'service'
    FOR delete NONE;

DEFINE FIELD id              ON otp_request TYPE string;
DEFINE FIELD transactionId   ON otp_request TYPE string
  ASSERT $value != NONE
  COMMENT 'References pay_in_transaction.id';
DEFINE FIELD msisdn          ON otp_request TYPE string;
DEFINE FIELD otpHash         ON otp_request TYPE string
  COMMENT 'Argon2-hashed OTP — never store plaintext';
DEFINE FIELD status          ON otp_request TYPE string
  ASSERT $value IN ['PENDING', 'VERIFIED', 'EXPIRED', 'FAILED'];
DEFINE FIELD attempts        ON otp_request TYPE int
  DEFAULT 0
  ASSERT $value <= 5
  COMMENT 'Max 5 attempts before lockout';
DEFINE FIELD expiresAt       ON otp_request TYPE datetime
  COMMENT 'OTP validity window (typically 5 minutes)';
DEFINE FIELD verifiedAt      ON otp_request TYPE option<datetime>;
DEFINE FIELD createdAt       ON otp_request TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt       ON otp_request TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_otp_transaction
  ON otp_request FIELDS transactionId;
DEFINE INDEX idx_otp_msisdn_status
  ON otp_request FIELDS msisdn, status;

-- ============================================================================
-- TABLE: payment_token
-- Tokenised payment credentials for repeat charges
-- ============================================================================

DEFINE TABLE payment_token SCHEMAFULL
  PERMISSIONS
    FOR select WHERE merchantId = $auth.merchantId OR $scope = 'service'
    FOR create, update WHERE $scope = 'service'
    FOR delete WHERE $scope = 'service';

DEFINE FIELD id              ON payment_token TYPE string;
DEFINE FIELD merchantId      ON payment_token TYPE string;
DEFINE FIELD msisdn          ON payment_token TYPE string;
DEFINE FIELD operatorId      ON payment_token TYPE string;
DEFINE FIELD tokenRef        ON payment_token TYPE string
  COMMENT 'Operator-issued payment token reference';
DEFINE FIELD status          ON payment_token TYPE string
  ASSERT $value IN ['ACTIVE', 'REVOKED', 'EXPIRED'];
DEFINE FIELD lastUsedAt      ON payment_token TYPE option<datetime>;
DEFINE FIELD expiresAt       ON payment_token TYPE option<datetime>;
DEFINE FIELD createdAt       ON payment_token TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt       ON payment_token TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_token_merchant
  ON payment_token FIELDS merchantId, status;
DEFINE INDEX idx_token_msisdn
  ON payment_token FIELDS msisdn, operatorId;

-- ============================================================================
-- TABLE: recurring_subscription
-- Merchant-initiated recurring billing subscriptions
-- ============================================================================

DEFINE TABLE recurring_subscription SCHEMAFULL
  PERMISSIONS
    FOR select WHERE merchantId = $auth.merchantId OR $scope = 'service'
    FOR create, update WHERE $scope = 'service'
    FOR delete NONE;

DEFINE FIELD id              ON recurring_subscription TYPE string;
DEFINE FIELD merchantId      ON recurring_subscription TYPE string;
DEFINE FIELD msisdn          ON recurring_subscription TYPE string;
DEFINE FIELD tokenId         ON recurring_subscription TYPE string
  COMMENT 'References payment_token.id';
DEFINE FIELD amount          ON recurring_subscription TYPE decimal
  ASSERT $value > 0;
DEFINE FIELD currency        ON recurring_subscription TYPE string
  ASSERT string::len($value) = 3;
DEFINE FIELD frequency       ON recurring_subscription TYPE string
  ASSERT $value IN ['DAILY', 'WEEKLY', 'BIWEEKLY', 'MONTHLY', 'QUARTERLY', 'ANNUAL'];
DEFINE FIELD status          ON recurring_subscription TYPE string
  ASSERT $value IN ['ACTIVE', 'PAUSED', 'CANCELLED', 'EXPIRED', 'SUSPENDED'];
DEFINE FIELD nextChargeAt    ON recurring_subscription TYPE datetime;
DEFINE FIELD lastChargedAt   ON recurring_subscription TYPE option<datetime>;
DEFINE FIELD failureCount    ON recurring_subscription TYPE int
  DEFAULT 0
  COMMENT 'Consecutive failures — suspend after 3';
DEFINE FIELD maxRetries      ON recurring_subscription TYPE int
  DEFAULT 3;
DEFINE FIELD createdAt       ON recurring_subscription TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt       ON recurring_subscription TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_subscription_merchant
  ON recurring_subscription FIELDS merchantId, status;
DEFINE INDEX idx_subscription_next_charge
  ON recurring_subscription FIELDS status, nextChargeAt
  COMMENT 'Used by scheduler to find due subscriptions';

-- Event: Subscription status change
DEFINE EVENT subscription_state_change ON TABLE recurring_subscription WHEN $before.status != $after.status THEN {
  CREATE event_store SET
    aggregateId   = $after.id,
    aggregateType = 'recurring_subscription',
    eventType     = string::concat('SUBSCRIPTION_', $after.status),
    eventData     = {
      previousStatus: $before.status,
      newStatus:      $after.status,
      merchantId:     $after.merchantId,
      msisdn:        $after.msisdn,
      amount:        $after.amount
    },
    sequenceNumber = time::now(),
    createdAt      = time::now()
};

-- ============================================================================
-- TABLE: channel_config
-- Operator/channel configuration per market
-- ============================================================================

DEFINE TABLE channel_config SCHEMAFULL
  PERMISSIONS
    FOR select WHERE $scope = 'service'
    FOR create, update WHERE $scope = 'service'
    FOR delete NONE;

DEFINE FIELD id              ON channel_config TYPE string;
DEFINE FIELD operatorId      ON channel_config TYPE string;
DEFINE FIELD country         ON channel_config TYPE string
  ASSERT $value IN ['PK', 'BD', 'NP', 'IQ'];
DEFINE FIELD channelName     ON channel_config TYPE string
  COMMENT 'Human-readable name (e.g. JazzCash, bKash, eSewa)';
DEFINE FIELD apiEndpoint     ON channel_config TYPE string;
DEFINE FIELD authMethod      ON channel_config TYPE string
  ASSERT $value IN ['OAUTH2', 'API_KEY', 'HMAC', 'CERTIFICATE'];
DEFINE FIELD timeoutMs       ON channel_config TYPE int
  DEFAULT 30000;
DEFINE FIELD retryPolicy     ON channel_config TYPE object
  COMMENT '{ maxRetries: int, backoffMs: int, backoffMultiplier: float }';
DEFINE FIELD supportsOTP     ON channel_config TYPE bool
  DEFAULT true;
DEFINE FIELD supportsToken   ON channel_config TYPE bool
  DEFAULT false;
DEFINE FIELD active          ON channel_config TYPE bool
  DEFAULT true;
DEFINE FIELD createdAt       ON channel_config TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt       ON channel_config TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_channel_operator
  ON channel_config FIELDS operatorId UNIQUE;
DEFINE INDEX idx_channel_country
  ON channel_config FIELDS country, active;