Database Architecture

Part of: MPAC SmartPOS Cloud Platform - Product RequirementsVersion: 2.0 Last Updated: 2026-01-28

---

Overview

This document defines the database architecture for the MPAC platform, including ownership boundaries, schema design patterns, and data synchronization mechanisms. The architecture follows a service-oriented approach where each service owns its domain data, with explicit synchronization patterns for shared entities. The design emphasizes multi-tenancy isolation, type safety through hybrid enum handling, and operational flexibility for schema migrations.

Table of Contents

• Database Ownership
• Schema Design Patterns
• Data Synchronization

---

Database Ownership

Purpose: Clear boundaries for data ownership and responsibility across services.

svc-portal: Source of Truth for Core Entities

Responsibility: Identity, authentication, and merchant hierarchy management.

Owned Tables:

• PSPs - Payment service providers
• Merchants - Merchant organizations
• Stores - Physical store locations
• Devices - SmartPOS terminal registration
• Users - Portal user accounts
• JWT Keys - Public/private keypairs for JWT signing
• MFA Codes - Multi-factor authentication codes
• Authentication Logs - Audit trail for login attempts
• User Sessions - Active session tracking
• Impersonation Logs - Admin impersonation audit trail

Key Characteristics:

• Single source of truth for merchant hierarchy
• All services reference these entities
• Changes propagate to other services via sync API
• Strict referential integrity enforced

---

svc-smarttab: POS Operations

Responsibility: Order, billing, and payment transaction management.

Owned Tables:

• Orders - Customer orders
• Bills - Finalized bills for checkout
• Payments - Payment transaction records
• Customers - Customer profiles
• Settlements - Daily settlement batches
• Card Slips - Credit card transaction records
• Receipts/Invoices - Digital receipt records

Key Characteristics:

• High write volume (80M transactions/day)
• Time-series partitioning for large tables
• Foreign key references to svc-portal entities (merchant_id, store_id, device_id)
• Eventual consistency acceptable for reporting

---

mpac-pgw: Payment Gateway

Responsibility: Payment processing, provider integrations, and webhooks.

Owned Tables:

• PaymentIntents - Payment intent lifecycle
• Payment Transactions - Provider-specific transaction records
• Client Partners - Mirror of PSPs from svc-portal
• Merchants - Mirror of merchants (synced from svc-portal)
• Stores - Mirror of stores (synced from svc-portal)
• StoreProviderConfigs - Payment provider configurations per store

Key Characteristics:

• Reads merchant/store data from local mirror
• Synced from svc-portal every 15 minutes
• No foreign key constraints to external databases
• Idempotency key cache (Redis, 24h TTL)

---

Schema Design Patterns

Purpose: Consistent patterns for multi-tenancy, enums, and performance optimization.

Multi-Tenancy Isolation

Pattern: All operational tables include tenant scoping columns for row-level isolation.

Schema Example:

CREATE TABLE bills (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  merchant_id INT NOT NULL REFERENCES merchants(id),
  store_id INT NOT NULL REFERENCES stores(id),
  device_id UUID REFERENCES devices(device_uuid),
  bill_number TEXT NOT NULL,
  total_amount DECIMAL(15, 2) NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('open', 'closed', 'voided')),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes for tenant queries
CREATE INDEX idx_bills_merchant_store ON bills(merchant_id, store_id);
CREATE INDEX idx_bills_store_status ON bills(store_id, status);
CREATE INDEX idx_bills_device ON bills(device_id);

Query Pattern (enforced by middleware):

-- All queries MUST filter by tenant scope
SELECT * FROM bills
WHERE merchant_id = :merchant_id
  AND store_id = :store_id
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Key Features:

• Automatic tenant filtering in ORM/middleware
• Prevents cross-tenant data leakage
• Efficient indexes for tenant-scoped queries
• Supports multi-store merchants

---

Enum Handling (Hybrid Approach)

Pattern: Use TEXT columns with CHECK constraints in database, with strongly-typed enums in application code.

Database Schema:

CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status TEXT NOT NULL CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'refunded')),
  payment_method TEXT NOT NULL CHECK (payment_method IN ('cash', 'credit_card', 'qr_code', 'e_wallet')),
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

