# Technical documentation — db_design (DB Diagram)

Evidence date: 2026-10-17.

Source: `git@bitbucket.org:overdrive/db_design.git`, `main` at `992fbe3` (2026-07-07, jacobrits1, "merge: integrate app into overdrive/db_design repo"). Only branch: `main`. `origin/HEAD` points at `main`.

The scan-host path `~/projects/sonar_scan/repos/db_design` was not on the machine that produced this document. Facts below come from that git revision.

## Architecture

One Apache document root. `index.html` is the application. JSON endpoints are PHP scripts under `api/`. Diagram parsing, layout, import, and export run in the browser. MySQL stores users, diagram text, layout JSON, and version snapshots.

```text
index.html
assets/js/app.js            UI state, save, dialogs
assets/js/api.js            fetch → api/*.php
assets/js/editor.js         CodeMirror
assets/js/dbml-parser.js    DBML model
assets/js/canvas.js         SVG
assets/js/sql-import.js     MySQL DDL → DBML text
assets/js/sql-export.js     model → MySQL DDL
        |  cookie DBDAIGRAM_SESS, JSON body
        v
api/auth.php                accounts and session
api/diagrams.php            diagrams and versions
api/db.php                  PDO, CREATE DATABASE, CREATE TABLE, seed
api/config.php              env, session, json_out / json_err
        v
MySQL database dbdaigram (default name)
```

There is no queue, cron entry, mailer, object store, or background worker in the tree.

## Repository layout

| Path | What it is |
|---|---|
| `README.md` | Product overview, default login, WAMP, LAN, backup, Dokploy |
| `index.html` | Login view and editor view |
| `api/config.php` | DB constants, session, JSON helpers, `require_login`, `require_admin` |
| `api/db.php` | Connection and schema bootstrap |
| `api/auth.php` | `action` switch for accounts |
| `api/diagrams.php` | `action` switch for diagrams |
| `assets/js/*.js` | Application client, one IIFE per file, attached to `window` |
| `assets/css/app.css` | Layout and dark theme |
| `assets/vendor/` | Minified CodeMirror, simple-mode, active-line, and CSS. `.keep` contains the word `placeholder` |
| `Dockerfile` | `php:8.2-apache` image |
| `docker-compose.yml` | `app` and `mysql` |
| `.htaccess` | `RewriteEngine On` and a no-op rule for `api/` when the request is not an existing file or directory |
| `.dockerignore` | Excludes `.git`, `.claude`, `*.md`, `.env` |
| `.gitignore` | `.env`, `.claude/`, IDE files, logs, `node_modules/`, `dist/` |

24 tracked files at `992fbe3`, including the vendored CodeMirror files. No `tests/`, `composer.json`, `package.json`, or pipeline YAML.

**Discrepancy:** task creation listed `sonar-project.properties`. It is not in this tree.

## Languages and frameworks

| Piece | Choice | Evidence |
|---|---|---|
| Server | PHP, PDO, no framework | `api/*.php` |
| HTTP server | Apache, `mod_rewrite` and `mod_headers` enabled | `Dockerfile` |
| Browser | Plain HTML, CSS, and JavaScript. No bundler | `index.html` script tags |
| Editor | CodeMirror, vendored, simple mode named `dbml` | `assets/js/editor.js`, `assets/vendor/` |
| Database | MySQL, charset `utf8mb4` | `api/db.php`, Compose image `mysql:8.0` |
| Image base | `php:8.2-apache` | `Dockerfile` |

PHP extensions installed in the image: `pdo`, `pdo_mysql`, `intl`. Application code uses PDO only. No call to `intl` was found.

CodeMirror's release number is **Unknown**. The minified file banner does not include a version string in the portion inspected, and the filenames do not either.

## Runtime configuration

`api/config.php` reads environment variables and falls back to WAMP-shaped defaults:

