Database Schema

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`code`UQ	TEXT	Short human-readable code shown in the UI and on documents — e.g. WH-GGN (Gurgaon warehouse), OFC-DEL (Delhi office), AMZ-BLR1 (Amazon Bangalore FC).
`name`	TEXT	Full descriptive name of the location.
`type`	ENUM	warehouse \| office \| amazon_fc — controls which movement types can use this location as source or destination.
`address` nullable	TEXT	Physical street address. Printed on STN and delivery challan documents.
`gstin` nullable	TEXT	GST registration number. Required for e-way bill generation and printed on tax invoices.
`state_code` nullable	TEXT	2-digit state code (e.g. 06 for Haryana). Used to determine whether a sale is intra-state (CGST+SGST) or inter-state (IGST).
`is_active`	BOOLEAN default: true	Inactive locations are hidden from movement forms but their history is preserved.
`created_at`	TIMESTAMPTZ	When this location was first added to the system.
`updated_at`	TIMESTAMPTZ	Last time any field was edited.

erp.contacts

External people and entities that stock travels to or from — customers, employees who carry devices, influencers, and vendors. In the UI these appear as the 'Send to' / 'Receive from' target on movement forms, never labelled as 'contacts'.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`code`UQ	TEXT	Human-readable identifier — CUST-0001 (customer), EMP-0042 (employee), INF-VARUN (influencer). Used in CSV imports and document references.
`name`	TEXT	Full name — shown in the movement form's recipient selector.
`type`	ENUM	customer \| employee \| influencer \| vendor — controls which types appear in each movement form selector. Sales shows customers; to_employee shows employees; influencer movements show influencers.
`email` nullable	TEXT	Email address — used for sending delivery challans and invoices.
`phone` nullable	TEXT	Phone number.
`address` nullable	TEXT	Default delivery address. Pre-fills the ship_to_address field on a movement but can be overridden per movement.
`gstin` nullable	TEXT	Customer's GST number — printed on the sales invoice.
`is_active`	BOOLEAN default: true	Inactive contacts are hidden from movement forms but their history is preserved.
`created_at`	TIMESTAMPTZ	When this contact was first added.
`updated_at`	TIMESTAMPTZ	Last time any field was edited.

erp.skus

Product type definitions — what upliance sells and tracks. Covers both serialized devices (each physical unit gets a unique serial number) and bulk accessories (tracked by quantity only, no per-unit identity).

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`code`UQ	TEXT	Product code used in all internal references and CSV imports — e.g. UP-V1, AF-4L.
`family`	ENUM	V1_0 (upliance 1.0) \| V2_0 (frodo 2.0) \| AF (air fryers) \| ACCESSORY \| BUNDLE — used for filtering and reporting dashboards.
`description`	TEXT	Human-readable product name shown in movement forms and printed on documents.
`is_serialized`	BOOLEAN	TRUE = each physical unit has a unique serial number (devices). FALSE = tracked by quantity only with no per-unit identity (accessories). Controls whether movement lines require a serial number scan.
`uom`	TEXT default: 'pcs'	Unit of measure — pcs, box, etc. Printed on documents.
`hsn_code` nullable	TEXT	Harmonised System of Nomenclature code. Required by GST law on sales invoices.
`created_at`	TIMESTAMPTZ	When this SKU was first defined.
`updated_at`	TIMESTAMPTZ	Last time any field was edited.

erp.users

Upliance staff who log into the ERP. Login is via Google OAuth restricted to @upliance.ai. A user must exist in this table to access the app — a valid Google account alone is not enough.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier — also carried in the NextAuth JWT as the Hasura user claim.
`email`UQ	TEXT	Google account email — must end in @upliance.ai. Used to match the OAuth token to this user record.
`name`	TEXT	Display name shown in the UI and on audit trails.
`role`	ENUM	mech \| ops \| sales \| finance \| admin — controls what actions the user can take. Hasura row-level permissions are scoped to this role.
`is_active`	BOOLEAN default: true	Deactivated users cannot log in even if their Google account is valid.
`created_at`	TIMESTAMPTZ	When this user was first added.
`updated_at`	TIMESTAMPTZ	Last time any field was edited.

