Database Views — Classification & Registry

Reference document for all database VIEWs (regular & materialized) in the Kesles Merchant database cluster. Defines who may consume which view, where the view is defined, and how to add a new view.

Scope note (2026-06-03): dokumen ini terutama mencakup views di db_kesles_merchant. Database baru (db_kesles_merchant_payment, db_kesles_merchant_partner, db_kesles_merchant_order) memiliki views di migrasinya masing-masing (mig 003_*_views.sql). Registry §4 akan diperluas saat views tersebut menjadi aktif dikonsumsi.

See also: database/schema/schema.md for table overview, partner-access-scope-plan.md for the HMAC + DB role partner mechanism.

1. 4-tier consumer classification

Every view in the project serves one of the four consumer tiers below. Adding a new view must map itself to one of the tiers — if it doesn't fit, the design likely needs rethinking.

#	Tier	Consumer	Auth	Routing column	View schema
1	PSP Internal	`payment.kesles.com` (Kesles itself)	DB role + HMAC	`partner.partners.psp_role = 'internal'`	`partner.v_partner_*_psp_internal`
2	PSP External	Bank / Acquirer (Mandiri, BRI, BNI, …)	DB role + HMAC	`partner.partners.psp_role = 'bank'`, `psp_bank_code IN ('BMRI','BRIN','BBNI', …)`	`partner.v_partner_*_psp_bank`, `psp.v_active_merchants`
3	Partner (non-PSP)	Seller / Agency / Affiliate / Community / Reseller (Joob, Ampersand, etc.)	API HMAC `/api/partner/v1/*`	`partner.partners.psp_role = 'none'`, `partner_type ∈ {reseller, agency, affiliate, community, sales, other}`	`partner.v_partner_*_public`, MV `merchant.v_partner_merchant_performance_30d`
4	Merchant UMKM	`merchant_dashboard` & `mobile_partner` apps (end-user UMKM single-outlet)	JWT user auth	none — query self-data from `merchant.merchants`	`merchant.v_*` (internal, plain view)
5	Merchant Enterprise (planned)	Phoenixdart etc. — multi-outlet, multi-user, ERP-integrated	JWT user (hosted dashboard) or HMAC `partner.api_credentials` (ERP/BI integration)	`merchant.merchants.merchant_tier='enterprise'` + `partner.partners.linked_merchant_id`	`merchant.v_outlet_daily_summary` (planned mig 099)