| Variable | Fallback | Used for |
|---|---|---|
| `DB_HOST` | `127.0.0.1` | PDO host |
| `DB_PORT` | `3306` | PDO port |
| `DB_USER` | `root` | PDO user |
| `DB_PASS` | empty string | PDO password |
| `DB_NAME` | `dbdaigram` | Database name |

Compose sets those five on the `app` service. `DB_HOST` is `mysql`. `DB_PASS` is `${DBDAIGRAM_MYSQL_PASSWORD:-dbdaigram}`. `DB_NAME` is `${DBDAIGRAM_DB_NAME:-dbdaigram}`. The MySQL service uses the same password variable as `MYSQL_ROOT_PASSWORD` and the same name as `MYSQL_DATABASE`.

Local WAMP, if Apache serves the tree with no env vars, connects to `127.0.0.1` as `root` with an empty password. That is the opposite of the Compose fallback password `dbdaigram`.

The README backup example invokes `C:\wamp64\bin\mysql\mysql9.1.0\bin\mysqldump.exe`. Compose pins `mysql:8.0`. Both are in the repository; they are not the same MySQL line.

No `.env` file is committed. `.env` is gitignored and dockerignored. There is no `.env.example`.

Session settings in `api/config.php`: cookie name `DBDAIGRAM_SESS`, `HttpOnly`, `SameSite=Lax`, `use_strict_mode`. Every response from a script that includes `config.php` sends `Content-Type: application/json; charset=utf-8` and `X-Content-Type-Options: nosniff`.

## Data model

Created by `bootstrap_schema` when `information_schema.tables` has no `users` row for `DB_NAME`. Identifier `DB_NAME` is interpolated into that query and into `CREATE DATABASE`. It comes from the environment or the constant, not from the request body.

### `users`

| Column | Type | Notes |
|---|---|---|
| `id` | `INT UNSIGNED` PK, auto-increment | |
| `username` | `VARCHAR(64)` NOT NULL, unique `uq_username` | |
| `display_name` | `VARCHAR(128)` NOT NULL default `''` | |
| `password_hash` | `VARCHAR(255)` NOT NULL | `password_hash` / `PASSWORD_DEFAULT` |
| `is_admin` | `TINYINT(1)` NOT NULL default 0 | |
| `created_at` | `DATETIME` default `CURRENT_TIMESTAMP` | |

### `diagrams`

| Column | Type | Notes |
|---|---|---|
| `id` | `INT UNSIGNED` PK, auto-increment | |
| `owner_id` | `INT UNSIGNED` NOT NULL, index `idx_owner` | FK to `users.id`, `ON DELETE CASCADE` |
| `title` | `VARCHAR(255)` NOT NULL default `Untitled diagram` | |
| `dbml` | `MEDIUMTEXT` NOT NULL | Editor source |
| `layout` | `MEDIUMTEXT` NOT NULL | JSON string |
| `is_shared` | `TINYINT(1)` NOT NULL default 0 | |
| `created_at` | `DATETIME` default `CURRENT_TIMESTAMP` | |
| `updated_at` | `DATETIME` default `CURRENT_TIMESTAMP` ON UPDATE | |

Layout JSON written by the client:

```text
{
  tables: { "<table name>": { x, y } },
  notes: { "<note name>": { x, y, w, h } },
  pan: { x, y },
  zoom: <number>
}
```

`loadDiagram` replaces missing `tables`, `notes`, `pan`, or `zoom` when `JSON.parse` succeeds, and replaces a parse failure with `{}` then the same defaults.

### `diagram_versions`

| Column | Type | Notes |
|---|---|---|
| `id` | `INT UNSIGNED` PK, auto-increment | |
| `diagram_id` | `INT UNSIGNED` NOT NULL, index `idx_diagram` | FK to `diagrams.id`, `ON DELETE CASCADE` |
| `dbml` | `MEDIUMTEXT` NOT NULL | |
| `layout` | `MEDIUMTEXT` NOT NULL | |
| `saved_by` | `INT UNSIGNED` NULL | No foreign key |
| `created_at` | `DATETIME` default `CURRENT_TIMESTAMP` | |

