POS Database Design: Why My Retail POS Has Tickets and Bills
In a multi-department cloth store, a sale is recorded on one floor and paid for at a different counter. That one domain insight shaped my whole POS database design: tickets, bills, shifts and escalations in Prisma and PostgreSQL.
By Wasif Rehman · · SaaS architecture
In short
- Find the place where the real-world process splits what software habitually merges; here it was sale versus payment.
- Model tickets and bills as separate entities, joined by a table that records which slips were settled together.
- Keep stock per department so a wrong count points to a specific floor.
- Stamp every bill with its staffer and till so cash reconciliation becomes a query.
- Put authority in the schema: owner approvals and an append-only audit log for anything involving money.
How a multi-department store records sales and takes payment
I'm building a POS as a freelance project for Sher-e-Punjab Cloth House, a multi-department clothing store in Attock with a branch in Kamra. The full build is in the Multi-Department POS case study.
Before writing any code, I spent time understanding how the shop actually moves money. A customer browses the fabric floor and picks something. The department staff ring it up, but no money changes hands there. The customer gets a numbered slip and keeps shopping: another slip from menswear, another from the children's floor. At the exit sits one central counter where all the slips are combined and paid in a single transaction.
- Department rings up the item
- Numbered slip
- More slips from other floors
- Central counter combines them
- One payment
Miss that, and no amount of clean code makes the system match reality.
Tickets vs bills: separating sales from payments in the schema
So the schema has two distinct aggregates, one for each side of that split, joined by a table that is more than plumbing.
| Entity | Whose record | What it holds |
|---|---|---|
Ticket | A department's record of a sale | Its line items, its department, the staffer who rang it, a human-legible sequential number and a status |
Bill | The counter's record of a payment | The payment method, the checkout staffer, and a link to one or more tickets |
| The join table | The business event itself | Which slips were settled together in one payment |
The join table between tickets and bills isn't plumbing. It is the business event of "these three slips were settled together."
Returns and exchanges then hang off this structure with their own models. "The customer returns the menswear item from a three-ticket bill" has to be expressible precisely:
- Which line item
- Which decision
- Which refund method
- Settled how
Tracking stock per department, not per store
Inventory follows the same logic. Products and variants are catalog-level, but stock is a DepartmentStock row. The same shirt can be plentiful upstairs and sold out at the branch.
Shift tracking for cash drawer reconciliation
Cash is the other half of trust. A Shift records a drawer session:
- Who opened it
- How much float it started with
- The counted total at close
Because every bill is stamped with its staffer and till, expected versus counted is a query, not an argument.
Owner approvals and audit logs built into the schema
In a family-run store, some actions are the owner's alone: big discounts, voiding a ticket, an unusual refund. Rather than burying that in UI logic, there's an EscalationRequest model.
- The till raises a request A typed request for the action that needs the owner.
- The owner decides They approve or reject it.
- The decision stays linked It is permanently linked to the ticket it affected.
Sensitive actions also write to an append-only AuditLog. Together they give a simple rule:
I wrote more about why a deliberate approval step like this matters in Human-in-the-Loop Automation: When Not to Let AI Finish the Job.
Why Prisma 7 and PostgreSQL suit a POS data model
This domain is relational to its bones: tickets joining bills, stock per department, decisions linked to documents.
| Choice | What it gives |
|---|---|
| PostgreSQL | Real foreign keys and transactions. A bill and its ticket-status flips commit atomically or not at all. |
Prisma 7 with the pg driver adapter | A typed schema that doubles as living documentation of the domain model. |
The same schema is scoped per tenant from day one, which I cover in Multi-Tenant SaaS Architecture from Day One. The till and checkout screens built on top of it are covered in POS UI design for repetitive retail work.
Frequently asked questions
What is the difference between a ticket and a bill in a POS?
In my POS, a ticket is a department's record of a sale: its line items, department, staffer, sequential number and status. A bill is the central counter's record of a payment for one or more tickets, with the payment method and the checkout staffer.
Why separate sales and payments in a POS database?
Because in a multi-department store they happen in different places. Departments ring up sales, and one counter takes payment for all of a customer's slips at once. A schema that treats them as one event cannot match how the shop works.
How do you reconcile a cash drawer in a POS?
A Shift records who opened the drawer, the starting float and the counted total at close. Every bill is stamped with its staffer and till, so comparing expected and counted cash is a query.
Why use PostgreSQL instead of a document database for a POS?
The data is relational: tickets join bills, stock is per department, and decisions link to documents. Postgres gives real foreign keys and transactions, so a bill and its ticket-status changes commit together or not at all.
How should a POS handle discounts that need owner approval?
Model the approval as data. In my POS, a till raises a typed escalation request, the owner approves or rejects it, and the decision stays linked to the ticket. Sensitive actions also go to an append-only audit log.
Case study: Multi-Department POS