# PRD — dbdaigram (DB Diagram)

Evidence date: 2026-10-17.

Source: `git@bitbucket.org:overdrive/dbdaigram.git`, `main` at `c6e0389` (2026-07-07).

There is no prior PRD in the source repository or in Silicon Ark for this slug. Requirements below are recovered from `README.md` and from the PHP and browser code. Provenance is labelled. The default password is included because the README and `api/db.php` publish it as the first-login account.

Silicon Ark `db_design/` documents a second repository whose recorded history contains this same commit. See `project-plan.md` for that relationship. This PRD describes `overdrive/dbdaigram` only.

## Product purpose

**Documented.** `README.md` says this is a self-hosted dbdiagram.io replacement for internal team use: design schemas in DBML, see a live entity-relationship diagram, drag tables, and export MySQL, stored in MySQL.

**Implemented.** A single HTML page plus a PHP session API does that work. Parsing and drawing stay in the browser. Login, diagram load/save, sharing, versions, and user administration go to MySQL.

## Users and roles

| Actor | Evidence | Status |
|---|---|---|
| Signed-out visitor | `index.html` login form, `api/auth.php` action `login` | **Implemented** |
| Signed-in user | Session keys `user_id` and `is_admin`. Any user can create and edit their own diagrams and edit diagrams marked shared | **Implemented** |
| Diagram owner | `diagrams.owner_id`. Only the owner can change `is_shared`. Owner can delete their diagram | **Implemented** |
| Administrator | `users.is_admin = 1`. Seeded account `admin`. Can list users, create users, reset passwords, delete other users, and delete diagrams they can open | **Implemented** |
| Deploy operator | README sections "Making it available to the team (LAN)" and "Production (Dokploy)" | **Documented**. No admin screen for deploy |

Two roles exist in data: admin and non-admin. There is no manager, guest, or per-diagram permission other than owner versus shared.

The checkbox in the user dialog says an administrator "can manage users, delete any diagram". The server allows an admin to delete a diagram only after `get_accessible_diagram` succeeds, which requires the diagram to be owned by the caller or `is_shared = 1`. An admin does not see, open, or delete another user's private diagram.

**Discrepancy:** README line "admins can delete any diagram but see only shared/own ones" and the checkbox wording "delete any diagram" are broader than the access check. The parenthetical in the README matches the code. Provenance of the wording: **Documented**. Provenance of the check: **Implemented**.

## Systems the product talks to

1. The visitor's browser, loading `index.html` and files under `assets/` from the same host.
2. The PHP API on that host (`api/auth.php`, `api/diagrams.php`), using cookie `DBDAIGRAM_SESS`.
3. MySQL database `dbdaigram` (Docker service `mysql`, or local WAMP MySQL).
4. Dokploy, Let's Encrypt, and DNS, if the README production section is how the host is run. Those systems are not called by application code.

The client does not call a third-party API. CodeMirror is a local file. Google Fonts and other CDNs are not referenced.

## Major workflows

### WF-1 — Sign in and resume

`index.html` shows "DB Diagram" and "Internal database design tool". Username and password are required in the browser. Submit calls `POST api/auth.php?action=login` with JSON `{username, password}`.

The server trims the username, rejects an empty username or password, loads `users` by username, and checks the password with `password_verify`. On success it regenerates the session id and stores `user_id` and `is_admin`. The response is `{id, username, display_name, is_admin}`. On failure the HTTP status is 401 and the body is `{error: "Invalid username or password"}`. The page writes that error under the form and clears the password field after success.

On load, the page calls `GET api/auth.php?action=me`. A valid session enters the editor. An empty session shows the login card. If `me` finds the user row gone, it destroys the session and returns `{user: null}`. A network failure shows the login card and a toast.

After entry, the page reads `localStorage` key `dbd_last_diagram`. If that id loads, that diagram opens. If the load fails, or there is no id, the editor opens an unsaved diagram filled with the sample DBML in `assets/js/app.js` (tables `users` and `posts`, enum `user_role`, one ref).

Provenance: **Implemented**.

### WF-2 — Edit DBML and the diagram

The left pane is a CodeMirror editor in mode `dbml` (line numbers, active line, Tab inserts two spaces). A change that is not `setValue` waits 250ms, then parses and marks the diagram dirty.