All three tables are `ENGINE=InnoDB`. The database, when this app creates it, is `CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`.

Bootstrap does not alter an existing `users` table. See business rule BR-025.

## HTTP API

Actions are the `action` query parameter. Mutations use `POST` with a JSON body from the client. The PHP scripts do not check the HTTP method. `body_json` returns `[]` when the body is empty or is not a JSON object.

Errors are `{error: "<message>"}` with a non-200 status. Unknown `action` is 404 `Unknown action`.

The client (`assets/js/api.js`) sends `credentials: 'same-origin'` and throws on any non-OK response, using `data.error` when present.

### `api/auth.php`

| Action | Auth | Body / query | Success | Failure |
|---|---|---|---|---|
| `login` | none | `{username, password}` | User object. Regenerates session id | 400 blank; 401 bad credentials |
| `logout` | none (session already started) | empty | `{ok: true}` after `session_destroy()` | |
| `me` | none | | `{user: null}` or `{user: {id, username, display_name, is_admin}}` | Missing row: session destroyed, `{user: null}` |
| `change_password` | login | `{current, new}` | `{ok: true}` | 400 if `new` shorter than 6; 403 if current does not verify |
| `users` | admin | | Array of users without password hashes | 401 / 403 |
| `create_user` | admin | `{username, password, display_name, is_admin}` | `{ok: true, id}` | 400 validation or duplicate username |
| `reset_password` | admin | `{user_id, new}` | `{ok: true}` even when no row matches | 400 if `new` shorter than 6 |
| `delete_user` | admin | `{user_id}` | `{ok: true}` | 400 if `user_id` is the session user |

`is_admin` in JSON responses is a boolean. The column is stored as 0 or 1.

### `api/diagrams.php`

Every action calls `require_login()` before the switch.

| Action | Extra rule | Input | Success |
|---|---|---|---|
| `list` | own or shared | | Rows: id, title, is_shared, owner_id, owner_name, created_at, updated_at, is_mine. Order `updated_at DESC` |
| `get` | `get_accessible_diagram` | `id` query | id, title, dbml, layout, is_shared, owner_id, owner_name, is_mine, updated_at |
| `create` | owner = session user, shared = 0 | `{title, dbml, layout}` | `{ok: true, id}` |
| `update` | accessible; only owner may set `is_shared` | `{id, title?, dbml?, layout?, is_shared?, snapshot?}` | `{ok: true}`. If `snapshot`, insert version and keep 50 |
| `delete` | accessible, and owner or session admin | `{id}` | `{ok: true}` |
| `duplicate` | accessible | `{id}` | `{ok: true, id}` of the copy |
| `versions` | accessible | `id` query | `{id, created_at, saved_by username}` newest id first |
| `version_get` | parent diagram accessible | `version_id` query | `{id, diagram_id, dbml, layout, created_at}` |

`get_accessible_diagram` selects the row and joins `users` for `owner_name`. Missing id: 404. Not owner and not shared: 403.

Update builds the `SET` clause from a fixed list (`title`, `dbml`, `layout`, and optionally `is_shared`). Field names are not taken from the client.

Connection failures return 500 `Database connection failed: ` plus the PDO message.

## Client behaviour that affects the server

| Topic | Detail | File |
|---|---|---|
| Editor debounce | 250ms, ignores `setValue` | `editor.js` |
| Autosave delay | 1800ms after `markDirty` | `app.js` |
| Explicit snapshot | Save button and Ctrl/Cmd+S. New diagrams create, then update with `snapshot: true` | `app.js` |
| Share before first save | `save(false)` then toggle | `app.js` |
| Local keys | `dbd_theme`, `dbd_last_diagram` | `app.js` |
| Zoom limits | 0.15 to 3; fit max 1.5; button factor 1.2; wheel factor 1.12 | `canvas.js` |
| PNG | 2× canvas, handles removed | `canvas.js` `exportPNG` |
| SQL file name | Title with `[^\w-]` replaced by `_` | `app.js` `doExport` |