Inventory

Where things are right now — a fast snapshot derived from the movement history.

erp.inventory_items

One row per physical serialized device. Tracks the current location and status of every unit. The movements table is the source of truth for history; the fields here are a denormalized snapshot that must stay in sync with the latest movement on each device.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`serial_no`FKUQ → devices_master.serial_number	TEXT	The serial number printed on the physical device — scanned or typed during movement line entry. Hard FK to devices_master.serial_number; every inventory row maps to exactly one staging row by serial.
`sku_id`FK → skus.id	UUID	Which product type this device is — e.g. upliance 1.0, Air Fryer 4L.
`current_location_id`FK → locations.id nullable	UUID	Which warehouse or office the device is currently sitting in. NULL when the device is with a person (current_contact_id is set instead). Exactly one of these two columns is populated at any time.
`current_contact_id`FK → contacts.id nullable	UUID	Which customer, employee, or influencer currently has the device. NULL when the device is at a location. Mutually exclusive with current_location_id — a DB CHECK constraint enforces this.
`status`	ENUM	in_stock \| in_transit \| with_customer \| with_influencer \| with_employee \| at_amazon \| in_service \| written_off — the device's current state. Updated whenever a movement is dispatched or received.
`last_movement_id`FK → movement_lines.id nullable	UUID	Pointer to the most recent movement line this device appeared on — the exact record of the event that caused the current status. The movement itself is reachable via movement_lines.movement_id.
`created_via`	TEXT	Origin trail: 'movement' (created as part of a movement, or before this column existed), 'production' (added at the production line — Tescom), 'import' (promoted from devices_master). CHECK-constrained.
`imported_at` nullable	TIMESTAMPTZ	When this row was promoted from devices_master. NULL for 'production' and 'movement' origin.
`import_source_ref` nullable	TEXT	The devices_master.source_id this row maps to (soft TEXT pointer — no FK constraint; structural link is on serial_no). 'audit-<uuid>' for the May-26 audit-seed rows, real source_id otherwise. Set on every inventory row.
`created_at`	TIMESTAMPTZ	When the device was first registered in the system.
`updated_at`	TIMESTAMPTZ	Last time any field was updated.

erp.stock_levels

Running quantity of non-serialized accessories at each location. Because accessories have no serial numbers, stock is a simple integer count per SKU per location. (sku_id, location_id) is the primary key — there is no surrogate id, since nothing references this leaf table.

Column	Type	Real-world meaning
`sku_id`PKFK → skus.id	UUID	Which accessory SKU this count is for — must reference a non-serialized SKU. Part of the composite primary key.
`location_id`PKFK → locations.id	UUID	Which location holds this stock. Part of the composite primary key.
`qty`	INTEGER default: 0	Current quantity on hand. Never goes negative — a CHECK constraint enforces this. Updated atomically when movements are dispatched or received.
`updated_at`	TIMESTAMPTZ	Last time the quantity changed.

Movements

The central append-only event log — every time stock moves, a row is written here and never modified.

erp.movements

Every stock event in the company's history. One row per movement, covering all 14 movement types with a uniform column set — columns irrelevant to a given type are NULL. This is the source of truth; inventory_items and stock_levels are derived snapshots of it.