`DBML.parse` accepts, case-insensitively: `Table` (optional schema prefix and `as` alias), field settings, `indexes`, `Ref` (one line or a block), `Enum`, `TableGroup`, `Project`, and `Note`. It records errors with line numbers. The bar under the editor shows the first 20 errors. Clicking an error moves the cursor to that line. A parser exception replaces the model with empty collections plus a "Parser crashed" error.

The right pane is an SVG canvas:

- Tables are placed from saved layout or, if new, on a grid of three per row.
- Header colour comes from the table setting `headercolor`, otherwise the theme colour.
- A table alias is drawn in the header. A table note is a hover title on an "≡" mark. A field note is a hover title on the row.
- Primary-key fields show a key mark and bold name. Non-key `not null` fields show an asterisk. Unique and increment are not drawn as their own marks.
- Relationship lines use `1` and `*` from the ref operator: `>` is `*` to `1`, `<` is `1` to `*`, `-` is `1` to `1`, `<>` is `*` to `*`.
- `TableGroup` draws a dashed rectangle around member tables. `Note` blocks are sticky notes. `Project` is parsed and not drawn. Enums are parsed and not drawn as boxes.
- Hovering a table highlights its lines. Double-clicking a table jumps the editor to that table's line.
- Wheel zoom is clamped between 0.15 and 3. Buttons zoom by 1.2. Fit is capped at 1.5. Auto-arrange clears stored positions and fits.
- Dragging the background pans. Dragging a table or note snaps to an 8px grid, updates layout, and marks the diagram dirty.
- Hovering a field shows round handles. Dragging a handle onto another field appends a `Ref` line. Cardinality: both ends `pk` or `unique` produces `-`; a key dragged onto a plain column produces `<`; otherwise `>`. An existing single-column ref between those two columns, in either direction, is refused with the toast "These columns are already connected". A drag onto the same field is ignored.

The splitter resizes the editor between 220px and 70% of the window width. That width is not stored.

Provenance: **Implemented**. The README feature list matches these behaviours (**Documented**).

### WF-3 — New, open, autosave, and explicit save

**New** saves first when the editor is dirty and not blank, then clears the document to an empty unsaved diagram (no sample). The pre-navigation save does not create a version snapshot. If that save fails, the promise still resolves and New continues.

**Open** lists diagrams the caller owns or that are shared, newest `updated_at` first. Columns: title, owner ("me" or username), visibility badge `team` or `private`, updated timestamp, Duplicate, and Delete when the row is the caller's or the caller is an admin. Choosing a title loads it, with the same dirty-check as New. Empty list copy: `No diagrams yet. Create one with "New" and press Save.`

**Autosave** runs about 1.8 seconds after a dirty mark (edit, title input, or layout change). It sends title (blank becomes `Untitled diagram`), DBML text, and layout JSON. It does not set `snapshot`. The status text cycles through `Unsaved changes`, `Saving…`, `Saved`, and `Save failed`.

The first save of an unsaved diagram is `create`. Later saves are `update`. A successful create stores `dbd_last_diagram`.

**Save** (button or Ctrl/Cmd+S) is the same payload with `snapshot: true`. For a brand-new diagram the client creates the row, then calls update with `snapshot: true` so the first explicit save still writes a version. Toast: `Saved — version snapshot created`.

Leaving the page while dirty triggers the browser `beforeunload` prompt.

Provenance: **Implemented**. README "Autosave ~2s" matches the 1800ms timer (**Documented** and **Implemented**).

### WF-4 — Share, duplicate, delete

**Share** is disabled when the open diagram belongs to someone else. Tooltip: `Only the owner can change sharing`. If nothing is saved yet, the client saves without a snapshot, then shares. The server rejects `is_shared` changes from a non-owner with 403 `Only the owner can change sharing`. Shared diagrams show the button as `Shared ✓`.

A shared diagram stays on its owner's `owner_id`. Every signed-in user can load it and update its title, DBML, and layout. Duplicate creates a new private diagram owned by the caller, titled `{original} (copy)`, with the same DBML and layout and no version rows copied.

Delete asks for confirmation (`This cannot be undone.`). The server allows the owner, or an admin who can already access the diagram. Deleting the diagram that is open starts a new empty diagram. Child version rows are removed by the foreign key `ON DELETE CASCADE`.

