Remittance Schema

Owner	Classification	Review Date	Status
CDO Office	Internal	April 2027	Active

-- ============================================================================
-- Simpaisa Remittances SurrealDB Schema
-- Domain: Cross-Border Remittances, FX Quotes, AML/CFT Checks,
--         Beneficiary KYC, Corridor Configuration, Compliance Records
-- Target: SurrealDB 2.x | SurrealQL
-- Markets: PK, BD, NP, IQ
-- Scale: 270M+ transactions, $1B+ processed
-- ============================================================================

-- ============================================================================
-- TABLE: remittance
-- Core cross-border remittance transaction
-- ============================================================================

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

DEFINE FIELD id                  ON remittance TYPE string;
DEFINE FIELD merchantId          ON remittance TYPE string
  ASSERT $value != NONE;
DEFINE FIELD corridorId          ON remittance TYPE string
  COMMENT 'References corridor_config.id (e.g. GBP_PKR, AED_BDT)';
DEFINE FIELD senderId            ON remittance TYPE string
  COMMENT 'Sender identity reference';
DEFINE FIELD beneficiaryId       ON remittance TYPE string
  COMMENT 'References beneficiary_kyc.id';
DEFINE FIELD sourceAmount        ON remittance TYPE decimal
  ASSERT $value > 0;
DEFINE FIELD sourceCurrency      ON remittance TYPE string
  ASSERT string::len($value) = 3;
DEFINE FIELD destinationAmount   ON remittance TYPE decimal
  ASSERT $value > 0;
DEFINE FIELD destinationCurrency ON remittance TYPE string
  ASSERT string::len($value) = 3;
DEFINE FIELD fxQuoteId           ON remittance TYPE string
  COMMENT 'References fx_quote.id — locked rate used for this transfer';
DEFINE FIELD exchangeRate        ON remittance TYPE decimal
  ASSERT $value > 0;
DEFINE FIELD fee                 ON remittance TYPE decimal
  DEFAULT 0;
DEFINE FIELD deliveryMethod      ON remittance TYPE string
  ASSERT $value IN ['MOBILE_WALLET', 'BANK_DEPOSIT', 'CASH_PICKUP', 'AGENT_NETWORK']
  COMMENT 'How the beneficiary receives funds';
DEFINE FIELD status              ON remittance TYPE string
  ASSERT $value IN [
    'QUOTE_LOCKED', 'AML_PENDING', 'AML_PASSED', 'AML_FAILED',
    'KYC_PENDING', 'KYC_VERIFIED', 'KYC_FAILED',
    'SUBMITTED', 'PROCESSING', 'DELIVERED', 'FAILED',
    'RETURNED', 'UNDER_REVIEW', 'CANCELLED'
  ];
DEFINE FIELD amlCheckId          ON remittance TYPE option<string>
  COMMENT 'References aml_check.id';
DEFINE FIELD complianceRecordId  ON remittance TYPE option<string>
  COMMENT 'References compliance_record.id';
DEFINE FIELD traceId             ON remittance TYPE string;
DEFINE FIELD idempotencyKey      ON remittance TYPE string
  ASSERT $value != NONE;
DEFINE FIELD channelRef          ON remittance TYPE option<string>;
DEFINE FIELD purpose             ON remittance TYPE option<string>
  COMMENT 'Purpose of remittance (family support, education, medical, etc.)';
DEFINE FIELD failureReason       ON remittance TYPE option<string>;
DEFINE FIELD deliveredAt         ON remittance TYPE option<datetime>;
DEFINE FIELD metadata            ON remittance TYPE option<object>;
DEFINE FIELD createdAt           ON remittance TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt           ON remittance TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

-- Indexes
DEFINE INDEX idx_remittance_idempotency
  ON remittance FIELDS idempotencyKey UNIQUE;
DEFINE INDEX idx_remittance_merchant_status
  ON remittance FIELDS merchantId, status;
DEFINE INDEX idx_remittance_corridor
  ON remittance FIELDS corridorId, createdAt;
DEFINE INDEX idx_remittance_sender
  ON remittance FIELDS senderId;
DEFINE INDEX idx_remittance_beneficiary
  ON remittance FIELDS beneficiaryId;
DEFINE INDEX idx_remittance_trace
  ON remittance FIELDS traceId;
