All tables are defined in src/db/schema.ts using Drizzle ORM’s pgTable builder and migrated with drizzle-kit. The Drizzle config reads the connection string from the DATABASE_URL environment variable:
export default defineConfig({
schema: './src/db/schema.ts',
out: './drizzle',
dialect: 'postgresql',
dbCredentials: { url: process.env.DATABASE_URL },
});
Soft-delete pattern
Most tables include a borrado_en column of type timestamp with time zone. A NULL value means the record is active. When you “delete” a record the application sets borrado_en to the current timestamp instead of issuing a DELETE statement. All queries filter by borrado_en IS NULL to return only active rows.
Tables
usuarios
Stores staff accounts. The primary key is a text UUID generated by the application.
| Column | Type | Notes |
|---|
id | text | Primary key (app-generated UUID) |
nombre | varchar(60) | Full name |
nombre_usuario | varchar(30) | Login username |
contrasena | varchar(255) | Hashed password |
rol | varchar(20) | Default 'cajero' |
creado_en | timestamptz | Auto-set on insert |
actualizado_en | timestamptz | Auto-set on insert |
borrado_en | timestamptz | Soft-delete timestamp |
caja_turno
Represents a single cash-register shift. Tracks denomination counts, sales by payment method, and total cash-outs.
| Column | Type | Notes |
|---|
id | serial | Primary key |
fecha | date | Shift date |
hora_apertura | timestamptz | Opening time, default now |
hora_cierre | timestamptz | Closing time (nullable) |
usuario_id | text | FK → usuarios.id |
monto_inicial | numeric(10,2) | Opening cash amount |
b200…m010 | integer | Bill/coin denomination counts |
ventas_efectivo | numeric(10,2) | Cash sales total |
ventas_qr | numeric(10,2) | QR sales total |
total_salidas | numeric(10,2) | Total cash-outs |
cerrada | boolean | false while shift is open |
cierre_obs | text | Closing observations (nullable) |
gastos_caja
Expenses recorded against an open shift.
| Column | Type | Notes |
|---|
id | serial | Primary key |
caja_id | integer | FK → caja_turno.id (cascade delete) |
usuario_id | text | FK → usuarios.id |
descripcion | text | Expense description |
metodo_pago | varchar(20) | Payment method |
monto | numeric(10,2) | Amount |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
productos
Retail products (non-dish items) sold by unit.
| Column | Type | Notes |
|---|
id | text | Primary key |
nombre | varchar(60) | Product name |
precio | numeric(10,2) | Unit price |
stock | integer | Current stock, default 0 |
unidad | varchar(20) | Unit of measure |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
ingredientes
Kitchen ingredients with minimum-stock alerts.
| Column | Type | Notes |
|---|
id | text | Primary key |
nombre | varchar(100) | Ingredient name |
unidad | varchar(20) | Unit of measure |
cantidad | double precision | Current quantity, default 0 |
cantidad_minima | double precision | Minimum alert threshold, default 0 |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
platos
Menu dishes.
| Column | Type | Notes |
|---|
id | text | Primary key |
nombre | varchar(60) | Dish name |
precio | numeric(10,2) | Price |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
plato_ingredientes
Join table linking dishes to their ingredient requirements. Uses a composite primary key.
| Column | Type | Notes |
|---|
plato_id | text | PK + FK → platos.id (cascade delete) |
ingrediente_id | text | PK + FK → ingredientes.id |
cantidad | double precision | Quantity needed per serving |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
transacciones
The central sales table. Each row is one order.
| Column | Type | Notes |
|---|
id | serial | Primary key |
nro_reg | integer | Human-readable order number |
fecha | date | Order date |
hora | timestamptz | Exact order time |
tipo | varchar(30) | Default 'venta' |
concepto | text | Order description |
monto_total | numeric(10,2) | Total amount |
monto_pagado | numeric(10,2) | Amount paid so far |
mesa | varchar(50) | e.g. 'Mesa 5', 'Para llevar', 'Delivery' |
cliente | varchar(100) | Customer name (nullable) |
estado | varchar(20) | pendiente | abierto | cerrado |
estado_cocina | varchar(20) | pendiente | terminado |
caja_id | integer | FK → caja_turno.id |
usuario_id | text | FK → usuarios.id |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
monto_pendiente is not stored. Compute it as monto_total - monto_pagado.
export const transacciones = pgTable('transacciones', {
id: serial('id').primaryKey(),
nro_reg: integer('nro_reg').notNull(),
fecha: date('fecha').defaultNow(),
hora: timestamp('hora', { withTimezone: true }).defaultNow(),
tipo: varchar('tipo', { length: 30 }).default('venta'),
concepto: text('concepto').notNull(),
monto_total: numeric('monto_total', { precision: 10, scale: 2 })
.notNull()
.default('0'),
monto_pagado: numeric('monto_pagado', { precision: 10, scale: 2 })
.notNull()
.default('0'),
mesa: varchar('mesa', { length: 50 }),
cliente: varchar('cliente', { length: 100 }),
estado: varchar('estado', { length: 20 }).default('pendiente'),
estado_cocina: varchar('estado_cocina', { length: 20 }).default('pendiente'),
caja_id: integer('caja_id').references(() => caja_turno.id),
usuario_id: text('usuario_id').references(() => usuarios.id),
creado_en: timestamp('creado_en', { withTimezone: true })
.defaultNow()
.notNull(),
actualizado_en: timestamp('actualizado_en', { withTimezone: true })
.defaultNow()
.notNull(),
borrado_en: timestamp('borrado_en', { withTimezone: true }),
});
detalle_items
Line items for a transaction. Each row is either a producto or a plato (the two foreign keys are mutually exclusive).
| Column | Type | Notes |
|---|
id | serial | Primary key |
transaccion_id | integer | FK → transacciones.id (cascade delete) |
producto_id | text | FK → productos.id (nullable) |
plato_id | text | FK → platos.id (nullable) |
cantidad | numeric(10,2) | Quantity ordered |
precio_unitario | numeric(10,2) | Unit price at time of sale |
subtotal | numeric(10,2) | cantidad × precio_unitario |
notas | text | Per-item customer notes, e.g. 'Sin cebolla' |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
| Column | Type | Notes |
|---|
id | serial | Primary key |
detalle_item_id | integer | FK → detalle_items.id (cascade delete) |
ingrediente_id | text | FK → ingredientes.id (nullable) |
descripcion | text | Free-text extra, e.g. 'Extra queso' |
precio | numeric(10,2) | Extra price |
cantidad | numeric(10,2) | Default 1 |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
pagos
Payment records for a transaction. Multiple payments are allowed per transaction (split payments).
| Column | Type | Notes |
|---|
id | serial | Primary key |
transaccion_id | integer | FK → transacciones.id (cascade delete) |
metodo_pago | varchar(20) | 'efectivo' or 'qr' |
monto | numeric(10,2) | Amount to pay |
monto_recibido | numeric(10,2) | Cash received (cash payments only) |
referencia_qr | varchar(100) | QR transaction reference (nullable) |
usuario_id | text | FK → usuarios.id |
creado_en | timestamptz | — |
actualizado_en | timestamptz | — |
borrado_en | timestamptz | Soft-delete timestamp |
cambio (change due) is not stored. Compute it as monto_recibido - monto for cash payments.
Relationships
usuarios
├── caja_turno.usuario_id
├── gastos_caja.usuario_id
├── transacciones.usuario_id
└── pagos.usuario_id
caja_turno
├── gastos_caja.caja_id (cascade delete)
└── transacciones.caja_id
transacciones
├── detalle_items.transaccion_id (cascade delete)
└── pagos.transaccion_id (cascade delete)
detalle_items
├── detalle_item_extras.detalle_item_id (cascade delete)
├── productos.producto_id
└── platos.plato_id
detalle_item_extras
└── ingredientes.ingrediente_id
platos
└── plato_ingredientes.plato_id (cascade delete)
ingredientes
└── plato_ingredientes.ingrediente_id