Provenance: **Implemented**. README statement that shared diagrams are visible and editable by every signed-in user, and that private diagrams are visible only to the owner: **Documented** and **Implemented**.

### WF-5 — Import MySQL

**Import SQL** opens a paste box. The client strips `/* */`, `--`, and `#` comments, splits on semicolons, and reads `CREATE TABLE` (optional `IF NOT EXISTS`) and `ALTER TABLE ... ADD FOREIGN KEY`.

Recognised column attributes: primary key, auto-increment, not null, unique, a simple `DEFAULT`, a `COMMENT`, and inline `ENUM(...)`. Table-level primary key, key/index, unique key, and table comment are recognised. `CHECK`, `FULLTEXT`, and `SPATIAL` are skipped. Other statements are ignored.

`ENUM` columns become DBML enums named `{table}_{column}_enum`. Foreign keys are emitted as `Ref: ... > ...` with `delete:` and `update:` when the SQL has `ON DELETE` / `ON UPDATE`. Schema-qualified names keep the last segment.

If no table was recognised, the toast is `No CREATE TABLE statements found in the pasted SQL.` Otherwise the user can replace the editor text or append it. The canvas fits, and the diagram is marked dirty (so autosave follows).

Provenance: **Implemented**. README "paste a mysqldump / phpMyAdmin export" is **Documented**.

### WF-6 — Export

The export menu offers three downloads. The file name is the diagram title with characters other than letters, digits, and hyphen turned into `_`.

| Export | Behaviour |
|---|---|
| DBML (`.dbml`) | Raw editor text. Comments and layout of the source are kept. |
| MySQL (`.sql`) | Refused with `Fix DBML errors before exporting SQL` when the model has errors. Otherwise `SQLExport.mysql`. |
| Image (`.png`) | Raster of the diagram bounds at 2×, connection handles omitted. Empty canvas rejects with `Nothing to export`. |

MySQL export writes `SET FOREIGN_KEY_CHECKS`, one `CREATE TABLE` per table (`ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`), then `ALTER TABLE ... ADD CONSTRAINT` for foreign keys. Many-to-many (`<>`) becomes a SQL comment telling the reader to create a junction table; it does not create one. Sticky notes, groups, project settings, and header colours are not in the SQL.

Type mapping that is special-cased: `string`/`str` → `VARCHAR(255)`, `number` → `DOUBLE`, `bool`/`boolean` → `TINYINT(1)`, `uuid` → `CHAR(36)`, `serial` → `INT AUTO_INCREMENT`, `timestamptz` → `TIMESTAMP`, `jsonb` → `JSON`. A matching enum becomes a MySQL `ENUM`. Anything else is uppercased as written. `now()` and `current_timestamp` defaults become `CURRENT_TIMESTAMP`. `true`/`false` become `1`/`0`.

Provenance: **Implemented**.

### WF-7 — Version history

**History** on an unsaved diagram toasts `Save the diagram first to start version history`.

Otherwise the client lists `diagram_versions` for that diagram (newest id first): timestamp and `saved_by` username, or an em dash when the user row is gone. **Restore** asks for confirmation, writes a snapshot of the current editor and layout, replaces the editor and layout with the chosen version, saves without a new snapshot, and toasts `Version restored`.

The server keeps the newest 50 version rows per diagram when a snapshot is written, and deletes older ones. The list itself does not apply a second limit.

Provenance: **Implemented**. README "last 50 kept" and "every explicit Save (Ctrl+S)" match the `snapshot` flag (**Documented** and **Implemented**). Autosave does not snapshot.

### WF-8 — Password and users

**Change password** (any signed-in user) asks for current, new, and repeat. The client requires the two new values to match. The server requires the new password length ≥ 6 and the current password to verify, then stores `password_hash(..., PASSWORD_DEFAULT)`. Wrong current password is 403 `Current password is incorrect`.

**Manage users** is shown only when `user.is_admin` is true on the session loaded at entry. The dialog lists username, display name, and badge `admin` or `user`. Actions: reset password (prompt, server enforces length ≥ 6), delete (not shown for the signed-in user; confirm text says their diagrams will be deleted), and create.

Create requires username matching `^[a-zA-Z0-9._-]{2,64}$`, password length ≥ 6, optional display name (defaults to the username), and an admin checkbox. Duplicate username returns `Username already exists`.