Application Code (Python/SQLAlchemy):

from enum import Enum
from sqlalchemy import Column, Enum as SQLEnum, Text
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class PaymentStatus(str, Enum):
    PENDING = "pending"
    PROCESSING = "processing"
    COMPLETED = "completed"
    FAILED = "failed"
    REFUNDED = "refunded"

class PaymentMethod(str, Enum):
    CASH = "cash"
    CREDIT_CARD = "credit_card"
    QR_CODE = "qr_code"
    E_WALLET = "e_wallet"

class Payment(Base):
    __tablename__ = "payments"

    id = Column(Text, primary_key=True)
    status = Column(SQLEnum(PaymentStatus, native_enum=False), nullable=False)
    payment_method = Column(SQLEnum(PaymentMethod, native_enum=False), nullable=False)

Application Code (Go/GORM):

package models

// Constants for type safety
const (
    PaymentStatusPending    = "pending"
    PaymentStatusProcessing = "processing"
    PaymentStatusCompleted  = "completed"
    PaymentStatusFailed     = "failed"
    PaymentStatusRefunded   = "refunded"
)

type Payment struct {
    ID            string `gorm:"primaryKey;type:uuid;default:gen_random_uuid()"`
    Status        string `gorm:"type:text;not null;check:status IN ('pending', 'processing', 'completed', 'failed', 'refunded')"`
    PaymentMethod string `gorm:"type:text;not null"`
    CreatedAt     time.Time
}

Benefits:

• Database: Easy migrations (just add to CHECK constraint)
• Code: Type safety and IDE autocomplete
• No Native ENUM: Avoids PostgreSQL ENUM migration complexities
• Backwards Compatible: New values don't break old code
• Safe Refactoring: Rename enum values without ALTER TYPE CASCADE

Migration Example:

-- Adding new enum value (simple ALTER TABLE)
ALTER TABLE payments
  DROP CONSTRAINT payments_status_check,
  ADD CONSTRAINT payments_status_check
    CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'refunded', 'disputed'));

-- No need for ALTER TYPE CASCADE (would be required with native ENUM)

---

Indexing Strategy

Pattern: Indexes optimized for multi-tenant queries and common filter patterns.

Common Index Patterns:

-- Tenant-scoped queries (most common)
CREATE INDEX idx_{table}_merchant_store ON {table}(merchant_id, store_id);

-- Status filters (frequent)
CREATE INDEX idx_{table}_status ON {table}(status) WHERE status IN ('active', 'pending');

-- Time-range queries (reports)
CREATE INDEX idx_{table}_created_at ON {table}(created_at DESC);

-- Composite for complex filters
CREATE INDEX idx_bills_store_status_created ON bills(store_id, status, created_at DESC);

-- Foreign key lookups
CREATE INDEX idx_payments_bill_id ON payments(bill_id);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Index Maintenance:

• Monitor index usage with pg_stat_user_indexes
• Drop unused indexes (< 100 scans/month)
• REINDEX monthly for high-write tables
• ANALYZE after bulk operations

---

Partitioning Strategy

Pattern: Time-based partitioning for high-volume transactional tables.

Partitioned Tables:

-- Parent table (partitioned by month)
CREATE TABLE payment_transactions (
  id UUID NOT NULL,
  payment_intent_id TEXT NOT NULL,
  provider_name TEXT NOT NULL,
  amount DECIMAL(15, 2) NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Monthly partitions (auto-created by pg_partman)
CREATE TABLE payment_transactions_2026_01 PARTITION OF payment_transactions
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE payment_transactions_2026_02 PARTITION OF payment_transactions
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

Partitioned Tables List:

• payment_transactions - 80M+ rows/month
• authentication_logs - High write volume
• webhook_events - Retention 90 days

Retention Policy:

• Keep 12 months of partition data
• Archive older partitions to S3 (Parquet format)
• Drop partitions older than 24 months

---

Data Synchronization

Purpose: Keep mirror tables in mpac-pgw consistent with source of truth in svc-portal.

Merchant/Store Config Sync

Trigger: Real-time push from svc-portal after CREATE/UPDATE operations.

Flow:

svc-portal (CREATE/UPDATE merchant/store)
  └─ After successful commit
  └─ POST /v1/sync/merchants (HMAC auth)
     Headers:
       Authorization: HMAC-SHA256 svc-portal:<timestamp>:<signature>
       Content-Type: application/json
     Body: {
       client_partner_id: 1,
       merchants: [
         {
           merchant_id: 1,
           merchant_code: "MERCHANT001",
           name: "Example Restaurant Group",
           stores: [
             {
               store_id: 1,
               store_code: "STORE001",
               name: "Shibuya Branch",
               provider_configs: [
                 {
                   provider_name: "paypay",
                   merchant_id: "paypay_merchant_123",
                   api_key_encrypted: "...",
                   enabled: true
                 }
               ]
             }
           ]
         }
       ]
     }

mpac-pgw receives sync
  └─ UPSERT merchants table
  └─ UPSERT stores table
  └─ UPSERT store_provider_configs table
  └─ Return: {synced_merchants: 1, synced_stores: 2}

Sync API Endpoint:

# mpac-pgw sync endpoint
@router.post("/v1/sync/merchants")
async def sync_merchants(
    request: MerchantSyncRequest,
    auth: HMACAuth = Depends(verify_hmac)
):
    """
    Sync merchant/store data from svc-portal.
    This is the real-time push endpoint.
    """
    for merchant_data in request.merchants:
        # UPSERT merchant
        await db.execute(
            """
            INSERT INTO merchants (merchant_id, merchant_code, name, client_partner_id)
            VALUES (:id, :code, :name, :partner_id)
            ON CONFLICT (merchant_id) DO UPDATE SET
              merchant_code = EXCLUDED.merchant_code,
              name = EXCLUDED.name,
              updated_at = NOW()
            """,
            {
                "id": merchant_data.merchant_id,
                "code": merchant_data.merchant_code,
                "name": merchant_data.name,
                "partner_id": request.client_partner_id
            }
        )

        # Sync stores and provider configs
        for store_data in merchant_data.stores:
            await sync_store(store_data)

    return {"synced_merchants": len(request.merchants)}

---

Fallback: Periodic Sync

Purpose: Ensure eventual consistency in case real-time sync fails.

Schedule: Every 15 minutes via cron job in svc-portal.

Flow:

# svc-portal periodic sync job
@cron("*/15 * * * *")  # Every 15 minutes
async def periodic_sync_to_pgw():
    """
    Fallback sync: push all merchants/stores with recent updates.
    """
    last_sync_time = await redis.get("pgw_sync:last_sync_time")

    # Find merchants/stores updated since last sync
    merchants = await db.query(
        """
        SELECT m.*, array_agg(s.*) as stores
        FROM merchants m
        LEFT JOIN stores s ON s.merchant_id = m.id
        WHERE m.updated_at > :last_sync
           OR s.updated_at > :last_sync
        GROUP BY m.id
        """,
        {"last_sync": last_sync_time}
    )

    if merchants:
        await pgw_client.sync_merchants(merchants)
        await redis.set("pgw_sync:last_sync_time", datetime.now())

Error Handling:

• Retry with exponential backoff (3 attempts)
• Alert on persistent sync failures
• Dead letter queue for failed sync requests
• Manual sync endpoint for recovery

---

Consistency Guarantees

Real-time Sync:

• Best-effort delivery
• Target: < 5 seconds latency
• Retries on transient failures

Periodic Sync:

• Eventual consistency guarantee
• Max drift: 15 minutes
• Full reconciliation on sync

Monitoring:

• Track sync latency in Prometheus
• Alert if sync lag > 5 minutes
• Dashboard for sync health status

---

Cross-References

Related Domains

• Merchant Hierarchy - PSP/Merchant/Store models
• Device Management - Device registration and binding
• Payment Gateway - PaymentIntent and transaction models

Related Technical Sections

• Communication Patterns - Sync API authentication
• Security Architecture - Data encryption at rest
• Performance & Scalability - Database optimization

Related Appendix

• Data Model Diagrams - Complete ERD
• Migration Guide - Schema evolution procedures
• Database Maintenance - Backup and recovery procedures

---

Navigation

Previous: Communication PatternsNext: Security ArchitectureUp: Technical Architecture Index