Column	Type	Real-world meaning
`id`PK	UUID	Permanent internal identifier for this movement.
`movement_no`UQ nullable	TEXT	Human-readable document number assigned when the checker approves — e.g. DEL-2526-0001. NULL until approval. Mirrors the doc_no from the documents table.
`movement_type`	ENUM	One of 14 types: inter_warehouse, office_transfer, to_amazon, to_employee, sales_delivery, service_delivery, influencer_barter, influencer_marketing_out, try_and_buy_out, service_pickup, customer_return_7day, influencer_marketing_return, try_and_buy_return, amazon_return.
`direction`	ENUM	outbound (stock leaving a location) or inbound (stock arriving). Fully determined by movement_type — stored here for fast index queries.
`from_location_id`FK → locations.id nullable	UUID	Source location — set when stock originates from a warehouse or office. Exactly one of from_location_id or from_contact_id is always set; the other is NULL.
`from_contact_id`FK → contacts.id nullable	UUID	Source person — set when a customer, employee, or influencer is sending stock back (returns, service pickups). Exactly one of from_location_id or from_contact_id is always set.
`to_location_id`FK → locations.id nullable	UUID	Destination location — set when stock is going to a warehouse or office. Exactly one of to_location_id or to_contact_id is always set.
`to_contact_id`FK → contacts.id nullable	UUID	Destination person — set when stock is going to a customer, employee, or influencer. Exactly one of to_location_id or to_contact_id is always set.
`status`	ENUM default: 'draft'	Lifecycle stage: draft → pending_check → approved → dispatched → received (or cancelled). Stock side-effects fire at dispatched (source decremented) and received (destination incremented).
`ship_to_name` nullable	TEXT	Snapshot of the recipient's name at the moment of approval — frozen so printed documents remain accurate even if the contact or location master record is later edited.
`ship_to_address` nullable	TEXT	Snapshot of the delivery address at approval. Same immutability guarantee as ship_to_name.
`ship_to_gstin` nullable	TEXT	Snapshot of the recipient's GST number at approval — printed on the delivery challan and sales invoice.
`expected_return`	BOOLEAN default: false	TRUE for try_and_buy_out and influencer_marketing_out — stock is sent out but is expected to come back. Drives the overdue-returns dashboard.
`expected_return_by` nullable	DATE	Deadline date by which the stock should be returned. Required when expected_return is TRUE.
`parent_movement_id`FK → movements.id nullable	UUID	For return movements — points to the original outbound movement being reversed. Links influencer_marketing_return to its influencer_marketing_out, or try_and_buy_return to its try_and_buy_out.
`maker_id`FK → users.id	UUID	The staff member who created the draft and submitted it for approval.
`maker_at`	TIMESTAMPTZ	Timestamp when the maker submitted (transitioned the movement to pending_check).
`checker_id`FK → users.id nullable	UUID	The staff member who reviewed and approved or rejected the movement.
`checker_at` nullable	TIMESTAMPTZ	Timestamp when the checker acted.
`dispatched_by_user_id`FK → users.id nullable	UUID	Who confirmed that physical goods left the source location.
`dispatched_at` nullable	TIMESTAMPTZ	Timestamp of physical dispatch. Source stock is decremented at this point.
`received_by_user_id`FK → users.id nullable	UUID	Who confirmed receipt at the destination.
`received_at` nullable	TIMESTAMPTZ	Timestamp of physical receipt. Destination stock is incremented at this point.
`cancelled_by_user_id`FK → users.id nullable	UUID	Who cancelled this movement (if applicable).
`cancelled_at` nullable	TIMESTAMPTZ	Timestamp of cancellation.
`cancellation_reason` nullable	TEXT	Free-text reason for cancellation — required when cancelling.
`remarks` nullable	TEXT	General notes on the movement entered by the maker.
`created_at`	TIMESTAMPTZ	When the draft was first saved.
`updated_at`	TIMESTAMPTZ	Last modification timestamp.

erp.movement_lines