Delete user is refused for the caller's own id (`You cannot delete your own account`). Diagrams owned by the deleted user are removed by `ON DELETE CASCADE`.

There is no screen to rename a user, change a display name later, or toggle admin on an existing account. Password reset does not check that a row was updated; an unknown `user_id` still returns `{ok: true}`. `delete_user` likewise returns `{ok: true}` when the id matches no row, after the self-delete check.

Provenance: **Implemented**. README first-login steps are **Documented**.

### WF-9 — Theme

The moon/sun button toggles class `dark` on `body`, stores `localStorage` key `dbd_theme` (`light` or `dark`), and restyles the canvas. Default when the key is missing is light.

Provenance: **Implemented**. README "dark mode" is **Documented**.

## Functional requirements

### FR-001 — Sign in

The system authenticates a username and password against `users.password_hash`.

- Actor: signed-out visitor.
- Trigger: login form submit.
- Inputs: username, password.
- Behaviour: trim username; reject blanks; `password_verify`; regenerate session id; store `user_id` and `is_admin`.
- Output: user object, or 401 `{error}`.
- Validation: both fields required in the browser and on the server.
- Errors: empty fields `Username and password are required`; mismatch `Invalid username or password`.
- Evidence: `api/auth.php` `login`, `assets/js/app.js` `bindLogin`.

Implementation: **Implemented**. Confidence: High.

### FR-002 — Resume a session

A later page load with the session cookie returns the current user without asking for the password again.

- Actor: returning browser.
- Trigger: `DOMContentLoaded` → `API.me`.
- Output: `{user: {...}}` or `{user: null}`.
- Errors: missing user row destroys the session. Network failure shows the login card and a toast.
- Evidence: `api/auth.php` `me`, `assets/js/app.js` boot.

Implementation: **Implemented**. Confidence: High.

### FR-003 — Sign out

- Actor: signed-in user.
- Trigger: user menu → Sign out.
- Behaviour: `session_destroy()`, then the browser reloads.
- Evidence: `api/auth.php` `logout`, `btnLogout`.

Implementation: **Implemented**. Confidence: High.

### FR-004 — Change own password

See WF-8. Minimum length 6 is **Implemented**. The README tells the admin to change the seeded password and does not state the length rule; the length rule is code, not a written policy.

Implementation: **Implemented**. Confidence: High.

### FR-005 — Administer accounts

An administrator can list users, create a user, set that user's initial admin flag, reset a password, and delete a user other than themselves.

- Actor: user whose session `is_admin` is true.
- Trigger: Manage users.
- Errors: non-admin 403 `Admin access required`; bad username or short password 400; duplicate username 400; self-delete 400.
- Evidence: `api/auth.php` actions `users`, `create_user`, `reset_password`, `delete_user`; `usersModal`.

Implementation: **Implemented**. Confidence: High.

### FR-006 — Open the last diagram or the sample

See WF-1. Sample content is **Implemented** in `SAMPLE_DBML`. It is not described field-by-field in the README.

Implementation: **Implemented**. Confidence: High.

### FR-007 — Edit schema as DBML with live errors

The editor parses DBML and shows up to 20 errors that jump to the source line.

- Actor: signed-in user.
- Trigger: edits, debounced 250ms.
- Output: in-memory model and the error bar. Errors do not block autosave. They block MySQL export.
- Evidence: `assets/js/dbml-parser.js`, `editor.js`, `renderErrors`.

Implementation: **Implemented**. Confidence: High.

### FR-008 — Show and manipulate the diagram

See WF-2. Drag, zoom, fit, auto-arrange, groups, notes, and field-to-field refs are **Implemented**.

Implementation: **Implemented**. Confidence: High.

### FR-009 — Persist diagrams

A signed-in user can create and update a diagram. Title, DBML, and layout are stored. Blank title is stored as `Untitled diagram`.

- Access: create uses the session user as `owner_id` and leaves `is_shared` at 0. Update of title, DBML, and layout is allowed for the owner or for any user when `is_shared = 1`.
- Evidence: `api/diagrams.php` `create`, `update`; `save` in `app.js`.

Implementation: **Implemented**. Confidence: High.

### FR-010 — Autosave and explicit version snapshot

