Database schema

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:

drizzle.config.ts

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.

Below is the full Drizzle definition for this table:

src/db/schema.ts

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

`detalle_item_extras`

Optional extras attached to a line item. The extra can reference a known ingredient or use a free-text description.

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

Get Started

Architecture

Guides

Deployment

Soft-delete pattern

Tables

`usuarios`

`caja_turno`

`gastos_caja`

`productos`

`ingredientes`

`platos`

`plato_ingredientes`

`transacciones`

`detalle_items`

`detalle_item_extras`

`pagos`

Relationships

Build docs developers (and LLMs) love

Get Started

Architecture

Guides

Deployment

​Soft-delete pattern

​Tables

​usuarios

​caja_turno

​gastos_caja

​productos

​ingredientes

​platos

​plato_ingredientes

​transacciones

​detalle_items

​detalle_item_extras

​pagos

​Relationships

Build docs developers (and LLMs) love

Soft-delete pattern

Tables

`usuarios`

`caja_turno`

`gastos_caja`

`productos`

`ingredientes`

`platos`

`plato_ingredientes`

`transacciones`

`detalle_items`

`detalle_item_extras`

`pagos`

Relationships