The individual items on a movement — one row per serialized device, or one row per SKU+quantity for accessories. A movement shipping 80 devices has 80 lines; one shipping 50 lids has 1 line with qty = 50.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`movement_id`FK → movements.id	UUID	Which movement this line belongs to. Cascades on delete — deleting a movement removes all its lines.
`sku_id`FK → skus.id	UUID	Which product type this line covers.
`inventory_item_id`FK → inventory_items.id nullable	UUID	The specific serialized device being moved (identified by its serial number). NULL for accessory lines. A given device cannot appear twice on the same movement — a partial UNIQUE index enforces this.
`qty`	INTEGER	Quantity being moved. Always 1 for serialized device lines; the actual count for accessory lines.
`received_qty` nullable	INTEGER	How many units were actually received — recorded when the movement reaches the received status. May be less than qty if items are missing or damaged in transit.
`unit_price_paise` nullable	INTEGER	Selling price per unit in paise (₹1 = 100 paise). Set only for sales_delivery lines. NULL for all other movement types.
`line_total_paise` nullable	INTEGER	Total value of this line (qty × unit_price_paise). Set for sales_delivery lines — should always equal qty * unit_price_paise.
`remarks` nullable	TEXT	Per-line notes — e.g. 'screen cracked on arrival' or 'different colour than requested'.
`created_at`	TIMESTAMPTZ	When this line was added to the movement.

Documents

The paper trail — formal documents generated at approval, numbered sequentially per financial year, with PDF storage.

erp.doc_series

An atomic sequence counter per document prefix per financial year. Locked with SELECT FOR UPDATE during approval so concurrent approvals never receive the same number. Counters reset each April 1 for the new financial year.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier. The natural key is (prefix, fy) — enforced via a UNIQUE constraint.
`prefix`	TEXT	Document prefix — STN, DEL, RTO, RC, or SI. Each has its own independent counter.
`fy`	TEXT	Financial year as a 4-digit string (e.g. '2526' for April 2025 – March 2026). Counters reset when the FY changes.
`next_seq`	INTEGER default: 1	The sequence number to issue next. Incremented atomically inside the approval transaction using SELECT … FOR UPDATE.

erp.documents

Formal documents issued when a movement is approved — one or two per movement depending on type. A sales delivery gets both a DEL- (delivery challan) and an SI- (sales invoice); an inter-warehouse transfer gets only an STN-.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`doc_type`	ENUM	STN \| DC \| GRN \| RC \| SI — the category of document.
`prefix`	TEXT	The prefix in the document number — STN, DEL, RTO, RC, or SI.
`fy`	TEXT	Financial year this document was issued in — embedded in the document number.
`seq`	INTEGER	Sequential number within this prefix+FY, assigned atomically from doc_series. Together with prefix and fy, this forms the natural key.
`doc_no`UQ	TEXT	Full human-readable document number — e.g. DEL-2526-0001. Computed from prefix + fy + seq. This is the identifier that appears on all printed documents.
`movement_id`FK → movements.id	UUID	Which movement this document covers.
`issued_at`	TIMESTAMPTZ	When the document was generated — printed on the document.
`issued_by_user_id`FK → users.id	UUID	Which staff member's approval action triggered document generation.
`pdf_url` nullable	TEXT	FileStore-relative path to the stored PDF. NULL until the PDF has been generated and saved to disk.

erp.sales_invoices

Financial detail for every sales_delivery movement. Holds the buyer snapshot and invoice amounts. In V1, amounts are entered manually — there is no automatic CGST/SGST/IGST calculation. One row per sales movement.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier. The natural key is movement_id — there is exactly one invoice per sales movement.
`movement_id`FKUQ → movements.id	UUID	The sales_delivery movement this invoice covers. Cascades on delete.
`document_id`FKUQ → documents.id nullable	UUID	The SI- document generated for this invoice.
`buyer_name` nullable	TEXT	Customer name snapshot at time of issue — frozen so the printed invoice stays accurate even if the customer master record later changes.
`buyer_address` nullable	TEXT	Delivery address snapshot at issue.
`buyer_gstin` nullable	TEXT	Customer GST number snapshot at issue — printed on the invoice.
`place_of_supply` nullable	TEXT	2-digit state code of the delivery destination. Determines whether GST is intra-state (CGST+SGST) or inter-state (IGST).
`subtotal_paise`	INTEGER	Total before tax in paise (₹1 = 100 paise). Entered manually.
`tax_paise`	INTEGER default: 0	Tax amount in paise — CGST+SGST or IGST, entered manually in V1.
`total_paise`	INTEGER	Final invoice total including tax in paise. Should equal subtotal_paise + tax_paise.
`notes` nullable	TEXT	Invoice footer notes or payment terms, printed on the invoice.
`created_at`	TIMESTAMPTZ	When the invoice record was created.
`updated_at`	TIMESTAMPTZ	Last modification timestamp.

