Database Model

Multi-Tenant Architecture

graph TD
    subgraph PostgreSQL 11["PostgreSQL 11 — localhost:5432"]
        PUB["DB: public\n(tenant registry)"]
        M["DB: maxx\n~418 MB — primary company"]
        MS["DB: maxx_suport\n~87 MB — support/demo tenant"]
        DOTDOT["... more DBs\n(one per registered company)"]
    end

    PUB -->|unitati.bazadate = 'maxx'| M
    PUB -->|unitati.bazadate = 'maxx_suport'| MS

    APP["AncoraERP\nBasicFrontController"] -->|"1. lookup tenant DB by CUI (fiscal code)"| PUB
    APP -->|"2. all business queries"| M
    APP -->|"2. all business queries"| MS

---

public Database — Tenant Registry

The public database is the only one the login page connects to. It resolves which tenant DB to use.

erDiagram
    unitati {
        int     id          PK
        string  denumire    "Company display name"
        string  cui         "Romanian fiscal code (CUI) — used as login key"
        string  bazadate    "PostgreSQL database name for this tenant"
        int     codjudet    "Romanian county code (e.g. 9 = Ilfov)"
        bool    inactiv     "Soft-delete flag — if true, login blocked"
        bool    isbackup    "If true, exclude from daily backup rotation"
        string  email       "Contact email"
        string  telefon     "Contact phone"
    }

    utilizatori_global {
        int     id          PK
        int     id_unitate  FK
        string  utilizator  "Username"
        string  parola      "Password (XOR-encrypted or plaintext)"
        int     tip         "User type / access level"
    }

---

Tenant Database Structure (e.g., maxx)

Each tenant DB has its own isolated schema. Tables are not in a public schema sub-namespace — all top-level. Key table categories:

Configuration & Master Data

-- Global configs per tenant
parametri_sistem        -- system settings (company name, address, logo, etc.)
parametri_utilizatori   -- per-user preferences (default warehouse, dates, etc.)

-- Currency
cursvalutarbnr          -- BNR-fetched rates by date+currency code
cursvalutar             -- manually overridden rates

-- Nomenclatures
judete                  -- Romanian counties lookup table
localitati              -- cities/towns
tari                    -- countries (ISO codes)

Products & Inventory (produse / stocuri)

produse            -- product catalogue (cod, denumire, UM, TVA group, pret_lista)
grupe_produse      -- product categories tree
produse_terti      -- supplier cross-reference codes per product
stocuri            -- current stock per product × gestiune (warehouse)
miscari_stoc       -- all stock movements (audit log)
gestiuni           -- warehouse / depot definitions

Third Parties (terti)

terti              -- unified clients & suppliers (CUI, IBAN, address, tip: C/F/CF)
banci_terti        -- bank accounts per third party
contracte          -- framework contracts with clients/suppliers

Sales Cycle

comenzi_clienti        -- customer orders header
pozitii_comenzi_clienti -- order lines
facturi_emise (cfi_*)  -- issued invoices header + lines
avize_livrare          -- delivery notes
garantii               -- warranty records
proforma_vanzari       -- proforma invoices

Purchasing Cycle

comenzi_furnizori         -- purchase orders header + lines
facturi_intrare (ffi_*)   -- received supplier invoices header + lines
receptii                  -- goods receipt notes
devize                    -- cost estimates / quotations

Accounting

plan_conturi       -- chart of accounts (Romanian PCG)
note_contabile     -- manual journal entries
balanta_sintetica  -- computed balance (materialized or view)
bilant             -- balance sheet data
calcul_balanta     -- period trial balance computation
carte_mare         -- general ledger (full audit trail)

Payroll

personal           -- employee records (CI, contract, grade, bank)
state_plata        -- monthly payroll runs
retineri           -- deductions per employee
pontaj             -- time attendance records
tichete_de_masa    -- meal vouchers tracking

Treasury

registru_casa      -- petty cash register (lei / FX)
extras_cont        -- bank statement imports + reconciliation
ordine_plata       -- payment orders
banci              -- bank account definitions

---

Schema Evolution Pattern

AncoraERP uses a custom numbered SQL migration system:

/opt/tomcat/webapps/ancoraerp/
└── actualizaredb/
    ├── 0001.sql
    ├── 0002.sql
    ├── ...
    └── 1493.sql      ← 1,493 migration scripts as of mid-2025

flowchart TD
    START["auto_actdb.serv called\n(by daily_job.sh or admin)"]
    READ["SELECT max(nr_actualizare)\nFROM setari_actualizare\n→ e.g. 1480"]
    SCAN["Scan actualizaredb/\nfor files 1481.sql … 1493.sql"]
    EXEC["Execute each SQL file\nin numeric order"]
    LOG["INSERT INTO setari_actualizare\n(nr_actualizare, data)\nfor each applied script"]
    DONE["return HTML report"]

    START --> READ --> SCAN --> EXEC --> LOG --> DONE

• Each .sql file is idempotent (CREATE TABLE IF NOT EXISTS, ALTER … ADD COLUMN IF NOT EXISTS, conditional DO $$ ... IF $$)
• Scripts run in-process via BasicServicesController → no external migration runner
• Applied to every tenant DB independently (called once per DB with ?db_email=maxx)

---

Backup Strategy

gantt
    title 7-Day Rolling Backup Rotation
    dateFormat  D
    axisFormat  Day %d

    section Backup Slot 1 (Monday)
    maxx.dump        :1, 1d
    maxx_suport.dump :1, 1d

    section Backup Slot 2 (Tuesday)
    maxx.dump        :2, 1d

    section Backup Slot 3 (Wednesday)
    maxx.dump        :3, 1d

    section ...
    Week repeats     :4, 4d

# Backup path: /home/ancora/backup/$(date +%u)/  (1=Mon … 7=Sun)
# Format: pg_dump -Fc  (PostgreSQL custom compressed format)
# Restore: pg_restore -d targetdb /home/ancora/backup/3/maxx.dump
# Retention: 7 days rolling (overwritten each week)
# Sizes: maxx ≈ 418 MB compressed, maxx_suport ≈ 87 MB compressed

---

Key PostgreSQL Functions (from lib JARs)

The dark-jason-api JAR registers custom SQL functions used across all query XML configs:

dm2_get_meniu_level2(module_id, user_id)  -- returns accessible menu items
dm2_valuta_to_lei(amount, currency, date) -- apply BNR rate conversion
dm2_stoc_current(produs_id, gestiune_id)  -- current stock quantity
dm2_pret_vanzare(produs_id, client_id)    -- price with discount ladder
dm2_sold_client(tert_id, data)            -- AR balance at date
dm2_sold_furnizor(tert_id, data)          -- AP balance at date
dm2_calcul_tva(amount, tva_group)         -- VAT computation
dm2_generate_numar_document(tip, an)      -- sequential document numbering