DATABASE_SCHEMA.md

Garuda Chain — Skema Persistence Data

Metadata
Versi dokumen	1.0.0
Storage saat ini	JSON files + in-memory cache
Target	PostgreSQL + Redis + S3 (staging first)
Terakhir diperbarui	2026-07-04

---

1. Ringkasan

Garuda Chain API belum menggunakan database relasional. Semua state aplikasi disimpan di:

1. JSON files di data/ dan chain/
2. In-memory cache di module-level (sync ke disk on write)
3. On-chain state di Besu + Anvil rescue

Dokumen ini memetakan skema saat ini dan skema target untuk migrasi bertahap.

---

2. Peta Storage Saat Ini

File	Env override	Modul API	Writable
data/garuda-pay-store.json	GARUDA_PAY_STORE_PATH	garudaPay/store.ts	✅
data/garuda-pay-fiat-requests.json	GARUDA_PAY_FIAT_STORE_PATH	garudaPay/fiatStore.ts	✅
data/garuda-pay-cs-tickets.json	GARUDA_PAY_CS_STORE_PATH	garudaPay/csStore.ts	✅
data/garuda-pay-cs-images/	GARUDA_PAY_CS_IMAGES_PATH	garudaPay/csAttachments.ts	✅
data/kyc-store.json	KYC_STORE_PATH	kyc/store.ts	✅
data/kyc-documents/	KYC_DOCS_PATH	kyc/documents.ts	✅
data/bridge-transfers.json	BRIDGE_TRANSFERS_PATH	bridge/store.ts	✅
chain/community/check-ins.json	—	community/rewardStore.ts	✅
chain/community/missions-progress.json	—	community/rewardStore.ts	✅
chain/community/referrals.json	—	community/rewardStore.ts	✅
chain/community/claims.json	—	community/claimEngine.ts	✅
chain/community/rewards-config.json	—	read-only config	❌
chain/community/missions-config.json	—	read-only config	❌
chain/community/referral-config.json	—	read-only config	❌
chain/validator-program/applications.json	—	routes/validators.ts	✅
chain/deployments/*.json	—	loadConfig.ts	❌ (deploy)
chain/bridge-config.json	—	bridge services	❌ (config)

In-memory only:

Store	Modul	TTL
Check-in nonce	community/checkInNonce.ts	5 menit
Garuda Pay cache	garudaPay/store.ts	Until reload
Bridge relay locks	bridge/store.ts	20 detik

---

3. Skema Detail — JSON Saat Ini

3.1 garuda-pay-store.json

{
  profiles: Record<address, GarudaPayProfile>;
  payments: Record<address, GarudaPayPayment[]>;
  platformMdrRevenueIdr?: number;
}

GarudaPayProfile:

{
  linkedSources: LinkedPaymentSource[];  // rail terhubung
  activeRailId: PaymentRailId | null;
  fiatBalanceIdr: number;
  gidrOnChain?: number;
  gidrEnabled?: boolean;
}

LinkedPaymentSource:

{
  railId: "visa" | "mastercard" | "qris" | "gopay" | "ovo";
  railName: string;
  category: "card" | "qris" | "ewallet";
  maskLast4: string;
  displayMask?: string;
  label?: string;
  linkedAt: number;          // unix ms
  chainRef?: string;
  gatewayRef?: string;
}

GarudaPayPayment:

{
  id: string;
  merchant: string;
  amountGdc: number;
  amountIdr: number;
  invoice: string;
  method: "garuda_pay" | "gdc_wallet";
  railId?: PaymentRailId;
  railName?: string;
  timestamp: number;
  status: "completed" | "pending" | "failed";
  txHash?: string;
  chainRef?: string;
  gatewayTxnId?: string;
  gidrTxHash?: string;
  settlementRail?: "idr" | "gdc";
  direction?: "out" | "in";
  payerAddress?: string;
  counterparty?: string;
  kind?: "merchant" | "transfer";
  grossIdr?: number;
  mdrIdr?: number;
  netIdr?: number;
}

---

3.2 garuda-pay-fiat-requests.json

{
  requests: FiatRequest[];
}

FiatRequest:

{
  id: string;
  type: "deposit" | "withdraw";
  address: string;              // 0x...
  amountIdr: number;
  transferAmountIdr?: number;   // deposit: nominal unik
  referenceCode: string;
  status: "pending" | "completed" | "rejected" | "expired";
  createdAt: number;
  expiresAt?: number;
  completedAt?: number;
  rejectedAt?: number;
  adminNote?: string;
  bankDestination?: {
    bankCode: string;
    bankName: string;
    accountNumber: string;
    accountName: string;
  };
  gidrTxHash?: string;
}

---

3.3 garuda-pay-cs-tickets.json

{
  tickets: CsTicket[];
}

CsTicket:

{
  id: string;
  address: string;
  topicId: string;              // CsTopicId
  topicLabel: string;
  status: "open" | "resolved";
  relatedRequestId?: string;
  createdAt: number;
  updatedAt: number;
  resolvedAt?: number;
  messages: CsMessage[];
}

CsMessage:

{
  id: string;
  from: "user" | "admin";
  body: string;
  createdAt: number;
  hasImage?: boolean;
}

Binary: gambar di data/garuda-pay-cs-images/{ticketId}/{messageId}.jpg

---

3.4 kyc-store.json

{
  records: Record<address, KycSubmission>;
}

KycSubmission:

{
  address: string;
  status: "none" | "pending" | "verified" | "rejected" | "suspended";
  fullName: string;
  dob: string;                  // YYYY-MM-DD
  nationality: string;
  idType: string;
  idNumber: string;
  kycHash: string;              // keccak256 hash
  level: 2;
  submittedAt: number;
  reviewedAt?: number;
  reviewedBy?: string;
  rejectReason?: string;
  onChainLevel?: string;
  documents?: KycDocumentMeta;
  resubmitCount?: number;
  previousRejectReason?: string;
}

Binary: data/kyc-documents/{address}/{kind}.jpg (max 3 MB per file)

---

3.5 bridge-transfers.json

{
  transfers: BridgeTransferRecord[];
  processedInboundTx: string[];  // lowercase tx hashes
}

BridgeTransferRecord (dari @garuda-chain/sdk):

{
  transferId: string;
  direction: "outbound" | "inbound";
  status: string;               // stage dari bridgeStages
  sender: string;
  recipient: string;
  amount: string;
  token: string;
  sourceChainId: number;
  destChainId: number;
  sourceTxHash?: string;
  destTxHash?: string;
  createdAt: number;
  updatedAt: number;
  // ... field tambahan per stage
}

---

3.6 chain/community/check-ins.json

{
  checkIns: Record<address, RewardAccount>;
}

RewardAccount:

{
  lastCheckInAt: string;        // ISO date
  streak: number;
  maxStreak: number;
  pendingGdcMicro: number;
  totalCheckIns: number;
  pendingBreakdown?: {
    checkIn: number;
    missions: number;
    referral: number;
  };
  claimedOnChainBreakdown?: {
    checkIn: number;
    missions: number;
    referral: number;
  };
}

---

3.7 chain/community/missions-progress.json

{
  accounts: Record<address, MissionProgress>;
}

MissionProgress:

{
  completed: string[];          // mission IDs
  completedAt: Record<missionId, ISO string>;
  creditedMicro?: Record<missionId, number>;
  withdrawnMicro?: Record<missionId, number>;
}

---

3.8 chain/community/referrals.json

{
  codes: Record<code, referrerAddress>;
  bindings: Record<refereeAddress, ReferralBinding>;
  referrerStats: Record<referrerAddress, ReferrerStats>;
}

ReferralBinding:

{
  referrer: string;
  code: string;
  boundAt: string;
  qualifiedAt?: string;
  referrerRewarded: boolean;
  refereeRewarded: boolean;
}

ReferrerStats:

{
  totalQualified: number;
  monthlyQualified: Record<"YYYY-MM", number>;
  totalEarningsMicro: number;
}

---

3.9 chain/community/claims.json

Menyimpan reservasi klaim on-chain (nonce, voucher EIP-712, status).

---

3.10 chain/validator-program/applications.json

Aplikasi validator komunitas (submitted via public POST).

---

4. Storage Klien (Wallet)

Bukan bagian API, tetapi relevan untuk schema design:

Key	Storage	Isi
garuda_wallet_v1	localStorage	Encrypted wallet blob
garuda_wallet_unlock_v1	sessionStorage	Plaintext { address, privateKey } 12h
garuda_gnft_v2_{address}	localStorage	GNFT cache (stats, tokens)
garuda_gnft_market_v1	sessionStorage	Market listings cache
garuda_tx_log_{address}	localStorage	Transaction history
garuda_balance_{address}	localStorage	Balance cache

---

5. On-Chain State (Referensi)

Kontrak	State utama
GDC	balances, allowances, totalSupply
GIDR	balances (custodial mint/burn)
PayHub	payments, escrows, merchant registry
Staking	stakes, rewards, lock periods
NFT Marketplace	listings, ownership, tokenURI
Bridge V2	outboundLocked, inboundProcessed, daily limits
Identity	KYC level per address
CommunityRewardClaim	nonces, claimed amounts
ValidatorRegistry	validators, stakes, slashes

Registry: chain/deployments/mainnet.json

---

6. Skema Target — PostgreSQL

Status: Rencana. Implementasi hanya di staging (apps/api-v2).

6.1 Core Tables

-- Users (wallet-centric identity)
CREATE TABLE users (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  address       VARCHAR(42) NOT NULL UNIQUE,  -- lowercase 0x...
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Garuda Pay profiles
CREATE TABLE garuda_pay_profiles (
  user_id           UUID PRIMARY KEY REFERENCES users(id),
  active_rail_id    VARCHAR(20),
  fiat_balance_idr  BIGINT NOT NULL DEFAULT 0,
  gidr_on_chain     BIGINT DEFAULT 0,
  gidr_enabled      BOOLEAN DEFAULT false,
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE garuda_pay_linked_sources (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(id),
  rail_id       VARCHAR(20) NOT NULL,
  mask_last4    VARCHAR(8),
  display_mask  VARCHAR(32),
  label         VARCHAR(64),
  linked_at     TIMESTAMPTZ NOT NULL,
  chain_ref     VARCHAR(66),
  gateway_ref   VARCHAR(128),
  UNIQUE(user_id, rail_id)
);

CREATE TABLE garuda_pay_payments (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         UUID NOT NULL REFERENCES users(id),
  external_id     VARCHAR(64) NOT NULL UNIQUE,  -- legacy payment id
  merchant        VARCHAR(128),
  amount_gdc      NUMERIC(36,18),
  amount_idr      BIGINT,
  invoice         VARCHAR(64),
  method          VARCHAR(20),
  status          VARCHAR(20) NOT NULL,
  tx_hash         VARCHAR(66),
  kind            VARCHAR(20),
  direction       VARCHAR(4),
  created_at      TIMESTAMPTZ NOT NULL,
  metadata        JSONB
);
CREATE INDEX idx_payments_user ON garuda_pay_payments(user_id, created_at DESC);

-- KYC
CREATE TABLE kyc_records (
  user_id         UUID PRIMARY KEY REFERENCES users(id),
  status          VARCHAR(20) NOT NULL,
  full_name       VARCHAR(128) NOT NULL,
  dob             DATE,
  nationality     VARCHAR(64),
  id_type         VARCHAR(32),
  id_number_hash  VARCHAR(66) NOT NULL,  -- hash only in DB
  kyc_hash        VARCHAR(66),
  level           SMALLINT NOT NULL DEFAULT 2,
  submitted_at    TIMESTAMPTZ,
  reviewed_at     TIMESTAMPTZ,
  reviewed_by     VARCHAR(64),
  reject_reason   TEXT,
  on_chain_level  VARCHAR(20),
  resubmit_count  SMALLINT DEFAULT 0
);

-- KYC documents → S3, not DB blob
CREATE TABLE kyc_documents (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     UUID NOT NULL REFERENCES users(id),
  kind        VARCHAR(32) NOT NULL,
  s3_key      VARCHAR(256) NOT NULL,
  uploaded_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(user_id, kind)
);

-- Community check-ins
CREATE TABLE community_check_ins (
  user_id           UUID PRIMARY KEY REFERENCES users(id),
  last_check_in_at  DATE,
  streak            INT NOT NULL DEFAULT 0,
  max_streak        INT NOT NULL DEFAULT 0,
  pending_micro     BIGINT NOT NULL DEFAULT 0,
  total_check_ins   INT NOT NULL DEFAULT 0,
  pending_breakdown JSONB,
  claimed_breakdown JSONB,
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Bridge transfers
CREATE TABLE bridge_transfers (
  transfer_id       VARCHAR(66) PRIMARY KEY,
  direction         VARCHAR(10) NOT NULL,
  status            VARCHAR(32) NOT NULL,
  sender            VARCHAR(42) NOT NULL,
  recipient         VARCHAR(42) NOT NULL,
  amount            NUMERIC(36,18),
  token             VARCHAR(42),
  source_chain_id   INT,
  dest_chain_id     INT,
  source_tx_hash    VARCHAR(66),
  dest_tx_hash      VARCHAR(66),
  created_at        TIMESTAMPTZ NOT NULL,
  updated_at        TIMESTAMPTZ NOT NULL,
  metadata          JSONB
);
CREATE INDEX idx_bridge_sender ON bridge_transfers(sender);
CREATE INDEX idx_bridge_recipient ON bridge_transfers(recipient);

6.2 Redis (Cache & Ephemeral)

Key pattern	TTL	Fungsi
checkin:nonce:{address}	5m	Check-in nonce
rpc:health:{url}	15s	RPC probe result
gnft:listings	30s	Marketplace cache
bridge:relay:lock:{id}	20s	Relay dedup

---

7. Strategi Migrasi

Fase 1: Dual-write (JSON + PostgreSQL) di staging
Fase 2: Read dari PostgreSQL, write ke both
Fase 3: Read/write PostgreSQL only, JSON sebagai backup export
Fase 4: Production cutover modul per modul (Garuda Pay → KYC → Community → Bridge)

Rollback: JSON files tetap di-backup sebelum cutover.

---

8. Risiko Storage Saat Ini

Risiko	Dampak	Mitigasi (roadmap)
writeFileSync tanpa lock	Corrupt JSON concurrent write	PostgreSQL transactions
No backup automation	Data loss	prod:backup + tested restore
PII di plain JSON	Compliance risk	Encrypt at rest, S3 + KMS
No query/index	Slow list operations	SQL indexes
Single VPS disk	SPOF	Replicated DB

---

9. Referensi

• Implementasi: apps/api/src/garudaPay/, kyc/, community/, bridge/
• Types: apps/api/src/garudaPay/types.ts, kyc/types.ts
• REFACTOR_PLAN.md — Fase migrasi DB
• API_DOCUMENTATION.md