DBML constructs the parser accepts are listed in `prd.md` WF-2. MySQL import and export rules are WF-5 and WF-6 in that document.

## Authentication

Passwords are stored with PHP `password_hash` / `PASSWORD_DEFAULT` and checked with `password_verify`. The login path regenerates the session id.

There is no CSRF token, no login throttle, no CAPTCHA, and no second factor in this code. `logout` does not add an explicit cookie-clear step beyond `session_destroy()`.

Static files under `assets/` and `index.html` are not gated by PHP. The editor calls `me` and shows the login card when no user is returned. API diagram routes reject a missing session with 401 `Not authenticated`.

## Docker and deploy

`Dockerfile`:

- From `php:8.2-apache`.
- Installs `libicu-dev`, then `docker-php-ext-install pdo pdo_mysql intl`.
- Enables Apache modules `rewrite` and `headers`.
- Copies the build context to `/var/www/html` and `chown`s it to `www-data`.
- `EXPOSE 80`.

`.dockerignore` drops markdown, so the image does not contain `README.md`. It also drops `.git`, `.claude`, and `.env`.

`docker-compose.yml`:

- `mysql`: image `mysql:8.0`, volume `mysql_data`, health check `mysqladmin ping` as root, `restart: unless-stopped`, `start_period: 30s`.
- `app`: build `.`, depends on `mysql` healthy, `restart: unless-stopped`.
- No `ports:` mapping and no routing labels.

**Documented** in the README, not represented as files here:

| Item | README value |
|---|---|
| URL | `https://dbdesign.appnotify.co.za/` |
| Dokploy project | `dbdaigram` |
| Bitbucket repo | `overdrive/db_design` |
| DNS | A record to `102.37.22.152`, same host as `kb.appnotify.co.za` |
| TLS | Let's Encrypt configured in Dokploy after DNS |
| Deploy trigger | Push to `main` |

**Unknown / Requires Confirmation:** the Dokploy application settings that publish port 80 on that hostname, and whether auto-deploy is still attached to `main`. They are not in git.

The README LAN section is an Apache vhost change on WAMP (`Require local` to a wider `Require`). That is operator documentation, not a file in the repo.

## Security-relevant facts

These are observations from the code, not a pentest and not a decision to change the product.

- The first-boot password `admin123` is in `README.md` and `api/db.php`. It is not rotated by later requests.
- Compose's fallback MySQL root password is the literal `dbdaigram` when `DBDAIGRAM_MYSQL_PASSWORD` is unset.
- Admin authority for API checks is the session copy from login (see `business-rules.md` BR-011).
- Shared diagrams are writable by every signed-in user, with no optimistic lock (`business-rules.md` BR-008).
- `reset_password` and `delete_user` report success when the id matches no row (`delete_user` still blocks the caller's own id).
- Database connection errors include the PDO exception text in the JSON body.
- `DB_NAME` is concatenated into SQL in `bootstrap_schema`. The value is server configuration, not an end-user field.
- Diagram update uses placeholders and a whitelist of column names.

## Development and testing

No test runner, fixture, or coverage config is in the repository. No `bitbucket-pipelines.yml` is in the repository.

A local PHP/Apache host with MySQL, or `docker compose` with the variables above, is the run path described by the Dockerfile, Compose file, and README. This inspection did not start either environment.

Vendor JavaScript is already minified. There is no build script to regenerate it.

## Git history relevant to structure

| Commit | Effect on the tree |
|---|---|
| `88d998d` | Bitbucket default `.gitignore` only |
| `334e2aa` | All application and Docker files |
| `c6e0389` | README production section |
| `992fbe3` | Merge, current `main` |

Author of the application commits: jacobrits1. Author of the empty initial commit: Jaco Brits. Both commits that add the app include a Cursor co-author trailer.