The Kesles operator (internal admin dashboard, /internal/dashboard/*) is not a new tier — its data sources are identical to Tier 4 (merchant.* tables/views), the only difference is the filter (admin does not filter by merchant_id).

Important implications

Tier 1 & 2 (PSP) views are B2B data contracts — column changes must be coordinated with the PSP partner. Never DROP or rename fields without prior notice.
Tier 3 & 4 views are internal optimizations — free to change/remove as long as the Go consumers are updated in the same PR.
Schemas are always prefixed — put internal views in merchant.*, public PSP/Partner views in partner.* or psp.*. Don't mix.

2. Naming convention & location

<schema>.v_<entity>[_<aggregation_or_filter>]

Examples:

Pattern	Tier	Example
`merchant.v_<entity>_<agg>`	4 + operator	`merchant.v_merchant_daily_summary`
`merchant.v_<entity>_<window>` (MV)	3	`merchant.v_partner_merchant_performance_30d`
`partner.v_partner_<entity>_<role>`	1, 2, 3	`partner.v_partner_transactions_psp_bank`
`psp.v_<entity>`	2	`psp.v_active_merchants`

DDL location: semua views didefinisikan di merchant_database/<db_name>/migrations/v1/<NNN>_*.sql. Tidak ada view yang dibuat ad-hoc atau via ORM.

Views di db_kesles_merchant: merchant_database/db_kesles_merchant/migrations/v1/
Views di db_kesles_merchant_payment: merchant_database/db_kesles_merchant_payment/migrations/v1/003_payment_views.sql
Views di db_kesles_merchant_partner: merchant_database/db_kesles_merchant_partner/migrations/v1/003_partner_views.sql

3. Decision tree: regular VIEW vs MATERIALIZED VIEW vs raw query

Is the query always filtered by `WHERE merchant_id = $1` (or equivalent)
with an existing index?
├── Yes
│   └── Is the join/aggregation pattern duplicated in ≥2 callsites?
│       ├── Yes → REGULAR VIEW (deduplicate the pattern, planner inlines it)
│       └── No  → leave as raw query, no view needed
│
└── No (full table scan / large range / all-time aggregate)
    └── Can the data be up to 1×24 hours stale?
        ├── Yes → MATERIALIZED VIEW + daily cron refresh
        └── No  → DO NOT make a view; redesign the query (pre-aggregate
                  in a batch table, or cache at the app layer with TTL)

A plain view does not hide query cost — if the original query is slow, the view is also slow. Plain views are useful for pattern consistency, not pure optimization.

4. Existing view registry

4.1 `partner.` & `psp.` schemas — B2B data contract (Tiers 1, 2, 3)

DB note (2026-06-03): views psp.* dan partner.* yang ada di db_kesles_merchant adalah versi aktif yang sedang dipakai handlers. db_kesles_merchant_payment (mig 003) dan db_kesles_merchant_partner (mig 003) sudah memiliki view DDL yang siap, tetapi handlers belum dimigrasikan ke sana. Jangan pindahkan handler sebelum ada keputusan eksplisit service extraction.

Grouped together because the audience is all external/handler-routed via psp_role. Access control is not via app filter; it's via view-level field exposure (see header of mig 085).

View	Tier	DDL source	Consumer status	Notes
`psp.v_active_merchants`	1 (Internal)	mig 011 / 102	✅ Actively used — `/api/psp/v1/merchants/*` in `services/dashboard_api/internal/app/psp_merchants.go`	Single-tenant view; currently serves payment.kesles.com only
`partner.v_partner_merchants_public`	3	mig 085	❌ Not yet used by Go API	Schema ready, no handler routing yet
`partner.v_partner_merchants_psp_bank`	2	mig 085	❌ Not yet used	Schema ready for Mandiri/BRI/BNI; no handler yet
`partner.v_partner_merchants_psp_internal`	1	mig 085	❌ Not yet used	Schema ready; handler still on `psp.v_active_merchants`
`partner.v_partner_transactions_public`	3	mig 085	❌ Not yet used	—
`partner.v_partner_transactions_psp_bank`	2	mig 085	❌ Not yet used	—
`partner.v_partner_transactions_psp_internal`	1	mig 085	❌ Not yet used	—
`partner.v_partner_monthly_settlement`	3	mig 082	❌ Empty audit	For future reporting

PSP routing status (current):

Tier 1 (PSP Internal) ✅ active via 9 /api/psp/v1/* endpoints + /api/psp/v1/payment-events/*. Dual-source auth: psp.api_keys (legacy single-tenant) or partner.api_credentials with partner.partners.psp_role='internal'.

Tier 2 (PSP Bank) ✅ handler ready. Onboarding only requires issuing a credential in partner.api_credentials (auth_method='hmac', FK to partner.partners with psp_role='bank' + psp_bank_code). Middleware automatically attaches role+bank_code to the request context, the handler injects WHERE bank_code = $X on every list/search/detail endpoint /api/psp/v1/merchants/*. Tier-1-only endpoints (nmid-assignment, payment-events/*) reject 403 for Tier 2.

Tier 3 (Partner non-PSP) ✅ active via /api/partner/v1/* in merchant_core_api. Uses the merchantScopeFragment 4-mode (own_referral / explicit / partner_network / global), not the partner.v_partner_*_public view. That view remains a dead schema; it can be used in the future if a partner accesses directly through the schema role.

The dual-source PSP auth, mandatoryBankCode row-level filter, and requirePSPInternal Tier-1 gate are implemented in services/dashboard_api/internal/app/psp_auth_middleware.go, psp_merchants.go, and psp_event_receiver.go. See §4.5 for the per-item summary.

4.2 `merchant.*` schema Tier 3 — partner-side aggregation

View	Type	DDL source	Go consumer
`merchant.v_partner_merchant_performance_30d`	MATERIALIZED	mig 050	`partner/portfolio.go` (8 queries, see lines 151, 169, 293, 311, 493, 556, 664)
`merchant.v_device_current_wac`	regular	mig 059	`merchant/device_store.go`
`merchant.v_device_wac_breakdown`	regular	mig 059 / 077	reporting
`merchant.v_sales_quotations_with_cost`	regular	mig 041 / 059 / 075 / 076	sales quotation flow

4.3 `merchant.*` schema Tier 4 — Merchant app + internal operator

View	Type	DDL source	Target Go consumer
`merchant.v_transaction_with_fee`	regular	mig 095	`merchant/transaction.go` (`listMerchantTransactions`, `listMerchantRecentTransactions`), `partner/store.go` (`listTransactions`)
`merchant.v_merchant_daily_summary`	regular	mig 095	`merchant/transaction.go` (`getMerchantTransactionSummary`), `merchant/homedashboard.go` (`getMerchantHoldingBalanceTotal`, `getMerchantWeeklyGrossPoints`), `partner/store.go` (`getTransactionSummary`), `internaldashboard/store.go` (`queryTodayTransactionMetrics`)
`merchant.v_user_active_merchant`	regular	mig 097	`merchant/transaction.go` (`getUserMerchantContext`), `paymentterminals/store.go` (`getUserMerchantContext`). Does not replace `profile/store.go` (its column scope differs significantly).

Mig 095, 096 (drop payment_terminal_psp_pairings), and 097 are independent — they can be applied without strict ordering. The views in mig 095 / 097 don't reference tables/columns dropped in mig 096.

The Go refactor to use mig 095 is a separate PR, done after the view stabilizes in staging.

4.5 Tier 2 (PSP Bank) onboarding checklist

Schema ✅ DONE

partner.partners.psp_role enum (none/bank/internal) — mig 087
partner.partners.psp_bank_code (mandatory if role=bank) — mig 087
partner.api_credentials.hmac_secret_encrypted + allowed_ip_ranges — mig 087
6 partner.v_partner_*_psp_{bank,internal,public} views — mig 085 (currently optional, handler uses psp.v_active_merchants + WHERE filter)

Backend ✅ DONE

pspAuthMiddleware dual-source — psp.api_keys + partner.api_credentials
psp_role + psp_bank_code attached to request context via PSPAuthContext
List/search/detail handler (/api/psp/v1/merchants/*) injects WHERE bank_code = $X if role=bank — via mandatoryBankCodeFromContext helper
Tier-1-only gate (requirePSPInternal) on:
- PATCH /api/psp/v1/merchants/{id}/nmid-assignment
- POST /api/psp/v1/payment-events/* (4 sub-routes)
markPSPKeyUsed updates the table according to the source (psp.api_keys vs partner.api_credentials)

Credential issuance policy (as of 2026-04-30) ✅ DONE

All new credentials in partner.api_credentials must be HMAC: auth_method='hmac' + hmac_secret_encrypted populated.
createPartnerCredential & rotatePartnerCredential in services/dashboard_api/internal/app/dashboard_partner_credentials.go are HMAC-only — generate plaintext, encrypt via EncryptSecret, INSERT/UPDATE with auth_method='hmac'.
Operators only see plaintext once in the create/rotate response; hand off to the partner via a secure channel (the key is not stored again on the server).
(Backlog) Migrate / rotate existing client_credentials credentials that are still active. Currently only JOOB has 1 active client_credentials row — needs manual rotation via the dashboard so it auto-upgrades to HMAC. The /api/partner/v1/auth/token endpoint (legacy OAuth flow) will reject already-HMAC credentials (their auth_method has changed).

Operations (pending)

End-to-end integration test in staging:
- Insert sample partner.partners (psp_role='bank', psp_bank_code='BMRI')
- Insert sample partner.api_credentials (auth_method='hmac', credential_key='psp-bmri-test', hmac_secret_encrypted via psp-encrypt CLI)
- Curl /api/psp/v1/merchants → must only return merchants with bank_code='BMRI'
- Curl PATCH /api/psp/v1/merchants/{id}/nmid-assignment → must 403 (tier_internal_required)
- Curl POST /api/psp/v1/payment-events/transaction → must 403
Onboarding runbook: how to issue credentials (CLI or dashboard), per-bank IP allowlist setup, rotation procedure
Set CIDR allowed_ip_ranges per bank — coordinate with the acquirer network team

Documentation (pending)

psp-integration-contract.md — refactor to multi-tenant (the status block already exists, the contract body still explicitly mentions payment.kesles.com as a single consumer)
Public onboarding guide for PSP Bank in merchant_docs/api_docs/public/docs/guidelines/getting-started/
Postman collection with separate environment per role in merchant_docs/api_docs/internal/static/postman/

Future enhancement (optional, non-blocking)

Migrate the handler from psp.v_active_merchants to the 3 partner.v_partner_merchants_* views (mig 085) so that field exposure differs per tier (e.g. hide internal MDR breakdown from Tier 2). Currently security uses row-level filter; field-level is identical across tiers.
Per-role rate limit in middleware

4.4 Performance baseline & optimization notes

EXPLAIN ANALYZE on dev (empty DB, 0 transactions — numbers reflect structural view overhead, not real data load):

View / pattern	Old (raw)	New (view)	Δ	Plan
`v_transaction_with_fee` — list 100 rows with service_fee	~0.07 ms	0.066 ms	~0%	Identical. `idx_transactions_merchant_time` + Nested Loop Left Join. Planner inlines the view.
`v_merchant_daily_summary` — 7-day summary	0.230 ms	0.494 ms	+115%	View uses 2× GroupAggregate + Merge Full Join. The `transaction_date_wib` filter is not pushed down to the `transaction_at` index because the date-WIB column is computed.
`v_user_active_merchant` — context per user	~1 ms (10 subqueries × ~0.1 ms est.)	0.65 ms	~−35%	Single Nested Loop with Memoize cache. Uses `merchant_users_pkey` + `idx_merchant_registration_requests_user_id`.

Safe for prod

Views #1 and #3: net neutral or faster. Safe to ship straight to prod.

Needs observation: view #2

Risk: the transaction_date_wib filter is not pushed down. If a merchant has many transactions in a wide window (e.g. all-time scan in getMerchantHoldingBalanceTotal), view #2 GROUPs BY the full dataset before filtering by date. Estimate: scales with row count per merchant.

Mitigations (priority order, do them only if prod proves slow):

Double filter on the Go side — add AND transaction_at >= $start_ts AND transaction_at < $end_ts to the Go query that uses view #2. But view #2 currently does not expose transaction_at (already aggregated). Need to redefine view #2 to expose transaction_at_min, transaction_at_max, or switch the caller to view #1 + inline SUM/COUNT (trade-off: fee-only days without transactions will be skipped — need to confirm with business whether that scenario is material).
IMMUTABLE expression index wrapper — create a function merchant.to_wib_date(t timestamptz) RETURNS date IMMUTABLE then CREATE INDEX ON merchant.transactions (merchant_id, merchant.to_wib_date(transaction_at)). Asia/Jakarta has no DST → semantically immutable, but the IMMUTABLE marker is a slight lie to the planner. Risk: if the Asia/Jakarta timezone definition changes in the future (politics), the index becomes corrupt. Probability: very low (stable since 1932).
Materialized View — refresh daily via cron (mig 050 pattern). Trade-off: data lag <24 hours. Suitable for non-real-time dashboards.

Recommendation: apply the mitigation only if prod observation shows p95 latency > 100 ms for endpoints using view #2. Until then, the plain view is sufficient.

Hot paths using view #2

Endpoint	Go caller	Query window	Risk
`GET /merchant/home-dashboard`	`getMerchantHoldingBalanceTotal`	all-time (most exposed)	high
`GET /merchant/home-dashboard`	`getMerchantWeeklyGrossPoints`	7 days	low
`GET /merchant/home-dashboard`	`getMerchantTransactionSummary` (today)	1 day	very low
`GET /merchant/transactions`	`getMerchantTransactionSummary` (period)	1–30 days	low
`GET /api/partner/v1/.../transactions/summary`	`getTransactionSummary`	1–30 days	low
`GET /internal/dashboard/kpi`	`queryTodayTransactionMetrics`	1 day, all merchants	medium (depends on total daily volume)

5. Refresh runbook (Materialized View)

Currently only merchant.v_partner_merchant_performance_30d is materialized. The refresh is run by an external cron (not in the app):

# Cron: 02:00 WIB daily
psql "$DATABASE_URL" -c \
  "REFRESH MATERIALIZED VIEW CONCURRENTLY merchant.v_partner_merchant_performance_30d;"

Prerequisite: unique index on merchant_id (already created in mig 050). Without a unique index, CONCURRENTLY errors and the refresh will block the source table.

Initial build after a new CREATE MATERIALIZED VIEW is automatically non-CONCURRENT (blocking) — expected, only once.

Monitoring: if the MV fails to refresh, the partner portfolio endpoint will show data >24 hours old. There is no automatic alerting for this yet (TODO).

6. Checklist for adding a new view

7. Migration 095 — Dashboard Views (Tier 3/4 + operator)

Goal

Deduplicate the transactions × transaction_daily_fees join pattern that is duplicated 8 times across 4 Go files (see audit log or registry §4.3).

Pre-execution

Review the view SQL with DB/backend stakeholders.
Make sure no clashing view name exists in the merchant schema (SELECT viewname FROM pg_views WHERE schemaname='merchant';).
Confirm columns transaction_daily_fees.fee_status, fee_type, source_type still exist (check mig 071 transaction_fee_breakdown as the latest reference touching the fee).
EXPLAIN ANALYZE the old query vs SELECT * FROM v_... on staging DB.
Dry run the migration on the staging DB.

Execution

Apply mig 095 on staging via the project's migration procedure (see runbooks/database/operations/backup-restore.md).
Smoke test: sql SELECT count(*) FROM merchant.v_transaction_with_fee WHERE merchant_id = '<sample-uuid>'; SELECT * FROM merchant.v_merchant_daily_summary WHERE merchant_id = '<sample-uuid>' AND transaction_date_wib >= now()::date - 7 ORDER BY transaction_date_wib DESC LIMIT 10;
Apply mig 095 in production after the maintenance window is approved.

Post-execution (separate Go PR)

Refactor merchant_core_api/internal/merchant/transaction.go to use views #1 + #2.
Refactor merchant_core_api/internal/merchant/homedashboard.go to use view #2.
Refactor merchant_core_api/internal/partner/store.go to use views #1 + #2 (only for summary/list functions — referral & commission stay raw query because of dynamic WHERE).
Refactor merchant_core_api/internal/internaldashboard/store.go to use view #2 for getTodayTransactionMetrics.
cd merchant_core_api && go test ./....
End-to-end manual test: home dashboard, transactions list, partner portfolio (regression check), internal dashboard.

Rollback

DROP VIEW IF EXISTS merchant.v_transaction_with_fee;
DROP VIEW IF EXISTS merchant.v_merchant_daily_summary;

A view stores no data — dropping it is safe as long as Go code has not been refactored to use it yet.

8. Migration 097 — User Active Merchant View (Tier 4 + auth path)

Goal

Deduplicate the 2-source COALESCE pattern (merchant_users × merchants → merchant_registration_requests) used to resolve the active merchant for user X. The original pattern lives in:

merchant/transaction.go:74-141 getUserMerchantContext (3 fields)
paymentterminals/store.go:128-199 getUserMerchantContext (an exact duplicate of the above)
profile/store.go GetUserProfileWithMerchant (25+ fields — not refactored because its column scope differs significantly)

The merchant.v_user_active_merchant view returns 9 columns: user_id, source ('merchant'/'registration'/'none'), merchant_id, merchant_name, status, contact_phone, contact_email, merchant_joined_at, registration_created_at.

Behavior change note (intentional)

The old pattern in transaction.go and paymentterminals/store.go only accepted registration statuses ('pending', 'pending_review') when resolving merchant_id/merchant_name. The new view uses 4 statuses (+ 'pending_acquirer', 'acquirer_approved'), consistent with profile/store.go. After the caller refactor, users currently in pending_acquirer or acquirer_approved status will get the correct merchant_id (previously empty → context resolution failed). Verify on staging before applying to prod.

Pre-execution

Smoke test the view on staging: sql SELECT source, count(*) FROM merchant.v_user_active_merchant GROUP BY source; SELECT * FROM merchant.v_user_active_merchant WHERE user_id = '<sample-user>';
Sanity check vs old query: compare (merchant_id, merchant_name, status) from the view with the output of the old getUserMerchantContext for 5 sample users (mix: active, registration, none).

Execution

Apply mig 097 on staging.
Apply mig 097 in production.

Post-execution (separate Go PR)

Refactor merchant_core_api/internal/merchant/transaction.go getUserMerchantContext → SELECT ... FROM merchant.v_user_active_merchant WHERE user_id = $1.
Refactor merchant_core_api/internal/paymentterminals/store.go getUserMerchantContext (same).
Manual test: home dashboard, transactions list, payment terminal pair flow — focus on users currently in pending_acquirer / acquirer_approved (regression prevention).

Rollback

DROP VIEW IF EXISTS merchant.v_user_active_merchant;

9. What is NOT in mig 095 / 097 (intentionally deferred)

merchant.v_partner_accessible_merchants — flatten the 3 partner → merchant access sources (referral_partner_id, partner.merchant_access, partner.merchant_consents). The dynamic WHERE switch-case pattern is duplicated in partner/store.go:404-682 and partner/portfolio.go:75-412. Wait for Ship F+ from partner-access-scope-plan.md to land first to avoid overlap.
Refactor profile/store.go to use the view — view 097 intentionally only exposes 7 basic fields (id/name/status/contacts), while profile needs 25+ fields (address, banking, business profile, etc.). A profile refactor needs a second view with more columns, or the profile may stay raw query (preference: leave it, its scope is specific and not duplicated elsewhere).
Materialized version of v_merchant_daily_summary — wait for prod observation. If the hot path is slow, promote to MV via a follow-up migration + add to cron §5.

1. 4-tier consumer classification​

Important implications​

2. Naming convention & location​

3. Decision tree: regular VIEW vs MATERIALIZED VIEW vs raw query​

4. Existing view registry​

4.1 partner.* & psp.* schemas — B2B data contract (Tiers 1, 2, 3)​

4.2 merchant.* schema Tier 3 — partner-side aggregation​

4.3 merchant.* schema Tier 4 — Merchant app + internal operator​

4.5 Tier 2 (PSP Bank) onboarding checklist​

4.4 Performance baseline & optimization notes​

Safe for prod​

Needs observation: view #2​

Hot paths using view #2​

5. Refresh runbook (Materialized View)​

6. Checklist for adding a new view​

7. Migration 095 — Dashboard Views (Tier 3/4 + operator)​

Goal​

Pre-execution​

Execution​

Post-execution (separate Go PR)​

Rollback​

8. Migration 097 — User Active Merchant View (Tier 4 + auth path)​

Goal​

Behavior change note (intentional)​

Pre-execution​

Execution​

Post-execution (separate Go PR)​

Rollback​

9. What is NOT in mig 095 / 097 (intentionally deferred)​

1. 4-tier consumer classification

Important implications

2. Naming convention & location

3. Decision tree: regular VIEW vs MATERIALIZED VIEW vs raw query

4. Existing view registry

4.1 `partner.` & `psp.` schemas — B2B data contract (Tiers 1, 2, 3)

4.2 `merchant.*` schema Tier 3 — partner-side aggregation

4.3 `merchant.*` schema Tier 4 — Merchant app + internal operator

4.5 Tier 2 (PSP Bank) onboarding checklist

4.4 Performance baseline & optimization notes

Safe for prod

Needs observation: view #2

Hot paths using view #2

5. Refresh runbook (Materialized View)

6. Checklist for adding a new view

7. Migration 095 — Dashboard Views (Tier 3/4 + operator)

Goal

Pre-execution

Execution

Post-execution (separate Go PR)

Rollback

8. Migration 097 — User Active Merchant View (Tier 4 + auth path)

Goal

Behavior change note (intentional)

Pre-execution

Execution

Post-execution (separate Go PR)

Rollback

9. What is NOT in mig 095 / 097 (intentionally deferred)