DEFINE INDEX idx_remittance_created
  ON remittance FIELDS createdAt;

-- Event: State transition audit
DEFINE EVENT remittance_state_change ON TABLE remittance WHEN $before.status != $after.status THEN {
  CREATE event_store SET
    aggregateId   = $after.id,
    aggregateType = 'remittance',
    eventType     = string::concat('REMITTANCE_', $after.status),
    eventData     = {
      previousStatus:      $before.status,
      newStatus:           $after.status,
      sourceAmount:        $after.sourceAmount,
      sourceCurrency:      $after.sourceCurrency,
      destinationAmount:   $after.destinationAmount,
      destinationCurrency: $after.destinationCurrency,
      corridorId:          $after.corridorId,
      merchantId:          $after.merchantId,
      traceId:             $after.traceId
    },
    sequenceNumber = time::now(),
    createdAt      = time::now()
};

-- ============================================================================
-- TABLE: fx_quote
-- Foreign exchange quotes with time-limited validity
-- ============================================================================

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

DEFINE FIELD id                ON fx_quote TYPE string;
DEFINE FIELD merchantId        ON fx_quote TYPE string
  ASSERT $value != NONE;
DEFINE FIELD corridorId        ON fx_quote TYPE string;
DEFINE FIELD sourceCurrency    ON fx_quote TYPE string
  ASSERT string::len($value) = 3;
DEFINE FIELD destCurrency      ON fx_quote TYPE string
  ASSERT string::len($value) = 3;
DEFINE FIELD midMarketRate     ON fx_quote TYPE decimal
  ASSERT $value > 0
  COMMENT 'Mid-market rate at time of quote';
DEFINE FIELD appliedRate       ON fx_quote TYPE decimal
  ASSERT $value > 0
  COMMENT 'Rate after markup applied';
DEFINE FIELD markupBps         ON fx_quote TYPE int
  DEFAULT 0
  COMMENT 'Markup in basis points';
DEFINE FIELD rateSource        ON fx_quote TYPE string
  COMMENT 'Provider of the underlying rate (e.g. reuters, open_exchange)';
DEFINE FIELD status            ON fx_quote TYPE string
  ASSERT $value IN ['ACTIVE', 'LOCKED', 'USED', 'EXPIRED', 'CANCELLED'];
DEFINE FIELD expiresAt         ON fx_quote TYPE datetime
  COMMENT 'Quote validity window — typically 30 minutes';
DEFINE FIELD lockedAt          ON fx_quote TYPE option<datetime>
  COMMENT 'Timestamp when merchant confirmed the quote';
DEFINE FIELD usedByRemittance  ON fx_quote TYPE option<string>
  COMMENT 'References remittance.id when consumed';
DEFINE FIELD createdAt         ON fx_quote TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt         ON fx_quote TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_fxquote_merchant
  ON fx_quote FIELDS merchantId, status;
DEFINE INDEX idx_fxquote_corridor
  ON fx_quote FIELDS corridorId, createdAt;
DEFINE INDEX idx_fxquote_expiry
  ON fx_quote FIELDS status, expiresAt
  COMMENT 'Used by expiry job to invalidate stale quotes';

-- Event: Quote lifecycle tracking
DEFINE EVENT fxquote_state_change ON TABLE fx_quote WHEN $before.status != $after.status THEN {
  CREATE event_store SET
    aggregateId   = $after.id,
    aggregateType = 'fx_quote',
    eventType     = string::concat('FX_QUOTE_', $after.status),
    eventData     = {
      previousStatus: $before.status,
      newStatus:      $after.status,
      corridorId:     $after.corridorId,
      appliedRate:    $after.appliedRate,
      merchantId:     $after.merchantId
    },
    sequenceNumber = time::now(),
    createdAt      = time::now()
};

-- ============================================================================
-- TABLE: aml_check
-- Anti-Money Laundering / Counter-Financing of Terrorism checks
-- ============================================================================

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

DEFINE FIELD id                ON aml_check TYPE string;
DEFINE FIELD remittanceId      ON aml_check TYPE string
  COMMENT 'References remittance.id';
DEFINE FIELD senderId          ON aml_check TYPE string;
DEFINE FIELD beneficiaryId     ON aml_check TYPE string;
DEFINE FIELD screeningProvider ON aml_check TYPE string
  COMMENT 'e.g. dow_jones, refinitiv, comply_advantage';
