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
erDiagram
Category {
String id PK
String name
Boolean available
Int position
String image
String printerId FK
}
Ingredient {
String id PK
String name
}
Food {
String id PK
String name
String description
Decimal price
Boolean available
String categoryId FK
String printerId FK
}
FoodIngredient {
String foodId FK
String ingredientId FK
}
Order {
Int id PK
String displayCode
String table
String customer
DateTime createdAt
DateTime confirmedAt
Int ticketNumber
OrderStatus status
PaymentMethod paymentMethod
Decimal subTotal
Decimal discount
Decimal surcharge
Decimal total
String userId FK
String cashRegisterId FK
}
OrderItem {
String id PK
Int quantity
Int orderId FK
String foodId FK
Decimal unitPrice
Decimal unitSurcharge
Decimal total
String notes
}
Role {
String id PK
String name
}
User {
String id PK
String username
String password
String roleId FK
}
RefreshToken {
String id PK
String token
String userId FK
DateTime createdAt
DateTime expiresAt
DateTime revokedAt
String ip
String userAgent
}
Printer {
String id PK
String name
String ip
String mac
Int port
String description
PrinterStatus status
}
CashRegister {
String id PK
String name
Boolean enabled
String defaultPrinterId FK
}
ApiKey {
String id PK
String hash_key
String prefix
String last_digits
ApiKeyType type
String name
DateTime createdAt
DateTime lastUsedAt
DateTime revokedAt
}
Banner {
String id PK
String name
BannerType type
String title
String description
String website
String instagram
String facebook
String image
String color
DateTime dateTime
}
OrderInstruction {
String id PK
String text
Int position
}
Report {
String id PK
DateTime timestamp
Int intervalInMinutes
Decimal totalRevenue
Decimal totalCashRevenue
Decimal totalCardRevenue
Int totalOrders
Int averageCompletitionTime
}
CategoryStats {
String id PK
String categoryId FK
String reportId FK
Int itemsSold
Decimal revenue
}
FoodStats {
String id PK
String foodId FK
String reportId FK
Int itemsSold
Decimal revenue
}
UserStats {
String id PK
String userId FK
String reportId FK
Int ordersProcessed
Decimal totalHandled
}
Category }o--o| Printer : "printerId"
Food }o--|| Category : "categoryId"
Food }o--o| Printer : "printerId"
FoodIngredient }|--|| Food : "foodId"
FoodIngredient }|--|| Ingredient : "ingredientId"
Order }o--o| User : "userId"
Order }o--o| CashRegister : "cashRegisterId"
OrderItem }|--|| Order : "orderId"
OrderItem }|--|| Food : "foodId"
User }|--|| Role : "roleId"
RefreshToken }|--|| User : "userId"
CashRegister }o--o| Printer : "defaultPrinterId"
CategoryStats }|--|| Category : "categoryId"
CategoryStats }|--|| Report : "reportId"
FoodStats }|--|| Food : "foodId"
FoodStats }|--|| Report : "reportId"
UserStats }|--|| User : "userId"
UserStats }|--|| Report : "reportId"
Model reference
Menu
| 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. |
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. MAC address optional for identification. |
CashRegister | cash_registers | Named cash register stations. Each can have a default printer; orders are associated to a register. |
Configuration & Reporting
| Model | Table | Description |
|---|---|---|
ApiKey | api_keys | Machine-to-machine authentication keys. Two types: PRINTER (prefix ms_pt_) and WEBAPP (prefix ms_wb_). |
Banner | banners | Promotional images for POS and customer screens. Type: EVENT (branding) or SPONSOR (logos). |
OrderInstruction | order_instructions | Instructional text shown to cashiers during order creation, sorted by position. |
Report | reports | Aggregated sales statistics snapshots, grouped by time window (hourly, daily, or full-period). Linked to granular stats per category, food, and user. |
CategoryStats | category_stats | Per-category sales metrics for a given report period. Tracks items sold and revenue by category. |
FoodStats | food_stats | Per-food sales metrics for a given report period. Tracks items sold and revenue by menu item. |
UserStats | user_stats | Per-operator performance metrics for a given report period. Tracks orders processed and total cash/card handled. |
Last updated on