Database

MySagra uses MySQL as its primary database, managed via Prisma ORM . Migrations are applied automatically on API startup when MIGRATE_ON_START=true is set in the environment (default for the Docker Compose setup).

ER Diagram

Model reference

Model	Table	Description
`Category`	`categories`	Groups of foods. Each category can be linked to a specific printer for ticket routing.
`Food`	`foods`	Individual menu items. Belong to a category; can override the category printer.
`Ingredient`	`ingredients`	A shared pool of ingredients.
`FoodIngredient`	`food_ingredients`	Many-to-many join between `Food` and `Ingredient`.

Orders

Model	Table	Description
`Order`	`orders`	A customer order. Tracks table, customer name, payment method, totals, and status lifecycle.
`OrderItem`	`order_items`	A line item inside an order. Stores the food, quantity, unit price, and optional notes.
`DailyTicketCounter`	—	Incremental counter reset daily, used to generate the human-readable `ticketNumber` printed on receipts.

Order status lifecycle


PENDING → CONFIRMED → COMPLETED → PICKED_UP

Status	Meaning
`PENDING`	Order created, not yet confirmed by the cashier
`CONFIRMED`	Payment taken, ticket printed
`COMPLETED`	Food is ready for pickup
`PICKED_UP`	Customer collected the order

Users & authentication

Model	Table	Description
`Role`	`roles`	Named roles (e.g. `admin`, `cashier`). Controls API permissions.
`User`	`users`	Operator accounts. Each user has one role.
`RefreshToken`	`refresh_tokens`	JWT refresh tokens with expiry, revocation timestamp, and client metadata.

Hardware

Model	Table	Description
`Printer`	`printers`	ESC/POS network printers (IP + port). Status tracked as `ONLINE`, `OFFLINE`, or `ERROR`.
`CashRegister`	`cash_registers`	Named cash register stations. Each can have a default printer; orders are associated to a register.