Skip to main content

Shipping Device DB Reference Migration Plan

This document continues from the Shipping Device DB Reference Plan into the SQL migration design level. The focus is the order of migration files, the contents of each file, the primary constraints, and the design decisions that must be preserved before final implementation under kesles_reference/reference_database/sql/ (the standalone reference repo, db_reference).

Status (2026-06-23): shipped. Migrations 015–022 (plus the merchant schema, seeds, and later coverage/rate expansion migrations) are applied in kesles_reference/reference_database/sql/.

Companion documents:

Goal​

The goal of this migration plan is to:

  • translate the device shipping policy into a reference table structure ready to implement
  • keep naming and schema patterns consistent with the merchant schema in db_reference
  • ensure that rate, coverage, and fallback data can be managed without hardcoding

General Migration Principles​

  • All new tables live in the merchant schema.
  • Table names follow the merchant.ref_* pattern.
  • Migrations are additive and backward-compatible.
  • Initial seed data is separated from the schema migration as much as possible.
  • Constraints are used to block invalid data from the start.

After the latest current file 014_create_ref_mcc_code.sql, the recommended file order is:

  1. 015_create_ref_shipping_partner.sql
  2. 016_create_ref_shipping_service.sql
  3. 017_create_ref_shipping_origin.sql
  4. 018_create_ref_shipping_destination_region_group.sql
  5. 019_create_ref_shipping_partner_service.sql
  6. 020_create_ref_shipping_partner_coverage.sql
  7. 021_create_ref_shipping_rate.sql
  8. 022_create_ref_shipping_fallback_rule.sql

If we want to separate the basic reference seeds, the seed files can start after that, for example:

  • 023_seed_ref_shipping_service.sql
  • 024_seed_ref_shipping_partner.sql

But if this repo has historically mixed create and seed in a single file for certain references, the final approach can be adjusted. For the shipping use case, I still recommend keeping schema and seed separate.

015 Create Shipping Partner​

Table name​

merchant.ref_shipping_partner

Function​

Stores the master list of courier partners allowed by the system.

Core columns​

  • shipping_partner_id identity primary key
  • partner_code varchar unique
  • partner_name varchar
  • priority_rank smallint
  • is_primary boolean
  • supports_regular boolean
  • supports_cargo boolean
  • is_active boolean
  • notes text
  • created_at
  • updated_at

Primary constraints​

  • partner_code unique
  • priority_rank > 0
  • if is_primary = true, only one active primary partner is allowed at a time

Implementation notes:

  • the uniqueness for a single active primary can be handled with a partial unique index
  • priority_rank is still stored even though there is a fallback table, because it is useful for default ordering

016 Create Shipping Service​

Table name​

merchant.ref_shipping_service

Function​

Stores the internal service categories the system recognizes.

Initial values​

  • regular
  • cargo

Core columns​

  • shipping_service_id identity primary key
  • service_code varchar unique
  • service_name varchar
  • sort_order smallint
  • is_active boolean
  • created_at
  • updated_at

Primary constraints​

  • service_code unique
  • service_code restricted to lowercase snake_case or lowercase plain code

017 Create Shipping Origin​

Table name​

merchant.ref_shipping_origin

Function​

Stores shipping origin nodes. The initial phase still uses Makassar, but this model is prepared for possible multi-origin support later.

Core columns​

  • shipping_origin_id identity primary key
  • origin_code varchar unique
  • origin_name varchar
  • country_code
  • province_code or a foreign key reference to the region table when available
  • city_code or a foreign key reference to the region table when available
  • is_default boolean
  • is_active boolean
  • created_at
  • updated_at

Primary constraints​

  • origin_code unique
  • at most one active default origin

018 Create Shipping Destination Region Group​

Table name​

merchant.ref_shipping_destination_region_group

Function​

Stores destination groupings for the shipping policy.

Initial values​

  • SULAWESI
  • NON_SULAWESI

Core columns​

  • shipping_destination_region_group_id identity primary key
  • destination_city_code or FK to the city reference
  • region_group_code
  • region_group_name
  • is_active
  • created_at
  • updated_at

Primary constraints​

  • a single destination may only have one active region_group_code
  • region_group_code is restricted by an initial enum check: SULAWESI, NON_SULAWESI

Notes:

  • the choice between destination_city_code and a final FK depends on the source-of-truth region table already available in db_reference

019 Create Shipping Partner Service​

Table name​

merchant.ref_shipping_partner_service

Function​

Stores the mapping between an internal service and the partner's service code.

Core columns​

  • shipping_partner_service_id identity primary key
  • shipping_partner_id FK to ref_shipping_partner
  • shipping_service_id FK to ref_shipping_service
  • partner_service_code
  • partner_service_name
  • is_active
  • created_at
  • updated_at

