# Database

SIDES uses **PostgreSQL 16** running in a Docker container. Laravel connects
via `pgsql` driver using the Docker service name as the host.

## Connection Details

| Setting         | Value                        | Source                    |
|-----------------|------------------------------|---------------------------|
| DB_CONNECTION   | `pgsql`                      | `src/.env`                |
| DB_HOST         | `postgres` (Docker service)  | `src/.env`                |
| DB_PORT         | `5432`                       | `src/.env`                |
| DB_DATABASE     | `${POSTGRES_DB}`             | `src/.env`                |
| DB_USERNAME     | `${POSTGRES_USER}`           | `src/.env`                |
| DB_PASSWORD     | `${POSTGRES_PASSWORD}`       | `src/.env`                |

Default database name: **sides_db** (set by `POSTGRES_DB` in the root `.env`).

The container (`tckdev-db`) uses a named Docker volume **pgdata** for
persistent storage. Data survives container restarts and rebuilds.

## Entity-Relationship Diagram

```
 station (PK: stationid)
 |--------------|
 | stationid    |---+----------------+----------------+
 | name         |   |                |                |
 | district     |   |                |                |
 | lng, lat     |   |                |                |
 | mainriverbasin|  |                |                |
 | subriverbasin|   |                |                |
 | rainfall     |   |                |                |
 | waterlevel   |   |                |                |
 | siren        |   |                |                |
 | cctv_link    |   |                |                |
 |--------------|   |                |                |
                    v                v                v
          rainfall             waterlevel          siren
          (PK: id)             (PK: id)            (PK: id)
          |--------------|     |--------------|     |--------------|
          | id           |     | id           |     | id           |
          | stationid    |     | stationid    |     | stationid    |
          | timestamp    |     | datetime     |     | stationtype  |
          | anncum       |     | waterlevel   |     | active_time  |
          | daily        |     | alert        |     | level        |
          | hourly       |     | warning      |     |--------------|
          | currentrf    |     | danger       |
          | battery      |     |--------------|
          |--------------|
                    |                |
                    v                v
                 notification (PK: id)
                 |--------------|
                 | id           |
                 | stationid    |
                 | timestamp    |
                 | stationtype  |
                 | level        |
                 | active_time  |
                 |--------------|
```

```mermaid
erDiagram
    station {
        int stationid
        string name
        string district
        float lng
        float lat
        string mainriverbasin
        string subriverbasin
        float rainfall
        float waterlevel
        boolean siren
        string cctv_link
    }

    rainfall {
        int id
        int stationid
        datetime timestamp
        float anncum
        float daily
        float hourly
        float currentrf
        float battery
    }

    waterlevel {
        int id
        int stationid
        datetime datetime
        float waterlevel
        string alert
        string warning
        string danger
    }

    siren {
        int id
        int stationid
        string stationtype
        datetime active_time
        string level
    }

    notification {
        int id
        int stationid
        datetime timestamp
        string stationtype
        string level
        datetime active_time
    }

    station ||--o{ rainfall : "has"
    station ||--o{ waterlevel : "has"
    station ||--o{ siren : "has"
    station ||--o{ notification : "has"
```


**Note:** There are no database-level foreign keys between `station` and the
data tables. Relationships are enforced at the application level by matching
`stationid` values.

## Application Tables

### station

Primary reference table for all monitoring stations.

| Column          | Type          | Nullable | Notes                |
|-----------------|---------------|----------|----------------------|
| stationid       | string (PK)   | no       | External station ID  |
| name            | string        | no       | Station display name |
| district        | string        | no       |                      |
| lng             | float         | no       | Longitude            |
| lat             | float         | no       | Latitude             |
| mainriverbasin  | string        | no       |                      |
| subriverbasin   | string        | no       |                      |
| rainfall        | integer       | no       | Flag/counter         |
| waterlevel      | integer       | no       | Flag/counter         |
| siren           | integer       | yes      | Added by migration   |
| cctv_link       | string        | yes      | Added by migration   |

Migration: `2025_11_06_071853_create_station_table.php`
Alter migrations: `2025_11_08_004548`, `2025_11_25_113158`

### rainfall

Rainfall sensor readings per station.

| Column     | Type        | Nullable |
|------------|-------------|----------|
| id         | bigint (PK) | no       |
| stationid  | string      | no       |
| timestamp  | timestamp   | no       |
| anncum     | double      | no       |
| daily      | double      | no       |
| hourly     | double      | no       |
| currentrf  | double      | no       |
| battery    | double      | no       |

Migration: `2025_11_06_072709_create_rainfall__table.php`

### waterlevel

Water level sensor readings per station.

