Flow Sense tables

Reference for core transactional tables used by sales orders, vehicle loading sheets (VLS), delivery orders, and file attachments.

`clients`

Column	Notes
`id`	Primary key (UUID v7)
`name`
`address`
`phone`
`email`
`status`	`active`, `inactive`, or `suspended` (default `active`)
`archived_at`
`created_at`
`updated_at`	Auto-updated on row change

`users`

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`name`
`phone_number`
`role`	`super_admin`, `admin`, `sr`, `dsr`, or `distributor`
`email`	Unique (case-insensitive via `lower(email)`)
`username`	Unique (case-insensitive via `lower(username)`)
`password_hash`	Nullable until password is set
`status`	`active`, `inactive`, or `invited` (default `active`)
`created_at`
`updated_at`	Auto-updated on row change

`roles`

RBAC role definitions. users.role matches roles.key (e.g. sr, admin).

Column	Notes
`id`	Primary key (UUID v7)
`key`	Unique (case-insensitive) role key
`name`	Display name
`description`
`created_at`
`updated_at`	Auto-updated on row change

`permissions`

One row per tRPC procedure (e.g. user:list, order:create).

Column	Notes
`id`	Primary key (UUID v7)
`key`	Unique (case-insensitive) permission key
`description`
`created_at`
`updated_at`	Auto-updated on row change

`client_third_party_apis`

Per-client warehouse ERP API configuration (Chainlink or compatible). No foreign key on client_id; application code enforces client scope.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client (`clients.id`, not enforced in DB)
`integration_type`	`warehouse_products_sync`, `warehouse_order_create`, `warehouse_sales_orders_sync`, or `warehouse_delivery_confirm`
`base_url`	API host only (e.g. `https://api-chainlink-test.mudidokan.store`); tower appends `/api/v1/third-party/...`
`api_key`	Value for `X-API-Key` / `x-api-key` header
`warehouse_code`	Required for `warehouse_products_sync`; null for other integration types
`enabled`	When `true`, integration is active
`created_at`
`updated_at`	Auto-updated on row change

`products`

Column	Notes
`id`	Primary key (UUID v7, Flow Sense)
`external_product_id`	Product id from warehouse ERP; nullable until first sync
`client_id`	Owning client
`warehouse_id`	ERP warehouse id (text); scope for sync and uniqueness
`product_name`
`brand_name`
`barcode`
`product_code`	SKU / product code from ERP
`unit_price`
`minimum_order_quantity`
`image_url`
`is_active`	`true` when present in latest sync; `false` when dropped
`created_at`
`updated_at`	Auto-updated on row change

`shops`

Column	Notes
`id`	Primary key
`client_id`
`name`
`address`
`owner_name`
`owner_phone`
`lat`
`long`
`geom`	PostGIS point
`ps_ref`
`front_image_url`	Optional storefront image URL
`archived_at`
`created_at`
`updated_at`

`shop_change_requests`

Staging table for shop create/edit requests that require admin approval before changes are applied to shops.

Check: action = create requires shop_id null and route_id set; action = update requires shop_id set and route_id null.

Unique: at most one pending update per shop_id.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`action`	`create` or `update`
`shop_id`	Target shop for `update`; null for `create`
`route_id`	Intended route for `create` (resolved server-side from today’s assignments and shop coordinates); null for `update`
`payload`	JSONB full proposed shop snapshot (`name`, `address`, `owner_name`, `owner_phone`, `lat`, `long`, `ps_ref`, `front_image_url`)
`status`	`pending`, `approved`, `rejected`, or `cancelled` (default `pending`)
`requested_by`	User who submitted the request
`requested_at`	When the request was submitted
`reviewed_by`	Admin who approved or rejected; null until reviewed
`reviewed_at`	When the request was reviewed; null until reviewed
`review_note`	Required when rejecting; optional otherwise
`result_shop_id`	`shops.id` created or updated on approval; null until approved
`file_ids`	UUID array of linked `files.id` evidence photos; nullable (`null` or `[]` allowed)
`created_at`
`updated_at`	Auto-updated on row change

On approval: create inserts a shops row from payload; update updates the target shop. shop_route_assignments is maintained by the existing shops geom trigger from lat / long.

