# Database Configuration

TAF can run against multiple database engines. Connection details are supplied through environment variables and `config/storage.php`.

## Environment Variables

- `DB_DRIVER` – main database driver. Accepted values are `pgsql`, `mariadb`, `oci` and `mariadb_mongo`. Defaults to `pgsql`.
- `DB_HOST` – database server hostname (defaults to `localhost`).
- `DB_PORT` – port number (3306 for MariaDB, 5432 for PostgreSQL, 1521 for Oracle).
- `DB_NAME` – database name, normally `TAFDB`.
- `DB_USER` – username for connecting to the database.
- `DB_PASS` – password for `DB_USER`.
- `MONGO_URI` – connection string for MongoDB when using `mariadb_mongo`.
- `MONGO_DATABASE` – MongoDB database name.
- `MONGO_COLLECTION` – collection used by the object store.

Defaults for the Mongo values are defined in `config/storage.php`.

## Switching Database Engines

Set `DB_DRIVER` before running the application or the helper scripts. Examples:

```bash
DB_DRIVER=pgsql   ./test_setup.sh    # PostgreSQL (default)
DB_DRIVER=mariadb ./test_setup.sh    # MariaDB only
DB_DRIVER=oci     ./test_setup.sh    # Oracle
DB_DRIVER=mariadb_mongo ./test_setup.sh  # MariaDB with Mongo object store
```

The application reads `DB_DRIVER` in `api/config.php` and configures the PDO driver accordingly. Use `mariadb_mongo` when you want relational data in MariaDB but binary objects stored in MongoDB. Ensure the Mongo connection details in `config/storage.php` match your environment.

## `ui_modals` Table

The UI framework can persist modal definitions in a dedicated table called
`ui_modals`. Each record stores the modal name and a JSON configuration used by
`ModalBuilder` on the front end. The schema includes optional `tenant_id` and
`branch_id` fields so tenants or branches can override default modal layouts.

On PostgreSQL the `config` column is defined as `jsonb` to take advantage of
binary JSON storage and indexing. MariaDB uses the standard `json` type. When
adding new modal definitions through migrations or the admin interface, ensure
the JSON payload is valid so that it can be stored as `jsonb` in Postgres-based
deployments.


## `dashboard_card_permissions` Table

This table governs which users or groups can see specific dashboard cards.
Each row links a card's slug to an optional user or group record and may be
restricted by tenant or branch.

Fields include:
- `id` – primary key.
- `card_slug` – identifier of the dashboard card.
- `group_id` – nullable group reference.
- `user_id` – nullable user reference.
- `tenant_id` – nullable tenant scope.
- `branch_id` – nullable branch scope.
- `can_view` – boolean flag defaulting to `TRUE`.
- `updatedAt` – timestamp of the last update.

If a combination of tenant, group or user lacks a record in this table,
the corresponding dashboard card is not shown by default.

## `ohs_incidents` Table

Incident reports are stored in the `ohs_incidents` table. A recent migration
moved numerous detail columns into a single `form_data` JSONB field while
adding `submitted_by` and human readable `status` columns. The core columns are:

- `id` – primary key
- `date` – date of the incident
- `time` – time of the incident
- `location` – free text location
- `classification` – incident type
- `investigator_id` – assigned investigator
- `status` – current workflow status (defaults to `reported`)
- `submitted_by` – user that reported the incident
- `form_data` – JSON object containing additional fields
- `tenant_id` – optional tenant scope
- `branch_id` – optional branch scope
- `updatedAt` – timestamp of the last update
- `isDeleted` – soft delete flag

Older columns like `nature`, `how_sustained` and GPS coordinates are now kept
inside `form_data`. Migrations populate the JSON object and drop the legacy
columns so new installations only include the compact structure above.