Primary constraints​

  • the combination shipping_partner_id + partner_service_code is unique
  • the combination shipping_partner_id + shipping_service_id + partner_service_code is unique if we want to be more explicit

020 Create Shipping Partner Coverage​

Table name​

merchant.ref_shipping_partner_coverage

Function​

Determines whether a given partner serves a given destination for a given service from a given origin.

Core columns​

  • shipping_partner_coverage_id identity primary key
  • shipping_partner_id FK
  • shipping_service_id FK
  • shipping_origin_id FK
  • destination_city_code or final FK
  • region_group_code
  • is_available
  • sla_min_days
  • sla_max_days
  • effective_from
  • effective_to
  • is_active
  • coverage_notes
  • created_at
  • updated_at

Primary constraints​

  • sla_min_days >= 0
  • sla_max_days >= sla_min_days
  • active periods may not overlap for the same combination of partner, service, origin, and destination

Notes:

  • active overlap usually requires an exclusion constraint or must be enforced at the application layer
  • to keep things simple at the start, use a unique combination on active data + disciplined update process

021 Create Shipping Rate​

Table name​

merchant.ref_shipping_rate

Function​

Stores the master shipping rate for a combination of partner, service, origin, and destination.

Core columns​

  • shipping_rate_id identity primary key
  • shipping_partner_id FK
  • shipping_service_id FK
  • shipping_origin_id FK
  • destination_city_code or final FK
  • currency
  • price_amount
  • 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

Primary constraints​

  • price_amount >= 0
  • min_billable_weight_kg > 0
  • weight_increment_kg > 0
  • sla_max_days >= sla_min_days
  • price_unit is restricted to initial values, e.g. per_kg, flat_per_shipment
  • rounding_rule_code is restricted to initial values, e.g. ceil_kg, floor_kg, nearest_kg

Design notes​

For the initial phase, the safest design:

  • all rates use currency = 'IDR'
  • the majority of rates use price_unit = per_kg
  • weight rounding uses ceil_kg

This model is still flexible enough without being overly complex.

022 Create Shipping Fallback Rule​

Table name​

merchant.ref_shipping_fallback_rule

Function​

Stores the fallback partner ordering by origin, region group, and internal service.

Core columns​

  • shipping_fallback_rule_id identity primary key
  • shipping_origin_id FK
  • region_group_code
  • shipping_service_id FK
  • sequence_no
  • shipping_partner_id FK
  • fallback_reason_code
  • is_active
  • created_at
  • updated_at

Primary constraints​

  • sequence_no > 0
  • the combination origin + region_group_code + service + sequence_no is unique
  • region_group_code is restricted to the initial enum

Design notes​

fallback_reason_code can be optional in the initial phase. The value is more useful as a policy label than as a logic determiner.

Region Reference​

Before final SQL implementation, we must first decide the source of truth for the region data:

Realistic options:

  1. refer directly to the city/regency table already in db_reference
  2. store destination_city_code based on the canonical region code

This decision affects:

  • the FK column type
  • city/regency seeds
  • the shipping resolver query

While the final decision is pending, all designs in this document deliberately write destination_city_code or final FK.

Minimum indexes:

  • partner: partner_code, is_active, priority_rank
  • service: service_code, is_active
  • origin: origin_code, is_default, is_active
  • region group: destination_city_code, region_group_code
  • coverage: (origin, destination, service, partner, is_active)
  • rate: (origin, destination, service, partner, effective_from, effective_to, is_active)
  • fallback: (origin, region_group_code, service, sequence_no, is_active)

Seed vs. Migration Rules​

Belongs in the basic seed:

  • regular, cargo
  • origin Makassar
  • the three initial partners
  • the initial region-group mapping
  • the initial fallback rules

Can be deferred to a phased operational seed:

  • complete coverage of all cities/regencies
  • complete rates for all destinations
  • per-destination SLA updates

Resolver Queries the Design Must Support​

This migration design must support queries such as:

  • find the active rate for Makassar -> destination X -> cargo service
  • order partners by fallback sequence
  • check the active coverage of a partner for a specific destination
  • fetch the rate effective on a specific date
  • return merchant-facing values without exposing partner data to the merchant UI

Implementation Risks​

  • locking FKs to the wrong region table will complicate later seeds
  • adding too many enum checks now can complicate later expansion
  • not separating the basic seed from the bulk rate seed will make the migration too large
  • not preparing the effective date from the start will complicate auditing rate changes

Deliverables After This Document​

Once this migration plan is approved, the next deliverables are:

  1. SQL draft files 015 through 022
  2. basic seeds for service, origin, partner, fallback
  3. initial coverage and rate seeds
  4. draft resolver queries for the backend

Status​

Current status: shipped — the SQL migrations described here (015–022) are implemented and applied in kesles_reference/reference_database/sql/.