SR file flow: call files.create first (phase shop-add-req or shop-edit-req, link.type shop_change_req — no parent id). Collect returned file ids, then pass fileIds on shop.createChangeRequest, shop.createUpdateChangeRequest, or shop.updateChangeRequest (full list replace). After link, files.get / files.edit scope via file_ids on this row (requester only). Unlinked shop-change files before submit are not visible via files.get.

`routes`

Column	Notes
`id`	Primary key
`client_id`
`name`
`tag`	`p1` or `p2`
`coordinates`	GeoJSON polygon
`geom`	PostGIS polygon
`archived_at`
`created_at`
`created_by`
`updated_at`
`updated_by`

`shop_route_assignments`

Junction table (auto-maintained by triggers): which shops fall inside which route polygons for a client.

Column	Notes
`shop_id`
`route_id`
`client_id`	Denormalized for client scoping
`created_at`
`created_by`
`updated_at`
`updated_by`

`route_user_assignments`

Which user is assigned to work a route on a given weekday.

Column	Notes
`id`	Primary key (UUID v7)
`route_id`
`client_id`
`assigned_user_id`	User assigned to the route
`day`	Weekday: `1` = Sunday … `7` = Saturday
`created_at`
`created_by`
`updated_at`
`updated_by`

`daily_shop_tasks`

One row per shop visit by an SR. Visit date is created_at (calendar day in Asia/Dhaka). Which shops an SR should visit on a given weekday comes from route_user_assignments; route for a visit is derived via shop_id → shop_route_assignments → routes.

Unique: at most one visit per (shop_id, created_by) per operating calendar day (created_at in Asia/Dhaka).

Column	Notes
`id`	Primary key (UUID v7)
`shop_id`	Shop visited
`status`	`initial`, `open_no_order`, `closed`, `location_changed`, `not_found_at_location`, `destroyed`, `open_order_placed`
`note`
`lat`	Visit proof latitude (snapshot at visit time; not the shop registry location)
`long`	Visit proof longitude (snapshot at visit time)
`geom`	PostGIS point; trigger-maintained from `lat` / `long` for spatial queries
`created_at`	When the visit was recorded (visit date for filtering)
`created_by`	SR who recorded the visit

`orders`

Column	Notes
`id`	Primary key
`client_id`
`shop_id`
`daily_shop_task_id`
`discount`	Percent discount (numeric, 0–100); nullable
`created_by`	User who takes the order
`created_at`
`updated_at`
`updated_by`
`confirmed_at`
`confirmed_by`
`cancel_at`
`cancel_by`
`cancel_reason`
`warehouse_order_id`	Warehouse ERP order id after `POST /api/v1/third-party/partner-orders`
`warehouse_order_status`	e.g. `pending` from warehouse ERP
`warehouse_order_submitted_at`	When warehouse order API accepted the order
`warehouse_order_last_error`	Full JSON partner-orders API error if submit failed

`order_items`

if unit_price = 0.00 and parent_product_id = null then the row represent a free product for the whole order

Column	Notes
`id`	Primary key
`order_id`
`product_id`
`parent_product_id`	Nullable; free line for a paid product when `unit_price` is 0.00 and this is set
`discount`	Percent discount on paid line (numeric, 0–100); nullable
`order_quantity`
`unit_price`	Snapshot of product price at order time

`sales_orders`

Synced from the warehouse ERP sales order API (warehouse_sales_orders_sync). Linked to a Flow Sense order via order_id = API external_ref. No foreign keys; application code enforces client scope.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`order_id`	Flow Sense `orders.id` (same value as warehouse API `external_ref`)
`warehouse_sales_order_id`	Chainlink sale order `id` from `GET /sale-orders` response; used for delivery-confirmation API
`discount`	Percent discount (numeric, 0–100); nullable; global order discount from Chainlink
`status`	Warehouse sale order status (see values below)
`is_dsr_assigned`	`false` by default; set `true` when a DSR is assigned via VLS generation or `salesOrder.assignUserForDelivery`
`created_by_id`	ERP creator user id
`created_by_name`	ERP creator display name
`created_by_email`	ERP creator email
`created_at`	ERP `created_at` when synced; default `now()` on insert
`rejected_by_id`	ERP rejector user id; null if not rejected
`rejected_by_name`	ERP rejector display name
`rejected_by_email`	ERP rejector email
`rejected_at`	ERP rejection timestamp
`updated_at`	Auto-updated on row change
`updated_by`

