Skip to main content

Shipping Device DB Reference Plan

This document lays out the db_reference design plan for the shipping rate master needed when delivering payment terminal devices from Makassar to merchants. The focus is on the reference data model, integration into the runtime, and its governance.

Companion document:

Goal​

This plan ensures that the shipping rate master:

  • is stored as structured reference data
  • can support 1 primary partner + 2 backups
  • can separate the merchant-facing display from internal operational details
  • can be linked into the runtime shipments in db_kesles_merchant
  • can be updated without disturbing transaction history

Design Principles​

  • db_reference stores reference master data and rules that are relatively stable.
  • db_kesles_merchant stores the runtime snapshot when a shipment is created.
  • Courier partner data is internal-only.
  • Services exposed to the merchant are limited to internal categories, not partner service names.
  • Rates must be versioned through validity periods, not overwritten without a trace.

Schema Placement​

Following the pattern already in merchant_database/db_reference/sql/013_create_merchant_schema.sql, merchant-specific reference data should sit in the merchant schema.

Because this use case is specific to:

  • merchant onboarding / fulfillment
  • terminal device distribution
  • merchant operational pricing

new tables should sit in the schema:

  • merchant.ref_shipping_partner
  • merchant.ref_shipping_service
  • and so on

This approach is cleaner than adding many new tables to public.

Required Reference Entities​

Minimum reference entities needed:

1. Courier partner​

Stores the partners allowed by the system.

Required core columns:

  • shipping_partner_id
  • partner_code
  • partner_name
  • priority_rank
  • is_primary
  • is_active
  • supports_regular
  • supports_cargo
  • notes
  • created_at
  • updated_at

Notes:

  • only partners that pass internal approval are entered into this table
  • the priority order determines the default fallback

2. Internal service​

Stores the services the system recognizes and may surface in the application layer.

Initial values:

  • regular
  • cargo

Core columns:

  • shipping_service_id
  • service_code
  • service_name
  • sort_order
  • is_active

This table is important so that the UI and API are not directly dependent on the partner's service code.

3. Mapping internal service to partner service​

Because each partner has its own service names, a mapping table is needed.

Examples:

  • internal cargo may map to partner service JTR
  • internal cargo may map to partner service GOKIL

Core columns:

  • shipping_partner_service_id
  • shipping_partner_id
  • shipping_service_id
  • partner_service_code
  • partner_service_name
  • is_active

With this model, the merchant still only knows cargo, while internal fulfillment knows the final partner service code.

4. Origin node​

Today the primary origin is Makassar, but it is still safer to make origin a reference.

Core columns:

  • shipping_origin_id
  • origin_code
  • origin_name
  • city_id or an equivalent region reference
  • province_id
  • is_default
  • is_active

Benefits:

  • ready for additional hubs in the future
  • not hardcoded at runtime

5. Per-destination partner coverage​

This table stores whether a particular partner is active for a particular destination.

Core columns:

  • shipping_partner_coverage_id
  • shipping_partner_id
  • shipping_service_id
  • origin_id
  • destination_city_id
  • region_group_code
  • is_available
  • supports_cod if needed later
  • sla_min_days
  • sla_max_days
  • effective_from
  • effective_to
  • is_active

Notes:

  • destination_city_id must reference the city/regency table that is the source of truth
  • if seeds or imports still use names, use a single naming model that matches the region master: KOTA ... or KABUPATEN ...
  • region_group_code may flag SULAWESI or NON_SULAWESI for easier query and rules

6. Shipping rate​

This is the core table for the shipping rate master.

Core columns:

  • shipping_rate_id
  • shipping_partner_id
  • shipping_service_id
  • origin_id
  • destination_city_id
  • price_amount
  • currency
  • price_unit
  • min_billable_weight_kg
  • weight_increment_kg
  • rounding_rule_code
  • sla_min_days
  • sla_max_days
  • effective_from
  • effective_to
  • is_active
  • rate_source
  • rate_notes
  • created_at
  • updated_at

Notes:

  • price_unit is important to distinguish whether the rate applies per kg, per shipment, or with some minimum
  • in the initial phase the rate is most likely standardized as per kg with a minimum billing

7. Fallback rule​

Although fallback can be derived from priority_rank, it is still useful to have an explicit rule once the policy grows complex.

Core columns:

  • shipping_fallback_rule_id
  • origin_id
  • region_group_code
  • shipping_service_id
  • sequence_no
  • shipping_partner_id
  • fallback_reason_code
  • is_active