DEFINE FIELD watchlistsChecked ON aml_check TYPE array<string>
  COMMENT 'OFAC, UN, EU, local PEP lists, etc.';
DEFINE FIELD status            ON aml_check TYPE string
  ASSERT $value IN ['PENDING', 'CLEAR', 'MATCH_FOUND', 'FALSE_POSITIVE', 'ESCALATED', 'BLOCKED'];
DEFINE FIELD riskScore         ON aml_check TYPE option<int>
  COMMENT 'Numeric risk score from screening provider (0-100)';
DEFINE FIELD matchDetails      ON aml_check TYPE option<object>
  COMMENT 'Details of any matches found';
DEFINE FIELD reviewedBy        ON aml_check TYPE option<string>
  COMMENT 'Compliance officer who reviewed (if escalated)';
DEFINE FIELD reviewNotes       ON aml_check TYPE option<string>;
DEFINE FIELD completedAt       ON aml_check TYPE option<datetime>;
DEFINE FIELD createdAt         ON aml_check TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt         ON aml_check TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_aml_remittance
  ON aml_check FIELDS remittanceId;
DEFINE INDEX idx_aml_sender
  ON aml_check FIELDS senderId, createdAt;
DEFINE INDEX idx_aml_status
  ON aml_check FIELDS status;

-- ============================================================================
-- TABLE: beneficiary_kyc
-- Know Your Customer records for remittance beneficiaries
-- ============================================================================

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

DEFINE FIELD id                ON beneficiary_kyc TYPE string;
DEFINE FIELD merchantId        ON beneficiary_kyc TYPE string;
DEFINE FIELD fullName          ON beneficiary_kyc TYPE string;
DEFINE FIELD dateOfBirth       ON beneficiary_kyc TYPE option<string>
  COMMENT 'ISO 8601 date format';
DEFINE FIELD nationality       ON beneficiary_kyc TYPE option<string>
  COMMENT 'ISO 3166-1 alpha-2 country code';
DEFINE FIELD idDocumentType    ON beneficiary_kyc TYPE string
  ASSERT $value IN ['NATIONAL_ID', 'PASSPORT', 'DRIVING_LICENCE', 'VOTER_ID'];
DEFINE FIELD idDocumentNumber  ON beneficiary_kyc TYPE string;
DEFINE FIELD idDocumentCountry ON beneficiary_kyc TYPE string
  ASSERT $value IN ['PK', 'BD', 'NP', 'IQ'];
DEFINE FIELD kycProvider       ON beneficiary_kyc TYPE string
  COMMENT 'Primary KYC verification provider';
DEFINE FIELD kycStatus         ON beneficiary_kyc TYPE string
  ASSERT $value IN ['PENDING', 'VERIFIED', 'FAILED', 'EXPIRED', 'MANUAL_REVIEW'];
DEFINE FIELD riskRating        ON beneficiary_kyc TYPE string
  ASSERT $value IN ['LOW', 'MEDIUM', 'HIGH']
  DEFAULT 'MEDIUM';
DEFINE FIELD verifiedAt        ON beneficiary_kyc TYPE option<datetime>;
DEFINE FIELD expiresAt         ON beneficiary_kyc TYPE option<datetime>
  COMMENT 'KYC validity period — re-verification required after expiry';
DEFINE FIELD createdAt         ON beneficiary_kyc TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt         ON beneficiary_kyc TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_kyc_merchant
  ON beneficiary_kyc FIELDS merchantId;
DEFINE INDEX idx_kyc_document
  ON beneficiary_kyc FIELDS idDocumentNumber, idDocumentCountry;
DEFINE INDEX idx_kyc_status
  ON beneficiary_kyc FIELDS kycStatus, expiresAt;

-- ============================================================================
-- TABLE: corridor_config
-- Configuration per remittance corridor (source→destination pair)
-- ============================================================================

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

DEFINE FIELD id                  ON corridor_config TYPE string;
DEFINE FIELD sourceCurrency      ON corridor_config TYPE string
  ASSERT string::len($value) = 3;
DEFINE FIELD destCurrency        ON corridor_config TYPE string
  ASSERT string::len($value) = 3;