status values: draft, pending_availability_check, pending_finance_review, pending_approval, approved, sorting, dispatched, delivered, closed, cancelled.

Unique: (client_id, order_id).

`sales_order_items`

if unit_price_external = 0.00 and parent_product_id is set then the row is a line-bound free product; order-level free rows require Chainlink to send parent_product_id (null = global free)

Column	Notes
`id`	Primary key (UUID v7)
`sales_order_id`	Parent `sales_orders.id`
`product_id`	Flow Sense product id
`parent_product_id`	Nullable; paid product this free line belongs to when `unit_price_external` is 0.00
`discount`	Percent discount on paid line (numeric, 0–100); nullable
`sales_order_quantity`	Dispatched quantity from warehouse ERP
`unit_price_external`	Unit price from warehouse ERP (`unit_price`)

`trucks`

Client-scoped truck registry. Used when generating a VLS; truck number is unique per client among non-archived rows.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`truck_no`	Truck registration / fleet number
`driver_name`	Default driver name; nullable
`created_at`
`created_by`
`updated_at`	Auto-updated on row change
`updated_by`
`archived_at`	Soft-delete timestamp; null when active

Unique: (client_id, lower(truck_no)) where archived_at is null.

`vehicle_loading_sheets`

VLS batch header. A client may have multiple sheets for the same work_date (partial generate, follow-up batches). Created when an admin finalizes shop-to-vehicle assignments.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`work_date`	Operating calendar date the VLS was built for (`Asia/Dhaka`)
`delivery_date`	Planned delivery date for generated delivery orders
`created_at`
`created_by`	User who generated the VLS
`updated_at`	Auto-updated on row change
`updated_by`

Index: (client_id, work_date) — not unique; list all batches for a work date.

`vls_vehicles`

One row per truck/DSR slot on a VLS. Snapshots preserve truck and DSR details at generation time.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`vls_id`	Parent `vehicle_loading_sheets.id`
`truck_id`	References `trucks.id`
`assigned_user_id`	DSR assigned to this vehicle (`users.id`)
`sequence`	Display order of vehicles on the sheet (1-based)
`label`	UI label (e.g. `Vehicle 1`)
`truck_no_snapshot`	Truck number at generation time
`driver_name_snapshot`	Driver name at generation time; nullable
`assigned_user_name_snapshot`	DSR display name at generation time
`created_at`

`vls_vehicle_sales_orders`

Junction table: which sales orders are loaded on which VLS vehicle, and in what stop order.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`vls_vehicle_id`	Parent `vls_vehicles.id`
`sales_order_id`	`sales_orders.id` assigned to the vehicle
`sort_order`	Stop sequence on the vehicle (1-based)
`created_at`

Unique: (client_id, sales_order_id) — each sales order can be on only one active VLS at a time. When an SO is released from a VLS, its row here is deleted; history is kept in vls_sales_order_assignment_events.

`vls_sales_order_assignment_events`

Audit log for VLS sales-order assignment changes. One row per add, release, or move. Rows are append-only (never deleted).

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`sales_order_id`	`sales_orders.id` affected
`vls_id`	`vehicle_loading_sheets.id` involved
`vls_vehicle_id`	`vls_vehicles.id`; nullable (e.g. release may not target one vehicle)
`action`	`added`, `released`, or `moved`
`reason`	Required free-text reason from the admin
`performed_by`	`users.id` who performed the action
`performed_at`	When the action was recorded

action values:

added — SO placed on a VLS (generate or add-to-batch)
released — SO removed from a VLS; SO becomes available again if delivery allows
moved — release + add in one operation (target batch recorded on the event)

Index: (client_id, sales_order_id), (vls_id), (client_id, performed_at).

`vls_vehicle_routes`

Optimized driving route for a VLS vehicle (e.g. Google Directions). At most one route per vehicle.

Column	Notes
`id`	Primary key (UUID v7)
`client_id`	Owning client
`vls_vehicle_id`	Parent `vls_vehicles.id`
`provider`	Route provider key (e.g. `google_directions`)
`origin_name`	Warehouse / depot name
`origin_lat`	Origin latitude
`origin_lng`	Origin longitude
`overview_polyline`	Encoded route polyline; nullable
`total_distance_meters`	Total outbound distance
`total_duration_seconds`	Total outbound duration
`return_distance_meters`	Return leg distance to origin
`return_duration_seconds`	Return leg duration to origin
`stops`	JSON array of stop details (shop, coords, legs)
`waypoint_order`	JSON array of optimized waypoint indices
`created_at`
`created_by`
`updated_at`	Auto-updated on row change
`updated_by`