Autosave persists the current document and does not insert `diagram_versions`. Explicit Save and Ctrl/Cmd+S insert a version and then delete versions older than the newest 50 for that diagram.

- Evidence: `snapshot` in `app.js` and `api/diagrams.php` `update`.

Implementation: **Implemented**. Confidence: High.

### FR-011 — List, open, duplicate, and delete diagrams

The list contains rows where `owner_id` is the caller or `is_shared = 1`. Duplicate copies DBML and layout to a new private diagram owned by the caller. Delete is limited as in WF-4.

Implementation: **Implemented**. Confidence: High.

### FR-012 — Share with the team

Only the owner can set `is_shared`. Shared means every signed-in user can read and update the diagram content.

Implementation: **Implemented**. The README states the same rule (**Documented**). Confidence: High.

### FR-013 — Restore a version

Restoring writes a snapshot of the current content, then saves the chosen version's DBML and layout as the current diagram without a second snapshot.

- Access: `version_get` loads the version, then checks the parent diagram with the same owner-or-shared rule.
- Evidence: `historyModal`, `api/diagrams.php` `versions`, `version_get`.

Implementation: **Implemented**. Confidence: High.

### FR-014 — Import MySQL DDL into the editor

See WF-5. Conversion is entirely in the browser. Persistence happens only because the editor is then marked dirty.

Implementation: **Implemented**. Confidence: High.

### FR-015 — Export DBML, MySQL, and PNG

See WF-6.

Implementation: **Implemented**. Confidence: High.

### FR-016 — First-run database

On the first request that opens MySQL, if the database is missing (error 1049) the app creates it. If `users` does not exist, the app creates `users`, `diagrams`, and `diagram_versions`, and inserts username `admin`, display name `Administrator`, password `admin123`, `is_admin = 1`.

If `users` already exists, the function returns and does not alter tables or reset the admin password.

- Evidence: `api/db.php`. README "First login" and "Storage": **Documented**.

Implementation: **Implemented**. Confidence: High.

### FR-017 — Remember theme and last diagram in the browser

`dbd_theme` and `dbd_last_diagram` are stored in `localStorage` for that browser profile. They are not user preferences on the server. A different browser, or a cleared site store, does not restore them.

Implementation: **Implemented**. Confidence: High.

## Constraints

- One MySQL database per deployment, name from `DB_NAME` or default `dbdaigram`.
- Session cookie name `DBDAIGRAM_SESS`, `HttpOnly`, `SameSite=Lax`, `use_strict_mode`. No application-level expiry, CSRF token, or login rate limit is set in this repository.
- Diagram body and layout are `MEDIUMTEXT`. No application cap short of what MySQL accepts.
- Username 2–64 characters from the set letters, digits, `.`, `_`, `-`. Password minimum length 6 on create, reset, and change. No other complexity rule is in code.
- Export target implemented in code is MySQL only (`SQLExport.mysql`).
- The Docker Compose file does not publish host ports and does not contain Traefik or Dokploy labels. How `dbdesign.appnotify.co.za` reaches container port 80 is outside the repository.

## Assumptions and conflicts

| Topic | Where it stands |
|---|---|
| Product name | UI says DB Diagram. Repo slug, database name, and Dokploy project name are `dbdaigram`. Production hostname is `dbdesign.appnotify.co.za`. |
| Sibling repository | Silicon Ark `db_design/` records the same commits `334e2aa` and `c6e0389` inside `overdrive/db_design`, plus a later merge. Which repo is deployed is **Unknown / Requires Confirmation**. |
| Default password still in production | Code and README still seed and publish `admin` / `admin123`. Whether production was changed is **Unknown / Requires Confirmation**. |
| "Delete any diagram" | UI and README phrase vs access check. See Users and roles. |
| Auto-deploy on push to `main` | **Documented** in the README for `overdrive/dbdaigram`. No pipeline file in the repository. |
| `intl` PHP extension | Installed in the image. Unused by the PHP in this tree. **Inferred** as unused. |
| WAMP MySQL 9.1 versus Compose MySQL 8.0 | Both appear in the repo (a filesystem path in the README, and `mysql:8.0` in Compose). Which engine production runs is the Compose image if Dokploy uses this file. **Unknown / Requires Confirmation** if Dokploy overrides the image. |