# Database Structure Management

## What It Does

The server automatically keeps the database structure in sync with the application configuration. When the server starts, it compares what the database looks like against what the application expects. If there are differences, it generates and applies the necessary changes.

This means you never need to manually run SQL scripts to create tables, add columns, or change field types. The server handles it.

## How It Works

### Startup Check

Every time the server starts, it:

1. Reads all table definitions from configuration files
2. Compares them against the actual database
3. Generates the SQL needed to bring the database up to date
4. Applies the changes

### Version Tracking

Each organization's database has a version number. The server tracks what version the database is at and what version the configuration expects. Changes are only applied when versions differ.

### What Gets Detected

| Situation | What Happens |
|---|---|
| New table in configuration | Table is created with all columns, indexes, and constraints |
| New column added to configuration | Column is added to existing table |
| Column type changed | Column type is altered |
| Column removed from configuration | Column is dropped from table |
| Table renamed in configuration | Table is renamed |
| Column renamed in configuration | Column is renamed |
| Default value changed | Default value is updated |
| Missing primary key or unique constraint | Constraint is added |

## Data Safety

### Automatic Data Loss Detection

Before making any changes, the server analyzes whether the change could cause data loss:

| Change | Risk |
|---|---|
| Column dropped | Data in that column is permanently lost |
| Table recreated | All data in the table is lost |
| Text field shortened | Existing data longer than the new limit is truncated |
| Field type changed | Data may not convert correctly (e.g. text to number) |
| NOT NULL added to nullable column | Rows with null values will fail |

The following changes are always safe:

| Change | Why It Is Safe |
|---|---|
| New column added | No existing data affected |
| Text field made longer | Existing data still fits |
| Column made nullable | No data loss |
| Column renamed | Same data, new name |
| Index added | Only improves performance |

### Backup Before Changes

When data loss is detected, the server can automatically create backups before applying changes. This is configured with the `database_backup` setting in system options.

| Setting | What It Does |
|---|---|
| `table` | Copies only the affected tables to a backup schema |
| `database` | Creates a complete copy of the entire database |
| `none` | No backup is made, only warnings are shown |

**Recommendation:** Use `"table"` for production. It is fast and only copies what might be affected.

Backup tables are stored in the `backup` schema. They are not deleted automatically — you can inspect them or restore from them manually.

## Default Columns

Every table automatically gets these columns:

| Column | Purpose |
|---|---|
| `db_id` | Unique row identifier, auto-generated |
| `db_modify_id` | Change tracking number, increments on every update |
| `record_id` | UUID identifier for the record |
| `modify_id` | UUID that changes when the record is modified |
| `create_time` | When the record was created |
| `modify_time` | When the record was last modified |
| `modify_user` | Who last modified the record |
| `record_type` | Type discriminator for tables with multiple record types |
| `json_data` | Flexible JSON storage for additional data |

## Audit Log

Changes to data can be tracked automatically. When audit logging is enabled for a table, every insert, update, and delete is recorded with timestamps. This provides a complete history of all data changes.

## External Database Connections

The application can connect to external databases (4D, Odoo, etc.) and synchronize data. Field mappings define how external columns correspond to local columns.

A single external field can map to multiple local fields. For example, an external system's `id` field might correspond to both `product_id` and `record_id` in the local database.

## Supported Databases

| Database | Schema Management | Notes |
|---|---|---|
| PostgreSQL | Full support | Primary database, all features available |
| SQLite | Partial | Used for local/development, some limitations on ALTER operations |
