SQLAlchemy data models and schema reference

Warehouse

Represents a physical storage location. Every client has exactly one "principal" warehouse (the main stock store) and any number of "obra" (work-site) warehouses. The principal warehouse is resolved by matching owner_id and type == "principal".

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`name`	`String`	No	Human-readable warehouse name.
`type`	`String`	No	`"principal"` or `"obra"`.
`location`	`String`	Yes	City or district (informational).
`address`	`String`	Yes	Physical street address (informational).
`owner_id`	`Integer`	Yes	FK → `users.id`. `NULL` for legacy warehouses without an assigned owner.

Relationships

inventory → list of Inventory rows for this warehouse.
movements → list of Movement audit records for this warehouse.

Material

A catalogue entry for a physical material or supply item. Codes must be globally unique. Both soles and dollar prices default to 0.0 and are used by budget cost calculations.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`code`	`String`	No	Unique catalogue code.
`name`	`String`	No	Display name.
`unit`	`String`	Yes	Unit of measure (e.g. `"kg"`, `"m"`, `"pza"`).
`description`	`String`	Yes	Optional free-text description.
`unit_price`	`Float`	Yes	Unit price in Peruvian soles (defaults to `0.0`).
`unit_price_dolares`	`Float`	Yes	Unit price in US dollars (defaults to `0.0`).

Relationships

inventory → list of Inventory rows where this material appears.
movements → list of Movement records that reference this material.

Notesunit_price and unit_price_dolares feed budget cost projections. Keep both fields up to date so cost reports remain accurate in either currency.

Inventory

Tracks the quantity of a specific material in a specific warehouse. Each row is scoped to a single (warehouse_id, material_id) pair.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`warehouse_id`	`Integer`	No	FK → `warehouses.id`.
`material_id`	`Integer`	No	FK → `materials.id`.
`stock`	`Integer`	No	Total units on hand (defaults to `0`).
`reserved`	`Integer`	No	Units held for pending requirements (defaults to `0`).
`budget_id`	`Integer`	Yes	FK → `budgets.id`. Optional link to the project this stock belongs to.
`budget_name`	`String`	Yes	Denormalised budget name for display without a join.
`is_active`	`Boolean`	No	`False` when the linked project was deleted; stock is frozen until redirected (defaults to `True`).
`last_updated`	`DateTime`	Yes	UTC timestamp of the last stock change.

Relationships

warehouse → parent Warehouse.
material → parent Material.
budget → optional parent Budget.

Stock invariantavailable = stock − reserved. Before reserving, inventory_service verifies stock − reserved >= requested_qty. reserve_stock() mutates reserved but does not commit — the calling service commits after all items are processed.

Requirement

A work-site request for materials from the principal warehouse. Created at an "obra" warehouse; the service layer resolves the correct principal warehouse via owner_id.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`warehouse_id_obra`	`Integer`	No	FK → `warehouses.id` (must be an `"obra"` warehouse).
`status`	`String`	No	`"pending"`, `"partial"`, `"fulfilled"`, or `"cancelled"` (defaults to `"pending"`).
`budget_id`	`Integer`	Yes	FK → `budgets.id`. Project this requirement is charged to.
`budget_name`	`String`	Yes	Denormalised budget name for display without a join.
`created_at`	`DateTime`	Yes	UTC creation timestamp.
`notes`	`String`	Yes	Free-text operator notes.

Relationships

items → list of RequirementItem lines.
dispatches → list of Dispatch records fulfilling this requirement.

RequirementItem

A single line in a Requirement, specifying how many units of one material are requested and how many have been fulfilled so far.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`requirement_id`	`Integer`	No	FK → `requirements.id`.
`material_id`	`Integer`	No	FK → `materials.id`.
`requested_qty`	`Integer`	Yes	Number of units originally requested.
`fulfilled_qty`	`Integer`	No	Units dispatched so far (defaults to `0`).
`status`	`String`	No	`"pending"`, `"reserved"`, `"partial"`, or `"fulfilled"` (defaults to `"pending"`).

Relationships

requirement → parent Requirement.
material → the requested Material.

Dispatch

Records a physical shipment sent from the principal warehouse to an obra site. Created from a Requirement that is fully or partially reserved.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`requirement_id`	`Integer`	No	FK → `requirements.id`.
`dispatch_date`	`DateTime`	Yes	UTC timestamp of dispatch (defaults to `utcnow`).
`guia_number`	`String`	Yes	Formatted guide number: `GR-YYYYMMDD-{id:05d}`. Generated after insert when the `id` is known.
`user_id`	`Integer`	Yes	ID of the user who created the dispatch (no FK constraint).

Relationships

items → list of DispatchItem lines.
requirement → parent Requirement.
receipt → single Receipt confirming arrival (one-to-one, optional).

