Database schema

ERP DLM uses PostgreSQL 16 with SQLAlchemy 2.0 async. All business entities inherit from BaseModel, defined in backend/app/core/models.py.

General conventions

Convention	Detail
Primary key	UUID v4 (uuid.uuid4) generated by the application
Timestamps	created_at and updated_at TIMESTAMPTZ, automatic
Soft delete	Not consistent across the whole project: verify each table
Column names	English snake_case
Multi-company	company_id FK to companies.id when applicable

Core mixins

class BaseModel(Base, TimeStampedMixin, UUIDMixin):
    """Base model with UUID PK + timestamps."""

• UUIDMixin → id: UUID PK.
• TimeStampedMixin → created_at, updated_at.
• SoftDeleteMixin → deleted_at (NULL = active).
• AuditableMixin → created_by, updated_by.

Soft delete warning

Not all tables use the same soft-delete mechanism. Before filtering by is_deleted, check the actual table schema (\d <table> in psql) or the SQLAlchemy model.

Mechanism	Example tables
is_deleted BOOLEAN DEFAULT false	sales_invoices
is_active BOOLEAN DEFAULT true	clients, products, suppliers, agents, accounts, subaccounts
deleted_at TIMESTAMPTZ	Several tables using SoftDeleteMixin

Real name mapping

The following tables and columns have been verified against the code and real schema:

Table	Main columns	Notes
companies	name, tax_id, email, phone, address, city, state, zip_code, country, is_active	Multi-tenant company
clients	name, tax_id, email, phone, address, city, postal_code, province, country_code, client_type, is_active, registration_date, discount_percent, credit_limit, agent_id	NO first_name/last_name
suppliers	name, tax_id, email, phone, address, is_active	Similar to clients
agents	first_name, last_name, code, is_active, commission_percent	DOES have first_name/last_name
products	reference, description, sale_price, cost_price, is_active, family_id, subfamily_id, brand_id	NO is_deleted
accounts	code, description, account_type, is_active	Chart of accounts
subaccounts	code, description, account_code, is_active	Subaccounts
entry_lines	entry_id, subaccount_code, debit, credit, description, is_active	Numeric(20,2)
sales_invoices	date, document_number, total_amount, grand_total, net_total, tax_total, surcharge_total, irpf_percent, irpf_amount, status, is_deleted	Only document table with is_deleted
bank_accounts	code, description, iban, subaccount_code, initial_balance, is_active	Treasury
customer_receipts	invoice_id, receipt_number, amount, paid_amount, issue_date, due_date, status	Collections
families	code, name	Product families
fiscal_years	code, name, start_date, end_date, is_active	Fiscal years

Multi-company relationships

• Each tenant row in saas_manager (separate database) defines db_name, db_user, db_password and status.
• Within the tenant database many business tables include company_id to separate companies.
• If company_id is NULL, the record is considered global or shared (depends on the module).

Common types

Data type	Use
UUID	Primary and foreign keys
TIMESTAMPTZ	Date/time with timezone
DATE	Dates without time (due dates, fiscal years)
NUMERIC(20,2)	Monetary amounts
TEXT	Long notes
JSONB	Flexible / metadata fields
BOOLEAN	Active / deleted flags

Best practices when adding tables

1. Inherit from BaseModel for automatic UUID and timestamps.
2. Decide the soft-delete mechanism and keep it consistent within the module.
3. Add indexes on frequently searched columns (name, tax_id, code).
4. Use company_id when the record belongs to a specific company.
5. Create the corresponding Alembic migration.