Unique: (client_id, vls_vehicle_id).

`delivery_orders`

Column	Notes
`id`	Primary key
`client_id`
`assigned_to`
`order_id`
`sales_order_id`
`delivery_date`
`vls_vehicle_id`	Set when created via VLS generation; references `vls_vehicles.id`
`bill_collection_status`	`null` (nothing collected yet), `partial`, or `collected`
`completed_at`
`completed_by`
`canceled_at`
`canceled_by`
`cancel_reason`
`warehouse_delivery_confirmed_at`	When warehouse delivery-confirmation API accepted the delivery
`warehouse_so_number`	ERP `so_number` from delivery-confirmation response
`warehouse_delivered_at`	ERP `delivered_at` from delivery-confirmation response
`warehouse_delivery_last_error`	Full JSON warehouse delivery-confirmation API error if confirm failed
`created_at`
`updated_at`
`updated_by`

bill_collection_status: null means no collection recorded yet; partial means some bill amount collected; collected means fully collected.

`delivery_order_items`

Column	Notes
`id`	Primary key
`delivery_order_id`
`product_id`
`delivered_quantity`

`delivery_order_tasks`

Column	Notes
`id`	Primary key
`delivery_order_id`
`status`	`open_no_delivery, closed, location_changed, not_found_at_location, destroyed, open_delivered`
`note`
`visited_at`
`lat`
`long`
`geom`
`created_at`
`created_by`

`bill_collections`

Column	Notes
`id`	Primary key
`delivery_order_id`	Delivery order the payment applies to
`user_id`	User who recorded the collection
`collected_amount`	Partial or full payment amount
`created_at`

`files`

Upload metadata for shop visits, deliveries, and shop change requests. Client scope is via junction tables for visit/delivery files, or via shop_change_requests.file_ids once linked (not client_id on this table).

Column	Notes
`id`	Primary key
`status`	`pending`, `queued`, `uploading`, `uploaded`, `missing`, or `failed` (default `pending`)
`phase`	`task_start`, `order`, `delivery`, `shop-add-req`, or `shop-edit-req`
`local_uri`
`url`
`kind`
`attempts`
`object_key`
`content_type`
`uploaded_at`
`created_at`
`updated_at`	Auto-updated on row change

`daily_shop_task_files`

Links a file to a shop visit (daily_shop_tasks). SR visit photos use this table; order-related photos on a visit also link here (use files.phase = order). Find files for an order via orders.daily_shop_task_id.

Column	Notes
`id`	Primary key
`daily_shop_task_id`
`file_id`
`created_at`

`delivery_order_task_files`

Column	Notes
`id`	Primary key
`delivery_order_task_id`
`file_id`
`created_at`

`cash_collections`

Column	Notes
`id`	Primary key
`collected_from`	user id from whom the cash is collected (basically DSR or SR)
`collected_by`	user id who collects the cash (basically warehouse manager)
`expected_amount`
`collected_amount`
`note`
`date`	YYYY-MM-DD input from client, not auto generated

`bank_details`

Column	Notes
`id`	Primary key
`client_id`
`account_type`	bank or mfs
`name`	bank name or mfs name
`account_number`
`account_holder_name`
`branch`	null for mfs
`routing_number`	null for mfs
`mfs_account_type`	personal, agent, or merchant; null for bank
`status`	active or inactive (default active)
`created_at`
`created_by`
`updated_at`
`updated_by`

`cash_deposits`

Column	Notes
`id`	Primary key
`deposited_by`	user id who deposit the money
`deposited_amount`
`adjusted_amount`	Nullable; set when accounts adjusts the deposit
`bank_id`
`deposit_reference`	bank slip no. or MFS transaction ID
`cash_received_date`	date of cash received
`deposited_date`	when received amount will be deposited in the bank
`depositor_note`
`status`	pending, confirmed, cancelled, or adjusted
`accounts_note`
`created_at`
`updated_at`
`updated_by`

Schema