AquaDealers
← All Docs
Billing is the primary feature of AquaDealers. Every bill creates a cascade of data changes across multiple tables in a single atomic transaction.
create_bill_v2 RPCA single PostgreSQL function that performs all of the following in one transaction. If any step fails, the entire transaction is rolled back — no partial state.
| # | Operation | Table |
|---|---|---|
| 1 | Insert bill header (number, date, farmer, totals, branch) | bills |
| 2 | Insert line items with quantities, rates, discounts | bill_items |
| 3 | Deduct stock from oldest FIFO lots per item | inventory_lots |
| 4 | Record inventory movements (reference_type = 'bill') | inventory_movements |
| 5 | Insert payment record (if amount_paid > 0) | payments |
| 6 | Allocate payment to this bill | payment_allocations |
| 7 | Create cash book entry (if cash payment) | cash_book |
| 8 | Update farmer due balance (via trigger) | farmers |
| 9 | Record transaction event for undo capability | transaction_events |
When the device is offline, bills are saved locally and synced when connectivity returns. The sync mechanism uses create_bill_offline_sync RPC with client_ref idempotency.
Each offline bill gets a client_ref — a UUID generated on the client when it is queued for offline sync. There is a unique index on (dealer_id, client_ref) on the bills table, and create_bill_offline_sync checks it before inserting, so re-running the sync queue never double-creates a bill.
client_ref alone does not cover every duplicate path: if an online create_bill_v2 call actually commits but its response is lost (e.g. on a flaky connection), the client cannot tell the difference from a request that never reached the server, and falls back to the offline queue with a new client_ref — which would slip past the client_ref check. To close this, every checkout also sends an idempotency_key, which survives that fallback unchanged (same payload object, same key). The key belongs to the cart content, not to the click: a repeated or retried save of the same cart reuses it, and it is cleared only after a successful save or when the cart changes (useCheckout.ts). The checkout handler also holds a synchronous lock for the whole save, because the Save button only disabled once the request started, after the session and duplicate checks had already awaited — three quick taps used to create two bills (QA audit 2026-10-15, BUG-01). create_bill_v2 itself checks (dealer_id, idempotency_key) first and returns the existing bill instead of inserting a duplicate — so the direct path and the offline-fallback path are both covered, no matter which client_ref the retry ends up using. A partial unique index on (dealer_id, idempotency_key) (ignoring nulls) backs this the same way.
create_bill_offline_sync propagates create_bill_v2's dedupe result into its own already_synced flag, not just its own client_ref check — otherwise a bill deduped via idempotency_key (new client_ref, pre-existing bill) would look "freshly synced" to the client and re-trigger the send-bill-whatsapp notification for a bill the farmer was already sent.
Up to 5 concurrent drafts stored in sessionStorage. Each draft preserves:
Drafts survive page refreshes within the same browser tab but not across tabs or after closing the browser.
5 invoice template designs available:
| Template | Style |
|---|---|
| TemplateOne | Clean minimal — product, qty, rate, amount |
| TemplateTwo | Detailed — includes HSN, MRP, discount columns |
| TemplateThree | GST-focused — full GST breakdown (CGST + SGST) |
| TemplateFour | Compact — smaller font, more items per page |
| TemplateFive | Modern — styled header, signature area prominent |
Each template supports toggling these columns on/off via Settings:
Templates are branch-specific — different branches can use different templates.
html-to-image renders the invoice DOM to a canvas (via an SVG <foreignObject>, not html2canvas's own CSS re-implementation), converts to PNG, then embeds in a jsPDF document. This ensures the PDF matches the on-screen invoice pixel-for-pixel. Changed 2026-09-18 from html2canvas, which threw Attempting to parse an unsupported color function "oklch" and failed every download once Tailwind v4's oklch()-based palette landed — html-to-image serializes computed styles as-is instead of re-parsing colors, so it doesn't hit this. html-to-image was already used elsewhere in the same file (WhatsApp image share) before this fix.jsPDF — directly draws text, tables, and lines for consistent layout regardless of screen rendering.Format: AD-{YYYYMMDD}-{sequence}
The sequence is a branch-specific auto-incrementing counter. Example: AD-20260909-042 is the 42nd bill from this branch on September 9, 2026.
Estimates (quotations) use the same billing flow but with no side effects.
| Aspect | Bill | Estimate |
|---|---|---|
| Database flag | is_estimate = false | is_estimate = true |
| Stock impact | Deducts from FIFO lots | None |
| Payment | Records payment, updates cash book | None |
| Farmer due | Increases outstanding balance | No change |
| WhatsApp notification | Sent (if configured) | Not sent |
| Listed in | /bill-history | /estimates |
| Conversion | — | Can be converted to a real bill |
When an estimate is converted to a bill, the system creates a new bill using the estimate's items and runs the full create_bill_v2 transaction. The estimate record is updated with a reference to the new bill.
Returns handle the reversal of sold goods. There are two entry points that both restore stock and adjust balances, but differ in scope:
create_bill_return): return against one specific bill, started from Bill Details.create_farmer_return_v1): return started from the farmer, spanning a date range across that farmer's bills; the server auto-allocates the returned quantity across the matching bill_items (most recent bill first) and reports any leftover as an "unmatched" line at a dealer-entered price.bill_returns — shared header for both flows: linked bill (bill-first) or farmer + date range (farmer-first), return date, total amount, settlement methodbill_return_items — bill-first line items: product, quantity returned, bill_item_idfarmer_return_lines — farmer-first line items: product, requested/matched/unmatched quantity, unmatched pricebill_return_allocations — farmer-first: how a farmer_return_lines row was split across specific bill_items (one row per bill hit)Both bill_return_items.bill_item_id and bill_return_allocations.bill_item_id are what get_farmer_items_v1/get_farmer_item_bills_v1 sum up to net returned quantity/value out of the farmer's purchase totals (see below) — a return only ever reduces a bill_item's effective quantity for reporting, it never edits the original bill_items row.
Bill-first returns restock the same lots the sale consumed, walked in the same order they were consumed (earliest-consumed lot first — i.e. FIFO order, not LIFO), via bill_item_lot_allocations. If a lot record is gone, restocking falls back to the inventory row's quantity_in_stock directly.
Farmer-first returns aren't tied to specific bill_item_lot_allocations rows (a farmer-first return spans a date range, not one bill), so they can't restock the original lot(s). Instead the returned quantity goes into one synthetic inventory_lots row per product per branch, batch_number = 'CUSTOMER-RETURN', shared across every farmer-first return of that product — a later return tops up the same lot's quantities rather than creating a new one. See Inventory Logic: the CUSTOMER-RETURN lot.
create_farmer_return_v1 was rewritten from an older copy of the function and lost the shared-lot reuse above — undo_transaction_v1 still assumed it (decrementing the lot rather than deleting it), but every new farmer-first return on a product with a prior return created a second CUSTOMER-RETURN lot instead of topping up the first. That also meant the lot's mrp/selling_price were never set (only ever true for this batch — the fields aren't populated on insert), so Billing's batch picker showed the returned stock's quantity split across duplicate rows and its price as ₹0. Separately, and unrelated to this regression: ProductSelector's "Add to Bill" was assuming getLotsWithStock() returns lots newest-first and read it back-to-front to find the oldest one with room — but it actually returns oldest-first (FIFO), so the reversed read picked the newest lot instead, most visible once a product had a recent, differently-priced CUSTOMER-RETURN lot in the mix. Both are fixed: the RPC restores the shared-lot reuse and seeds pricing from the inventory row (existing null-priced lots were backfilled; existing duplicate lots from the affected window were left alone rather than merged, to avoid corrupting their undo history), and lot selection now walks getLotsWithStock() in its actual order via a small, unit-tested pickLotWithCapacity() helper in src/features/inventory/utils/pricing.ts.How the excess (return value beyond what's owed) is handled differs by flow:
balance_due is clamped at 0 and farmers.total_due is recomputed by recalculate_farmer_due_v1, which is also floored at 0 — so any excess beyond the bill's remaining balance is not credited anywhere (no negative balance, no return_credit_balance, no cash refund entry).settlement_method — farmer_credit adds to farmers.return_credit_balance (applied to the farmer's next bill), or cash_refund logs an expense in cash_book instead.Every return creates inventory movement records (reference_type = 'bill_return' for bill-first, 'farmer_return' for farmer-first) pointing to the return, plus a transaction_events row so it shows up in and can be undone from /transactions.
| RPC | Purpose |
|---|---|
create_bill_return | Bill-first: execute a return against one bill's items — restock lots, reduce that bill's balance, refresh farmer due |
get_returns_for_bill | List returns (either flow) that touched a given bill, for the Bill Details page |
preview_farmer_return_v1 | Farmer-first: preview what will happen — shows affected bills, amounts, credit calculation — before committing |
create_farmer_return_v1 | Farmer-first: execute the return — restore stock, adjust farmer balance, create audit entries |
replace_farmer_return_v1 | Farmer-first: replace (edit) an existing return — undoes the previous and applies the new |
get_farmer_items_v1 and get_farmer_item_bills_v1 (the farmer detail page's Items tab and its per-item bill drilldown) used to sum bill_items directly with no idea returns existed, so a farmer's purchased quantity/value never dropped after a return — stock correctly went back up, but the farmer-facing numbers were stuck at pre-return values indefinitely. Both RPCs now subtract bill_return_items + bill_return_allocations per bill_item before aggregating. Bills, statements, the ledger, and farmer due totals were never miscalculated by this bug — bills.balance_due/farmers.total_due are updated directly by the return RPCs, independent of these two aggregation functions./bills) and the farmer's own Bills tab (/farmers/:id) showed no indicator at all — no badge, no column — for any bill with a return, in either the desktop table or the mobile card. Bill Details (/bills/:id) only showed a return in a card at the very bottom of the page with no indicator above the fold, and its per-item "N sold − M returned" note only counted bill_return_allocations, silently missing bill-first returns recorded in bill_return_items on the same line. A farmer's Ledger tab return entries were not clickable at all — no way to see what was actually returned without leaving the page. The Balance Statement PDF (getFarmerStatement() + farmerStatementPdf.ts) fetched only the bill_returns header with no line items from either return table, and its template only ever printed an amount for bill/payment rows — a return row showed -/N/A in both amount columns despite the running balance visibly dropping. Separately, getFarmerReturnDetail() (used by the Returns page's own detail modal) only ever queried farmer_return_lines, so opening a bill-first return there silently showed zero items.bill_returns.bill_id for bill-first, bill_return_allocations.bill_id for farmer-first) — Bill History reads "Return Filed" (updated 2026-09-17 to match Bill Details' wording, since a plain "Return" badge next to the branch badge read as if the row itself were a return rather than a bill with a return against it), while the farmer's Bills tab still reads "Return". Bill Details shows the same "Return Filed" badge that jumps to the returns section and counts both return tables per item. A farmer's Ledger tab return entries now open a read-only item/amount breakdown on click. The statement lists each return's items and prints its amount (in amber) in the PAID(-) column instead of a blank placeholder. getFarmerReturnDetail() now merges items from both farmer_return_lines and bill_return_items.Add-on lot_expiry_sync, turned on per dealer from Admin → Add-ons → Apply Expiry to Older Stock or the dealer's Subscription tab. On New Purchase, a medicine line with an expiry date shows a tick-box Also set this expiry on older stock of this product. When ticked, record_stock_purchase_v2 (payload apply_expiry_to_existing_lots) writes that expiry to the product's older lots in the same branch that still have stock and are not already past expiry. Sold-out lots, processed-expired lots and the old purchase records are not changed. Staff who can add stock can use it. The RPC rejects the flag if the add-on is off, or if the new expiry is in the past (a mistyped past date would otherwise expire and write off every older lot); the tick-box only shows for an expiry of today or later.
Every dealer has it. A read-only history of one farmer: all bills, payments and returns in a period. Nothing in it changes any money. Renamed 2026-10-25 (was View Statement / Balance Statement; the code still uses BalanceStatementModal).
farmerStatementPdf.ts, headed ACCOUNT STATEMENT) and Share via WhatsApp (statementMessage, PDF attached). With the Payment Requests add-on (and, for staff, the farmerStatements permission) a separate green row under it reads Want the farmer to pay? (Change prices or add interest in a payment request.) with a Create request button that opens /farmers/:id/statements/new./f/:token (shared from Share Balance) shows the farmer their balance and history; its page title is Account Statement.Add-on farmer_statements, added 2026-10-25, turned on per dealer from Admin → Add-ons → Farmer Payment Requests or the dealer's Subscription tab (dealers.custom_features, or a plan whose plan_definitions.features includes it). Shown in the app as Payment Request; the code keeps the statement names (table farmer_statements, RPCs *_farmer_statement_v1, routes /farmers/:id/statements/..., staff key farmerStatements). Not the same as the Account Statement (read-only history, PDF and /f/:token link), which every dealer has, and not a settlement discount. Money rules are in Financial Logic.
/farmers/:id?tab=statements), shown only with the add-on and, for staff, the farmerStatements permission. The tab shows a count of unpaid requests. It opens with a one-line explanation and a link to the Account Statement for farmers who only need the history; New payment request starts the builder. A tab link opened without access falls back to the Ledger tab.LedgerActions.tsx); the Want the farmer to pay? · Create request row in the Account Statement modal; a green bar above the farmer tabs while a request is unpaid, Payment request PR-xxxx · ₹… to pay by … with Open and +n more (WaitingRequestBanner.tsx); and a notice on a bill's details page when that bill is on an unpaid request, saying a payment or edit there means the request needs a refresh (BillRequestNotice.tsx, query getRequestsForBill in statementService.ts).get_statement_bills_v1: active, non-estimate sales with a balance, by bill date). Opening balance and return credit are not included, and the page says so.get_statement_bills_v1 now returns product_type (joined from products.type) on each item (migration 20261026000001_statement_bills_product_type.sql).SaveReview.tsx, added 2026-10-25): Review & save (desktop) or Review (phone bar) opens a dialog titled Review payment request (Review changes to PR-xxxx when editing). It shows the total with Unpaid on bills / Price change / Interest; Bills on this request (n) with each bill's owed amount old → new and its interest; Price changes (n) with item, bill, qty, old → new rate and the change on that line; the interest rule in words with its total and how many items have typed interest; Not on this request (unticked bills, opening balance, farmer credit); the note; the high-rate warning when it applies; and What happens when you save (a new request with its own number and link, or the same number and link when editing; no bill, payment or due changes now; prices, payments and interest only on Mark paid). Buttons: Back to edit (keeps every edit) and Save payment request (Save changes when editing).save_farmer_statement_v1) stores a draft numbered PR-0001, … per dealer (requests made before 2026-10-25 keep their old ST- number; the count carries on across both, migration 20261025000001_payment_request_numbers.sql), with a snapshot of the bills and items. If a bill changed since it was loaded, the save is refused (STATEMENT_STALE), the review closes and the bills reload./farmers/:id/statements/:sid): Download PDF (statementPdf.ts, jsPDF, headed PAYMENT REQUEST with Request for the farmer and Pay by, amounts printed as "Rs"; each bill is its own numbered, boxed table with a light blue heading, its items and a bill-total row), Print and WhatsApp (farmerStatementShareMessage with the public link; needs a farmer phone) sit in the page header; below, a status card shows the total, the pay-by date with days left / overdue (or the paid date and method) and the Mark paid / Edit / Cancel buttons. The farmer's /s/:token page also has Download PDF and Print. A draft also has Edit (/farmers/:id/statements/:sid/edit), Cancel (cancel_farmer_statement_v1) and Mark paid. Paid and cancelled requests show a stamp and cannot be changed.settle_farmer_statement_v1, MarkPaidModal.tsx), two steps since 2026-10-25. 1 Payment: a table of each bill's old → new total, payment and interest, then Paid by (cash / UPI / bank / cheque, with optional UPI ref or cheque number), Payment date (today or earlier), the Receive total and Review changes. 2 Review (titled Review: mark PR-xxxx paid): the amount received with date and method, split into bills + interest; Bills that become fully paid (n), each Due x → 0 and, when repriced, bill total old → new; Prices saved on the bills (n) old → new, with a note that those bills show as Edited with an edit history and that GST and stock costs follow; Money recorded per shop (a farmer payment with a new receipt against n bills, and an interest income row) and whether the Cash & bank balance goes up (cash / bank) or not (UPI / cheque); Farmer and reports: farmer due before → after (interest is not added to dues), interest added to profit on the payment date, and the request becoming Paid with its link showing paid; then a warning that the payment cannot be undone on its own from Transactions. Back returns to step 1; Confirm paid writes the new prices to the bills, clears every bill and books the interest as income. Full payment only. If a bill changed after saving, the dialog lists those bills and offers Refresh request, which re-saves the request from the current amounts of the same bills and keeps the price and typed-interest edits./s/:token (no login, get_public_farmer_statement_v1): the shop, the farmer and the payment request with every bill and item, plus a Print button. Each request has its own random share_token.farmer_payment row "… (payment request PR-xxxx)" and statement_interest row "Interest on payment request PR-xxxx"; rows paid before migration 20261025000002 keep the old "statement" text), and the Profit Report's Interest Income tile. The request's payment cannot be undone on its own from Transactions.src/features/farmers/statements/ (statementMath.ts, statementService.ts, useStatements.ts, builder, save review SaveReview.tsx, view, Mark Paid modal, public page, WaitingRequestBanner.tsx, BillRequestNotice.tsx). Database: table farmer_statements, migrations 20261025000000_farmer_statements.sql, 20261025000001_payment_request_numbers.sql, and 20261026000001_statement_bills_product_type.sql (adds product_type to the get_statement_bills_v1 response).Sends stock back to the supplier it was bought from — expired medicine, damaged feed, a wrong item. Added 2026-10-15. The money side is in Financial Logic.
/suppliers/:id, next to New Purchase) and Return items on a purchase detail page, which opens /suppliers/:id/return?purchase=<id> with that invoice listed first. Both need the inventoryAddStock permission and a selected shop.total_amount / quantity (GST included) and can only be lowered.supplier_returns, supplier_return_lines; RPCs get_supplier_returnable_v1 (the list) and create_supplier_return_v1 (idempotency key per attempt, so a retried save is not recorded twice).Transfer stock between branches while preserving FIFO lot data. Each transfer creates movement records at both ends.
create_stock_transfer RPCtype = 'transfer_out'type = 'transfer_in'reference_idedit_bill_v1Editing quantities and prices needs the edit_bills feature (Pro and Pro+ plans). Adding, removing and changing products is a separate add-on, bill_item_edit: an admin turns it on per dealer from Admin → Add-ons → Bill Item Editing or the dealer's Subscription tab, which writes dealers.custom_features. A plan whose plan_definitions.features includes it also counts. The RPC refuses removed_item_ids / added_items without it, and the edit screen hides those controls. Staff also need the Transactions permission. One call can change, remove and add lines together, and it either applies fully or not at all. Payload keys: edits (bill_item_id, quantity, unit_price), removed_item_ids, added_items (inventory_id, quantity, unit_price, discount_percentage, gst_rate), excess_mode, idempotency_key and bill_date. A call that only sends edits still works.
replaces_bill_item_id, which is recorded only in the audit, so Edit History reads "Added 3SP (in place of Gell)".edit_bill_payment_v1)bill_return_items or farmer-first bill_return_allocations) can't be removed or repriced, and their quantity can't go below what was already returned. Other lines on the same bill stay editable.inventory.branch_id = bills.branch_id), the same rule as checkout.bill_item_lot_allocations newest allocation first (ORDER BY created_at DESC). This is the opposite order from bill-first returns, which restock FIFO. If the original lot has since been marked expired, the units go into the shop's live CUSTOMER-RETURN lot instead, so they stay sellable. Each step writes a positive bill_edit movement.preview_fifo_bill_lines.total − amount_paid: new due = old due + (new total − old total) + (old settlement − new settlement).farmers.return_credit_balance and used automatically on their next bill.cash_book expense row with source bill_edit_refund for the bill's shop, dated today.EXCESS_DECISION_REQUIRED:<amount>, and the received amount can't be edited in the same save.transaction_events row is updated to the new total and due.Each edit session sends an idempotency_key. A retried save with the same key returns the first result and changes nothing.
Every edit creates a record in bill_audit_logs.changes_jsonb with:
items: changed lines (old/new quantity and price)added and removedold_total/new_totalold_balance_due/new_balance_dueold_settlement/new_settlementexcess (amount and credit/refund)idempotency_keyresultEach record also stores user_id and a timestamp. The bill is flagged is_edited = true, and the Edit History panel on Bill Details shows every entry.
cancel_bill_v1| Constraint | Rule | Reason |
|---|---|---|
| Timing | Same-day only | Prevents backdated financial manipulation |
| Payment | amount_paid must be 0 | Bills with payments must be handled via returns or editing |
| Status | Must not already be cancelled | Idempotency — returns 'already_cancelled' without error |
status = 'cancelled'bill_item_lot_allocations row: the lot's remaining_quantity and inventory.quantity_in_stock both go back up, then the allocation is deleted. Estimates and other non-stock bills have no allocations, so cancelling them changes no stock.balance_due to 0 and reduces farmers.total_due by the old balance (floored at 0)reference_type = 'bill_cancellation' and its lot_idcancelled_at and cancelled_reason on the bill20261015000000_cancel_bill_restores_lots.sql): this RPC used to add each item's quantity to inventory only. The consumed lots stayed depleted, so the stock was invisible to the next FIFO sale, and cancelling an estimate added stock that had never left. The app has no button that calls cancel_bill_v1 (cancelling in the UI is Undo on /transactions, which already restored lots), and no live bill was ever cancelled through it, so no data needed correcting.
cancel_bill_v1 on an already-cancelled bill returns 'already_cancelled' status without error or side effects. Safe to retry.
A universal undo system that covers all financial transactions in the application.
| Type | Undo Behavior |
|---|---|
| Bills | Cancels the bill, restores stock, reverses farmer due |
| Purchases | Removes inventory lots, reverses supplier balance |
| Payments (farmer) | Reverses payment allocation, restores farmer due |
| Supplier Payments | Reverses payment, restores supplier balance |
| Returns | Re-deducts returned stock, reverses farmer credit |
| Supplier Returns | Puts each line's units back into the lot it was taken from, restores the supplier due (reduce-due returns) or removes the cash refund row |
| Stock Transfers | Reverses transfer at both branches |
| Expenses | Removes expense record and cash book entry |
| Other/Uncategorized Cash Entries | Removes the entry from cash book — the catch-all category for any cash_book row whose source doesn't match a recognized type. There's no feature to actually create one; it exists only as an undo fallback. |
transaction_events records every financial transaction with type, reference ID, timestamp, and actorundo_transaction_v1deleted_at or cancelled_at timestamps rather than hard deletion. This preserves the audit trail — undone transactions are still visible but marked as undone.Add-on ai_connector, added 2026-10-25, turned on per dealer from Admin → Add-ons → AI Assistant (ChatGPT / Claude). The dealer connects their own ChatGPT or Claude account; AquaDealers runs no AI itself and pays no AI cost.
/settings/ai) shows the connector link with Copy, and short steps for ChatGPT (Developer mode → custom connector, OAuth) and Claude (Connectors → Add custom connector). The AI app opens AquaDealers, the dealer logs in and taps Allow on /oauth/consent.app_help, from supabase/functions/mcp/guide.ts).confirmed: true; the server refuses a write without it. Entries made by the AI carry an [AI] prefix in their notes (ledger, expense list, cash book) and an AI badge on their row in Transactions (transaction_events.details.by_ai, migration 20261025000006). They are ordinary farmer payment / expense / supplier payment events, so the owner can Undo them there within the 20-day window like any other entry; the AI itself cannot undo (the write guard blocks it). A farmer added by the AI has no transaction event; it is edited or removed on the farmer's page.