Database Schema¶

Database: dms-layered on PostgreSQL 16 Two schemas: msort (Core layer) · meesho (Meesho layer) Migration tracking: public.__EFMigrationsHistory

Schema map¶

dms-layered (PostgreSQL 16)
├── public
│   └── __EFMigrationsHistory        EF tracks applied migrations here
│
├── msort  (Core layer)
│   ├── bin                          Sort bins (Regular / Retry / Reject)
│   ├── bot                          Robot assignment state
│   ├── station                      Physical stations (Drop / Unknown)
│   ├── infeed                       Scanner infeed conveyor points
│   ├── destinations                 Flexli destination IDs
│   ├── destination_map              destination ↔ bin assignments
│   ├── parcel_events               Audit log (Scanned / Retry / Error / Bagged)
│   ├── motherbags                   Sealed bag records
│   ├── sessions                     Active session (unique partial index)
│   ├── flexli_sorter_profile        System KV config store
│   └── idempotency_keys             Request deduplication
│
└── meesho  (Meesho layer)
    ├── data_sync (T1)               AWB routing cache (pulled from Meesho API)
    ├── inscan_sync (T2)             InScan outbox (→ Meesho API)
    ├── customer_sorter_profile (T5) Sorter config KV (API_Base_URL etc.)
    ├── motherbag_sync (T16)         Manifest outbox (→ Meesho API)
    ├── manifest_parcels             AWB list per sealed bag
    ├── client_bin_config            Meesho bin → routing type
    └── client_bin_core_destination_map  bin + routing type → Flexli dest

Key constraints¶

Unique partial index — one active session¶

CREATE UNIQUE INDEX ix_sessions_state
  ON msort.sessions (state)
  WHERE state = 'Active';

Enforces exactly one active sort session at the database level. Application layer cannot accidentally create a second active session.

FK constraints in msort (7 total)¶

Constraint	From	To
`fk_bot_destination`	`bot.destination_id`	`destinations.id`
`fk_bot_current_station`	`bot.current_station_id`	`station.id`
`fk_bot_drop_station`	`bot.drop_station_id`	`station.id`
`fk_infeed_destination`	`infeed.destination_id`	`destinations.id`
`fk_infeed_drop_station`	`infeed.drop_station_id`	`station.id`
`fk_destination_map_bin`	`destination_map.bin_id`	`bin.id`
`fk_motherbags_actual_bin`	`motherbags.actual_bin_id`	`bin.id`

Outbox pattern (`FOR UPDATE SKIP LOCKED`)¶

inscan_sync and motherbag_sync use FOR UPDATE SKIP LOCKED to allow concurrent OutboxWorker instances to drain different rows without blocking each other.

Migration history (10 applied)¶

#	Migration ID	Context	What it adds
1	`20260529034756_Core-Layer-Schema-v1`	Core	All `msort` tables + indexes
2	`20260529105730_Add-Idempotency-Record-Table-v1`	Core	`idempotency_keys` table
3	`20260602191908_AddTypeInStation-...`	Core	`station_type`, infeed MCU columns, `parcel_events.id` → bigint
4	`20260608110000_EnforceSingleActiveSession`	Core	Unique partial index on `sessions.state`
5	`20260608121000_AddRelationalForeignKeys`	Core	All 7 FK constraints
6	`20260529174637_InitialMeeshoSchema`	Meesho	All `meesho` tables
7	`20260529174911_SeedMeeshoConfiguration`	Meesho	Seed data in `customer_sorter_profile`
8	`20260529190314_UpdateDataSyncRejectedFieldToString`	Meesho	`data_sync.rejected` column type
9	`20260604173918_AddManifestParcelsTable`	Meesho	`manifest_parcels` table
10	`20260616094632_AddClientBinConfigTables`	Meesho	`client_bin_config` + map tables

Verify applied migrations:

PGPASSWORD='3edc#EDC' psql -U postgres -d "dms-layered" \
  -t -c 'SELECT "MigrationId" FROM public."__EFMigrationsHistory" ORDER BY 1;'

Verify full schema sync¶

make db-status

Expected output shows all 10 migrations applied, all msort and meesho tables present, and the unique partial index on sessions.state.

For adding new migrations, see EF Migrations.