StoreManagement
Database
Postgres on Supabase, in three schemas, with the reporting done by views rather than by Dart.
Three schemas, one database
Separate schemas keep the three features’ tables from colliding on names, and make it obvious which feature owns a table. They’re one database, though, and that’s the point — the books are kept about the cash and the stock, not alongside them.
The tables
| Schema | Tables |
|---|---|
public | daily_cash_summary, expenses, expense_payments, receivables, salaries, staff, staff_advances, advance_payments, advance_settlements, commissions, commission_payments, gcash_transfers |
inventory | products, product_terms, stock_movements, stock_counts, stock_count_lines |
bookkeeping | accounts, journal_entries, journal_entry_lines |
Reports are views
Almost every report is a database view, not Dart. The app selects from it and renders rows. That keeps the arithmetic next to the data, survives a rewrite of the client, and means a report can be checked in the SQL editor without running the app.
| View | Answers |
|---|---|
daily_cash_report | What a single day looked like, once every payment and settlement is folded in |
gcash_balance | Running GCash position from the transfer ledger |
inventory.product_on_hand | Current stock per product, from movements rather than a stored count |
inventory.low_stock | What's below its reorder point |
inventory.orders_daily | Stock received per day |
inventory.sales_daily | Stock out per day, in units and value |
inventory.adjustments_daily | Wastage, supplies used, corrections and returns per day — grouped by reason, and valued at the prices in force that day |
inventory.sales_reconciliation | Stock sold against cash recorded — the two features checking each other |
inventory.count_variance | A physical count against what the system believed |
bookkeeping.general_ledger | Every posted line, per account, with a running balance |
bookkeeping.trial_balance | Debits and credits per account, which must agree |
bookkeeping.unbalanced_entries | Entries that don't balance — should always be empty; it exists to prove it |
Why on-hand is derived, not stored
A stored quantity has to be right after every write, forever, and drifts the first time one fails halfway. Deriving it from stock_movements means the movement log is the truth and on-hand is always consistent with it — and a physical count becomes a comparison rather than a correction.
Migrations
Plain numbered SQL in supabase/migrations/, applied in order, never edited once applied. 0000 is the DailyCash baseline; the current head is 0023.
supabase/migrations/
0000_daily_cash_baseline.sql DailyCash, from scratch
0001_inventory_schema.sql StockRoom arrives
0002_sales_reconciliation.sql stock sold vs cash taken
0004_bookkeeping_schema.sql accounts, journals, ledger
0008_expected_bills_claim.sql bills owed but not yet paid
0010_expense_funding_sources.sql who actually paid: cash, GCash, a card
0012_invoice_and_gcash_ledger.sql
0017_product_terms_versioning.sql prices change; history must not
0018_orders_daily_view.sql
0019_sales_daily_view.sql
0020_replace_sales_for_date.sql re-record a day without duplicating it
0021_adjustments_daily_view.sql wastage, usage and corrections per dayThe numbering is the ordering. A migration that’s been applied to the live database is history — a correction is a new file, never a change to an old one.
A note on prices
0017_product_terms_versioning exists because a franchise price changing shouldn’t rewrite what last month cost. Terms are versioned with effective dates, so a report about March uses March’s prices even if the product was repriced in April.