| Column      | Type        | Nullable |
|-------------|-------------|----------|
| id          | bigint (PK) | no       |
| stationid   | string      | no       |
| datetime    | timestamp   | no       |
| waterlevel  | double      | no       |
| alert       | double      | no       |
| warning     | double      | no       |
| danger      | double      | no       |

Migration: `2025_11_06_072738_create_waterlevel_table.php`

### siren

Siren activation records.

| Column       | Type        | Nullable |
|--------------|-------------|----------|
| id           | bigint (PK) | no       |
| stationid    | string      | no       |
| stationtype  | string      | no       |
| active_time  | timestamp   | no       |
| level        | string      | no       |

Migration: `2025_11_07_031601_create_siren__table.php`

The `stationtype` column is always `3` for siren inserts (see stationtype
values below).

### notification

Notification log for alerts across all station types.

| Column       | Type        | Nullable |
|--------------|-------------|----------|
| id           | bigint (PK) | no       |
| stationid    | string      | no       |
| timestamp    | timestamp   | no       |
| stationtype  | integer     | no       |
| level        | string      | no       |
| active_time  | timestamp   | yes      |

Migration: `2025_11_07_024940_create_notification_table.php`

### users

Application users with access control and blocking support.

| Column             | Type        | Nullable | Default | Notes             |
|--------------------|-------------|----------|---------|-------------------|
| id                 | bigint (PK) | no       | auto    |                   |
| name               | string      | no       |         |                   |
| email              | string      | no       |         | unique            |
| email_verified_at  | timestamp   | yes      |         |                   |
| password           | string      | no       |         | bcrypt hashed      |
| access_level       | integer     | no       | 2       | 1=admin, 2=user   |
| is_blocked         | boolean     | no       | false   |                   |
| login_attempts     | integer     | no       | 0       |                   |
| remember_token     | string      | yes      |         |                   |
| created_at         | timestamp   | yes      |         |                   |
| updated_at         | timestamp   | yes      |         |                   |

Base: `0001_01_01_000000_create_users_table.php`
Alter: `2025_11_07_063825`, `2025_11_07_065418`

## stationtype Values

The `stationtype` field is used in the `notification` and `siren` tables to
identify the source station type.

| Value | Type        |
|-------|-------------|
| 1     | Rainfall    |
| 2     | Water level |
| 3     | Siren       |

## Laravel Standard Tables

These tables are created by Laravel's built-in migrations and support
framework features (sessions, caching, queue jobs).

| Table                 | Purpose                       |
|-----------------------|-------------------------------|
| password_reset_tokens | Password reset tokens         |
| sessions              | Database-backed sessions      |
| cache                 | Application cache store       |
| cache_locks           | Atomic cache locks            |
| jobs                  | Queue job queue               |
| job_batches           | Batched job tracking          |
| failed_jobs           | Failed job records            |
| migrations            | Migration tracking            |

## Default Admin User

The `DatabaseSeeder` creates a default admin user on first run using
`firstOrCreate`, making it idempotent -- safe to re-run without duplicates.

```php
User::firstOrCreate(
    ['email' => env('ADMIN_EMAIL', 'admin@example.com')],
    [
        'name' => 'Admin',
        'password' => Hash::make(env('ADMIN_PASSWORD', str()->random(32))),
        'access_level' => 1,
    ]
);
```

- **Email**: set by `ADMIN_EMAIL` env var (default: `admin@example.com`)
- **Password**: set by `ADMIN_PASSWORD` env var (default: random 32-char string)
- **access_level**: `1` (admin)

The seeder is triggered automatically when `RUN_SEEDER=true` in the Docker
environment (see `docker/entrypoint.sh`).

The migration `2025_12_11_124201_add_default_user_to_users_table.php` exists
but is a no-op (empty `up()` and `down()`) -- admin creation is handled
exclusively by the seeder.

## Seeding and Migrations

Migrations and seeding run automatically at container startup via
`docker/entrypoint.sh`:

```bash
# Runs when RUN_MIGRATIONS=true
php artisan migrate --force

# Runs when RUN_SEEDER=true
php artisan db:seed --force
```

Both `RUN_MIGRATIONS` and `RUN_SEEDER` default to `true` in
`docker-compose.yml`.

## Design Notes

**No foreign keys.** The `stationid` column in `rainfall`, `waterlevel`,
`siren`, and `notification` references `station.stationid` at the application
level only. There are no database-level `REFERENCES` constraints. This means
orphaned data rows can exist if a station is deleted without cleaning
associated records.

**No additional indexes.** Beyond primary keys and Laravel's default indexes
(on `sessions.user_id`, `sessions.last_activity`, `jobs.queue`), no custom
indexes exist on the data tables. This is a potential performance concern as
the `rainfall`, `waterlevel`, and `notification` tables grow -- queries
filtering by `stationid` or ordering by timestamp will perform full table
scans.