erp.eway_bills

E-way bill records required by Indian GST law for inter-location transfers above ₹50,000. In V1, the operator generates the e-way bill on the government portal and manually enters the number here. An active e-way bill must exist before an STN movement can be dispatched.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`ewb_no`UQ	TEXT	Government-issued e-way bill number (12 digits). The natural identifier — printed on the physical vehicle and in government records.
`movement_id`FK → movements.id	UUID	Which inter-location movement this e-way bill covers. Movements of type inter_warehouse, office_transfer, to_amazon, and to_employee require an active e-way bill before they can be dispatched.
`generated_at`	TIMESTAMPTZ	When the e-way bill was generated on the government portal — marks the start of the validity window.
`valid_until`	TIMESTAMPTZ	Expiry timestamp. Goods must reach their destination before this time; after expiry the vehicle can be intercepted by tax authorities.
`vehicle_no` nullable	TEXT	Vehicle or truck registration plate number.
`transporter_name` nullable	TEXT	Name of the logistics or courier company.
`distance_km` nullable	INTEGER	Distance in kilometres between source and destination. The government portal uses this to calculate how long the e-way bill stays valid.
`status`	ENUM default: 'active'	active \| cancelled \| expired — reflects the current status on the government portal, captured manually.
`created_at`	TIMESTAMPTZ	When this record was created in the ERP.

Audit

Immutable record of every action any user takes across the entire system.

erp.audit_log

Append-only log of every state change. Written automatically by the application on every create, update, status transition, and cancellation. Never modified or deleted — the permanent compliance record.

Column	Type	Real-world meaning
`id`PK	UUID	Internal database identifier.
`entity_type`	TEXT	The table that was changed — 'movement', 'document', 'inventory_item', 'location', etc. Paired with entity_id to identify the record.
`entity_id`	UUID	The ID of the record that was changed. Weakly typed — no foreign key constraint — so the audit trail survives even if the referenced record is later deleted.
`action`	TEXT	What happened — 'created', 'status_changed', 'approved', 'dispatched', 'received', 'cancelled', 'pdf_generated', etc.
`actor_user_id`FK → users.id	UUID	Which staff member performed the action. All audit entries are attributed to a real user.
`changes` nullable	JSONB	Before/after snapshot of the changed fields — e.g. { before: { status: 'draft' }, after: { status: 'pending_check' } }. NULL for creation events (no previous state to show).
`created_at`	TIMESTAMPTZ	Timestamp when the action occurred — the immutable record of when this event happened.

Device registry

The canonical device table. Every device that exists is a row here — production-line units added via /devices/new, plus the legacy backfill of ~13k devices from the source Hasura. inventory_items.serial_no hard-FKs back to devices_master.serial_number.

erp.devices_master

Canonical registry for every device. New production-line entries (via /devices/new) only set serial_number, device_type, resolved_sku_id, resolved_location_id, resolved_status='in_stock', resolution_state='resolved', plus optional pb_number/cb_number. The other columns retain legacy source-Hasura import data. Renamed from device_staging on 2026-06-01.