Benefits:

  • fallback changes do not require code edits
  • operational rule audit becomes clearer

8. Destination region grouping​

Because the policy differs between Sulawesi and outside Sulawesi, destination grouping is needed.

Design options:

  1. use a derivative field from province at runtime query time
  2. store an explicit table mapping destination to region group

For audit and ease of seeding, I recommend the explicit mapping:

  • SULAWESI
  • NON_SULAWESI

Core columns:

  • destination_region_group_id
  • destination_city_id
  • region_group_code
  • region_group_name
  • is_active

Conceptual Relationships​

The main relationships:

  • one shipping_partner has many shipping_partner_services
  • one shipping_service may be used by many partners
  • one origin has many coverage and rate entries
  • one destination_city may have many rates depending on partner and service
  • one fallback_rule selects partners according to ordering

Policy Rules That Must Be Captured in Data​

The agreed policy must be readable from the reference data:

Sulawesi​

  • default operational service: cargo
  • use the primary partner if coverage and rate are available
  • otherwise switch to backups by sequence_no

Outside Sulawesi​

  • service can be regular or cargo
  • selection depends on weight and coverage
  • fallback still follows the partner ordering

Merchant-facing​

What is returned to the merchant:

  • shipping_cost
  • estimated_delivery_days
  • origin_name

What stays internal:

  • shipping_partner_id
  • partner_service_code
  • fallback sequence
  • coverage flags

Integration Into the db_kesles_merchant Runtime​

Reference data alone is not enough. The runtime must store a snapshot when the shipping decision is made.

The runtime will minimally need fields such as:

  • shipping_service_code
  • shipping_cost_amount
  • shipping_currency
  • shipping_eta_min_days
  • shipping_eta_max_days
  • shipping_origin_name
  • resolved_shipping_partner_id internal only
  • resolved_partner_service_code internal only
  • shipping_rate_reference_id
  • resolved_at

Runtime principles:

  • the shipping snapshot does not change even if the reference rate is updated
  • the final partner can be used for fulfillment and audit
  • the merchant response does not need to expose the final partner

Data Sources and Update Process​

The rate data sources need to be clearly defined:

  • partner negotiation or quotation results
  • partner official rates approved internally
  • manual updates by ops, verified by finance

Recommended update process:

  1. import or input the new rate
  2. internal review
  3. set effective_from
  4. deactivate the old rate via effective_to or is_active
  5. record source metadata and change notes

Validations Required​

Minimum validations:

  • there must not be two active rates conflicting for the same combination of partner, service, origin, destination, and period
  • inactive partners cannot be used in active fallback
  • destinations without a region group cannot reach the runtime
  • an active rate must have active coverage
  • SLA values must not be empty when a rate is activated

Versioning and Audit​

Rates are sensitive operational data. Therefore:

  • never overwrite without history
  • use effective_from and effective_to
  • store rate_source
  • store rate_notes
  • if needed, add an audit table or rely on the runtime migration audit layer in a later phase
  1. finalize the business policy document
  2. finalize the list of primary and backup partners
  3. finalize the source of truth for city/regency regions
  4. create the basic reference table migrations
  5. seed the internal services and courier partners
  6. seed the destination region groups
  7. seed coverage
  8. seed tariff rates
  9. design the internal shipping resolver endpoint
  10. add snapshot fields to the runtime shipment or order

SQL Deliverables to Be Produced After This Plan Is Approved​

Likely deliverables:

  • create-reference-table migrations under merchant_database/db_reference/sql/
  • seed of active courier partners
  • seed of internal services regular and cargo
  • seed of destination region groups
  • seed of initial coverage and rate from Makassar
  • query contract document for the shipping resolver

Open Questions​

Things that still need to be decided before final SQL implementation:

  • which reference table will be the source of truth for destination_city_id
  • the final weight threshold for regular vs cargo
  • whether rates are stored per kg, per package, or hybrid
  • whether ETA min/max days is enough or a richer SLA model is needed
  • whether area-based surcharges are needed
  • whether one device shipment is always one origin or eventually multi-origin

Status​

Status (2026-06-23): shipped. The reference tables now live in db_reference (schema merchant, e.g. merchant.ref_shipping_partner, merchant.ref_shipping_service, merchant.ref_shipping_rate, merchant.ref_shipping_priority_destination) and are read by the core_api dbreference layer and order_service. The original design narrative below is preserved.

Original plan status: draft for schema design