# database

Script SQL della piattaforma: schemi, funzioni, viste, migrations.

Componente della piattaforma open source **MyTown** (gruppo `mytown-agpl-v3`; documentazione, installazione e architettura nel repository `platform`).

Il progetto MyTown e' realizzato da **Kondor CS Srl** con il supporto di **CNA Teramo** ed e' cofinanziato dall'**Unione Europea** - PR FESR Abruzzo 2021-2027, Intervento 1.1.1.1 "Sostegno a progetti di Ricerca e Innovazione" (Det. DPH006/182 del 23/07/2024). Sito del progetto: <https://www.mytown.tech>.

**Licenza**: GNU AGPL v3 (vedi `LICENSE`). Copyright (C) Kondor Consulting & Services S.r.l. Licenze commerciali e note sui marchi: `LICENSING.md` e `TRADEMARKS.md` nel repository `platform`.

---

# Database Directory

This directory contains database-related files for reference and development.

## Structure

```
database/
|-- install/        # Installazione from-scratch: DDL completi + seed + bootstrap (vedi install/README.md)
|-- schemas/        # Table definitions exported from PostgreSQL
|-- migrations/     # Migration scripts
|-- queries/        # Frequently used queries for reference
|-- views/          # View definitions
|-- functions/      # Stored functions and procedures
```

> **Prima installazione**: usare `install/` (procedura completa e validata,
> vedi [install/README.md](install/README.md)). Le cartelle `schemas/` e
> `migrations/` documentano l'evoluzione incrementale.

## Connection Details

- **Host**: <DB_HOST>
- **Port**: 5432
- **Database**: CompanyDB
- **Read-Only User**: claude_readonly

## Access Policy

**IMPORTANT**: Claude has READ-ONLY access to the database.

### Creating the Read-Only User

Run `setup-readonly-user.sql` as the postgres superuser or database owner:

```powershell
# Using psql from Windows
psql -h <DB_HOST> -U postgres -d CompanyDB -f setup-readonly-user.sql
```

### Exporting Schema for Reference

Export your current schema so Claude can reference it without connecting:

```powershell
# Export all table definitions
pg_dump -h <DB_HOST> -U kondor -d CompanyDB --schema-only --no-owner --no-acl > schemas/companydb-schema.sql

# Export specific schema
pg_dump -h <DB_HOST> -U kondor -d CompanyDB --schema-only --schema=public > schemas/public-schema.sql

# Export table list
psql -h <DB_HOST> -U kondor -d CompanyDB -c "\dt" > schemas/table-list.txt

# Export column details for all tables
psql -h <DB_HOST> -U kondor -d CompanyDB -c "SELECT table_name, column_name, data_type, is_nullable FROM information_schema.columns WHERE table_schema = 'public' ORDER BY table_name, ordinal_position;" > schemas/columns.txt
```

## Naming Conventions

Based on existing Kondor CS patterns:

- Tables: snake_case (e.g., `user_accounts`, `documenti_pratiche`)
- Columns: snake_case (e.g., `created_at`, `id_utente`)
- Primary Keys: `id` or `id_[table]`
- Foreign Keys: `id_[referenced_table]` or `[referenced_table]_id`
- Indexes: `idx_[table]_[column]`
- Constraints: `fk_[table]_[referenced]`, `uq_[table]_[column]`

## Common Tables Reference

| Table | Description | Service |
|-------|-------------|---------|
| utenti | User accounts | portal_login |
| ruoli | User roles | portal_login |
| dipendenti | Employees | hr |
| automezzi | Vehicles | automezzi |
| documenti | Documents | documentary |
| pratiche | Cases/Files | workflow |
| workflow_stati | Workflow states | workflow |

## Useful Queries

Save frequently used queries in the `queries/` folder for quick reference.

### Example: List all tables with row counts

```sql
SELECT 
    schemaname,
    relname as table_name,
    n_live_tup as row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
```

### Example: Find tables by column name

```sql
SELECT 
    table_name, 
    column_name, 
    data_type
FROM information_schema.columns
WHERE column_name LIKE '%utente%'
AND table_schema = 'public';
```