DEFINE FIELD sourceCountry       ON corridor_config TYPE string;
DEFINE FIELD destCountry         ON corridor_config TYPE string
  ASSERT $value IN ['PK', 'BD', 'NP', 'IQ'];
DEFINE FIELD corridorCode        ON corridor_config TYPE string
  COMMENT 'e.g. GBP_PKR, AED_BDT, USD_NPR';
DEFINE FIELD active              ON corridor_config TYPE bool
  DEFAULT true;
DEFINE FIELD minAmount           ON corridor_config TYPE decimal
  ASSERT $value >= 0;
DEFINE FIELD maxAmount           ON corridor_config TYPE decimal
  ASSERT $value > 0;
DEFINE FIELD dailyLimit          ON corridor_config TYPE decimal
  COMMENT 'Per-sender daily limit in source currency';
DEFINE FIELD monthlyLimit        ON corridor_config TYPE decimal
  COMMENT 'Per-sender monthly limit in source currency';
DEFINE FIELD defaultMarkupBps    ON corridor_config TYPE int
  DEFAULT 50
  COMMENT 'Default FX markup in basis points';
DEFINE FIELD deliveryMethods     ON corridor_config TYPE array<string>
  COMMENT 'Supported delivery methods for this corridor';
DEFINE FIELD requiresEDD         ON corridor_config TYPE bool
  DEFAULT false
  COMMENT 'Enhanced Due Diligence required for this corridor';
DEFINE FIELD rateProviders       ON corridor_config TYPE array<string>
  COMMENT 'Ordered list of FX rate sources for this corridor';
DEFINE FIELD quoteValidityMins   ON corridor_config TYPE int
  DEFAULT 30
  COMMENT 'How long an FX quote remains valid';
DEFINE FIELD regulatoryNotes     ON corridor_config TYPE option<string>
  COMMENT 'Corridor-specific regulatory requirements';
DEFINE FIELD createdAt           ON corridor_config TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt           ON corridor_config TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_corridor_code
  ON corridor_config FIELDS corridorCode UNIQUE;
DEFINE INDEX idx_corridor_dest
  ON corridor_config FIELDS destCountry, active;

-- ============================================================================
-- TABLE: compliance_record
-- Comprehensive compliance audit trail per remittance
-- ============================================================================

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

DEFINE FIELD id                ON compliance_record TYPE string;
DEFINE FIELD remittanceId      ON compliance_record TYPE string
  COMMENT 'References remittance.id';
DEFINE FIELD amlCheckId        ON compliance_record TYPE string
  COMMENT 'References aml_check.id';
DEFINE FIELD kycVerificationId ON compliance_record TYPE option<string>
  COMMENT 'References beneficiary_kyc.id';
DEFINE FIELD overallStatus     ON compliance_record TYPE string
  ASSERT $value IN ['PENDING', 'APPROVED', 'REJECTED', 'ESCALATED'];
DEFINE FIELD riskLevel         ON compliance_record TYPE string
  ASSERT $value IN ['LOW', 'MEDIUM', 'HIGH', 'CRITICAL'];
DEFINE FIELD sanctionsCleared  ON compliance_record TYPE bool
  DEFAULT false;
DEFINE FIELD pepCleared        ON compliance_record TYPE bool
  DEFAULT false;
DEFINE FIELD eddRequired       ON compliance_record TYPE bool
  DEFAULT false;
DEFINE FIELD eddCompleted      ON compliance_record TYPE bool
  DEFAULT false;
DEFINE FIELD approvedBy        ON compliance_record TYPE option<string>;
DEFINE FIELD approvalNotes     ON compliance_record TYPE option<string>;
DEFINE FIELD regulatoryRef     ON compliance_record TYPE option<string>
  COMMENT 'Regulatory reporting reference number';
DEFINE FIELD completedAt       ON compliance_record TYPE option<datetime>;
DEFINE FIELD createdAt         ON compliance_record TYPE datetime
  DEFAULT time::now();
DEFINE FIELD updatedAt         ON compliance_record TYPE datetime
  DEFAULT time::now()
  VALUE time::now();

DEFINE INDEX idx_compliance_remittance
  ON compliance_record FIELDS remittanceId UNIQUE;
DEFINE INDEX idx_compliance_status
  ON compliance_record FIELDS overallStatus;
DEFINE INDEX idx_compliance_risk
  ON compliance_record FIELDS riskLevel, overallStatus;