Column	Type	Real-world meaning
`source_id`PK	TEXT	Internal PK. Real `<uuid>` from the source Hasura for imported rows; synthetic `audit-<uuid>` for the May-26 warehouse-audit backfill; auto-generated for production-line entries added via /devices/new.
`serial_number`UQ nullable	TEXT	The device's permanent identity. Hard-FK target of inventory_items.serial_no — every inventory row maps to exactly one row here by serial. UNIQUE; nullable only because legacy import quirks (Postgres allows multiple NULLs under a UNIQUE constraint), but in current data every row has a serial.
`display_name` nullable	TEXT	Source `displayName` — user-facing label. Legacy import field; NULL for production-line entries.
`device_type` nullable	TEXT	`upliance` \| `frodo` \| `airfryer_4.5L` \| `airfryer_6L` \| `airfryer_12L`. Drives the SKU mapping. Set on every row (legacy + production).
`device_category` nullable	TEXT	Coarser bucket than device_type (`upliance` \| `Airfryer` \| NULL). Legacy import only.
`raw_status` nullable	TEXT	Source `status` as-is (IN_PRODUCTION / DELIVERED / null / …). NOT used — `resolved_status` is the canonical one. Kept on legacy rows for audit; NULL on production rows.
`customer_id_raw` nullable	TEXT	Source `Customer_ID` — opaque text reference, not an FK. Legacy audit trail only.
`customer_trial_id` nullable	TEXT	Source `Customer_Trial_ID`. Empty across all current rows — kept for shape compatibility with the source.
`pb_number` nullable	TEXT	Power-board number — subcomponent identity.
`cb_number` nullable	TEXT	Control-board number — subcomponent identity.
`pb_id` nullable	UUID	Source pb_id — subcomponent UUID.
`cb_id` nullable	UUID	Source cb_id — subcomponent UUID.
`source_user` nullable	UUID	Source user column (opaque — refers to a user in the source system, not our users table).
`source_metadata` nullable	TEXT	Source metadata column as raw text. NOT JSONB — the source uses Python-dict format (single quotes, embedded newlines) that fails json.loads. Parsed lazily only if a field is actually needed.
`version` nullable	TEXT	Firmware version on the source.
`bytebeam_id` nullable	TEXT	ID on the bytebeam.io IoT platform — external reference.
`active_os` nullable	TEXT	Active OS image on the device per source.
`source_created_at` nullable	TIMESTAMPTZ	When the device was first created in the source system.
`source_updated_at` nullable	TIMESTAMPTZ	Last update timestamp in the source system.
`imported_at`	TIMESTAMPTZ	When this row landed in devices_master (load time for imports, insert time for production-line entries).
`resolved_location_id`FK → locations.id nullable	UUID	Initial / current location holder when the device is at a warehouse / office / FC. Mutually exclusive with resolved_contact_id (CHECK constraint). Production-line entries default to TES-BLR.
`resolved_contact_id`FK → contacts.id nullable	UUID	Initial / current holder when the device is out with a customer / employee / influencer. Mutually exclusive with resolved_location_id.
`resolved_sku_id`FK → skus.id nullable	UUID	The device's SKU. Required for any row that's going to be promoted into inventory_items. Set on every row in current state.
`resolved_status` nullable	ENUM	inventory_status (in_stock \| in_transit \| with_customer \| with_influencer \| with_employee \| at_amazon \| in_service \| written_off). Derived from the target type (location → in_stock / at_amazon; contact → with_*). Production-line entries always start as in_stock.
`resolution_state`	TEXT	`resolved` (in inventory) \| `unknown` (no location for the device — not yet promotable). Legacy states `unresolved`, `out_of_scope`, `needs_manual` were purged 2026-06-01.
`resolution_notes` nullable	TEXT	Provenance tag — `src=production` for production-line entries; `src=export_1` / `src=workings_v2` / `src=owner_pick` / `src=audit_seed` for legacy mappings.
`promoted_at` nullable	TIMESTAMPTZ	Set when this row got an inventory_items counterpart. NULL only for rows still in `unknown` state. Production-line rows have it set in the same transaction they're created.
`promoted_item_id` nullable	UUID	The matching inventory_items.id (soft pointer — no FK constraint; structural link is inventory_items.serial_no → devices_master.serial_number). Set whenever an inventory counterpart exists.