Schema Database
Struttura completa del database PostgreSQL di Terminia con tutti i tipi, le relazioni e gli enum.
Tabelle Principali
companies
Tabella centrale per i dati aziendali dell'utente.
| Colonna | Tipo | Note |
|---|---|---|
| id | uuid | PK |
| name | text | Ragione sociale |
| vat_number | text | P.IVA |
| ateco_code | text | Codice ATECO |
| size | company_size | micro | small | medium | large |
| certifications | text[] | ISO, SOA, ecc. |
| annual_revenue | numeric | Fatturato annuo |
contracts
Tutti i contratti (commerciali e HR) dell'azienda.
| Colonna | Tipo | Note |
|---|---|---|
| id | uuid | PK |
| contract_type | contract_type | service_supply | nda | permanent | ... |
| counterpart_id | uuid | FK counterparts (commerciali) |
| employee_id | uuid | FK employees (HR) |
| status | contract_status | draft | active | expiring | ... |
| value | numeric | Valore contratto |
| auto_renewal | boolean | Rinnovo tacito |
| risk_score | integer | 0-100, calcolato da AI |
| embedding | vector(1536) | Per similarity search |
counterparts
Clienti, fornitori e partner commerciali.
| Colonna | Tipo | Note |
|---|---|---|
| type | counterpart_type | supplier | client | partner |
| reliability_score | integer | 0-100, da Advisor OSINT |
| reliability_label | reliability_label | excellent | good | warning | risk |
| verification_json | jsonb | Risultati OSINT |
| total_exposure | numeric | Esposizione totale |
bandi
Bandi pubblici trovati da BandoRadar.
| Colonna | Tipo | Note |
|---|---|---|
| source | bando_source | anac | ted_europa | ... |
| match_score | integer | 0-100 |
| match_breakdown | jsonb | Score per dimensione |
| gap_analysis_json | jsonb | Requisiti satisfied/missing |
| participation_status | participation_status | new | participating | won | ... |
| bando_embedding | vector(1536) | Per similarity search |
Enum Types
contract_status
draft | negotiating | active | expiring | renewed | terminated | suspendedcontract_type
Commerciali:
service_supply | goods_supply | framework | nda | agency | partnershipHR:
permanent | fixed_term | part_time | cococo | vat_number | internship | apprenticeshipalert_type
auto_renewal | contract_expiry | obligation_due | payment_expected | fixed_term_expiry | new_bando_match | reliability_score_drop | ...risk_level
low | medium | high | criticalRelazioni Chiave
contracts.counterpart_id→counterparts.idcontracts.employee_id→employees.idcontracts.parent_contract_id→contracts.id(self-reference)alerts.contract_id→contracts.idalerts.bando_id→bandi.idinvoices.contract_id→contracts.idclauses.contract_id→contracts.id
Indici e Performance
- Indici B-tree su tutte le FK e colonne di lookup frequente
- Indice GIN su
contracts.tagsecounterparts.tags - Indice ivfflat su colonne vector per similarity search
- Partitioning su
alertspertrigger_date