NotesA PDF “Guía de Remisión” is generated by utils/pdf_generator.py and saved to storage/guides/<guia_number>.pdf at dispatch time.

DispatchItem

A single line in a Dispatch, recording how many units of one material were physically sent.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`dispatch_id`	`Integer`	No	FK → `dispatches.id`.
`material_id`	`Integer`	No	FK → `materials.id`.
`dispatched_qty`	`Integer`	Yes	Number of units included in this shipment.

Relationships

dispatch → parent Dispatch (via backref).
material → the dispatched Material.

Receipt

Confirms that a dispatched shipment has physically arrived at the obra site. Creating a receipt triggers an Inventory increment and a Movement(type="IN") at the destination warehouse.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`dispatch_id`	`Integer`	No	FK → `dispatches.id` (one-to-one).
`receipt_date`	`DateTime`	Yes	UTC timestamp of confirmation (defaults to `utcnow`).
`user_id`	`Integer`	Yes	ID of the user who confirmed receipt (no FK constraint).

Relationships

dispatch → parent Dispatch.
items → list of ReceiptItem lines (via backref).

ReceiptItem sub-modelEach receipt line records the actually received quantity and whether it has been confirmed:

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`receipt_id`	`Integer`	No	FK → `receipts.id`.
`material_id`	`Integer`	No	FK → `materials.id`.
`received_qty`	`Integer`	Yes	Units confirmed received.
`confirmed`	`Boolean`	No	`True` once the line has been verified (defaults to `False`).

Movement

An immutable audit record written every time stock changes anywhere in the system. Never modified after creation.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`warehouse_id`	`Integer`	No	FK → `warehouses.id`. Warehouse where the change occurred.
`material_id`	`Integer`	No	FK → `materials.id`. Material affected.
`qty_change`	`Integer`	Yes	Signed quantity delta (positive = stock in, negative = stock out).
`movement_type`	`String`	Yes	`"IN"` (stock added) or `"OUT"` (stock removed).
`reference_type`	`String`	Yes	Entity that triggered the movement (e.g. `"dispatch"`, `"receipt"`).
`reference_id`	`Integer`	Yes	Primary key of the triggering entity.
`user_id`	`Integer`	Yes	ID of the user who triggered the change (no FK constraint).
`timestamp`	`DateTime`	Yes	UTC timestamp of the change (defaults to `utcnow`).

Relationships

warehouse → the affected Warehouse.
material → the affected Material.

NotesMovement rows form the complete stock audit trail. Do not delete or update them. All timestamps are stored in UTC; the UI converts to Lima/Peru time (UTC-5, no DST) for display.

Budget

Represents a project or obra that materials and costs are tracked against. Inventory rows and requirements can be linked to a budget for cost attribution.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`name`	`String`	No	Project name.
`budget_soles`	`Float`	No	Approved budget in Peruvian soles (defaults to `0.0`).
`budget_dolares`	`Float`	No	Approved budget in US dollars (defaults to `0.0`).
`notes`	`String`	Yes	Optional free-text notes.
`is_active`	`Boolean`	No	`False` freezes associated inventory rows until redirected (defaults to `True`).
`is_finished`	`Boolean`	No	Marks the project as complete (defaults to `False`).
`finished_at`	`DateTime`	Yes	UTC timestamp of project completion. `NULL` while active.
`created_at`	`DateTime`	Yes	UTC creation timestamp.

NotesWhen a budget is deactivated (is_active = False), linked Inventory rows are also set to is_active = False, freezing that stock until it is manually redirected to another budget or warehouse.

User

An application user. Two roles exist: "superadmin" (single, non-deletable system account) and "cliente" (regular operator). All data queries are scoped by owner_id matching the authenticated user’s id.

Column	Type	Nullable	Description
`id`	`Integer`	No	Primary key.
`username`	`String`	No	Unique login handle (indexed).
`email`	`String`	No	Unique email address (indexed).
`password_hash`	`String`	No	bcrypt hash (12 rounds). Never store or log plaintext passwords.
`role`	`String`	No	`"superadmin"` or `"cliente"` (defaults to `"cliente"`).
`is_active`	`Boolean`	No	`False` locks the account (defaults to `True`).
`created_at`	`DateTime`	Yes	UTC creation timestamp.

NotesAccounts are locked automatically after 5 consecutive failed login attempts and unlocked after 15 minutes. Sessions expire after 30 minutes of inactivity (server-side check). The password_hash column stores the bcrypt digest — never the raw password.

Architecture

Services

Data Models

SQLAlchemy data models and schema reference

Build docs developers (and LLMs) love

Architecture

Services

Data Models

Documentation Index

Build docs developers (and LLMs) love