# Business rules — db_design (DB Diagram)

Evidence date: 2026-10-17.

Source: `git@bitbucket.org:overdrive/db_design.git`, `main` at `992fbe3` (2026-07-07).

No business-rules document exists in the source repository. The README states a few rules in prose. Everything else below is taken from PHP and browser code. Anything marked **Inferred** is a conclusion from that evidence, not a written policy.

## Identity and access

### BR-001 — Two roles, stored as a flag

**Implemented.**

`users.is_admin` is 0 or 1. The session copies that flag at login (`is_admin` true only when the column is 1). `require_admin` reads the session flag, not the database.

**Inferred.** Changing `is_admin` in MySQL does not change a session that is already open. The user must sign in again. No API updates `is_admin` after create, so this only matters if the column is edited outside the app.

Evidence: `api/auth.php` `login`, `require_admin`.

### BR-002 — Seeded administrator

**Documented** in `README.md` and **Implemented** in `api/db.php`.

When the `users` table is created, the app inserts username `admin`, display name `Administrator`, password `admin123`, `is_admin = 1`. The README says to change that password immediately.

The insert runs only inside `bootstrap_schema`, which returns immediately when `users` already exists. A later request does not recreate or reset this account.

### BR-003 — Username and password limits

**Implemented.** Not stated in the README.

| Check | Rule | Where |
|---|---|---|
| Login blanks | Username and password are required | `login` |
| New username | 2–64 characters, only letters, digits, `.`, `_`, `-` | `create_user` |
| Duplicate username | `Username already exists` when MySQL raises integrity error 23000 | `create_user` |
| New, reset, or changed password | Length at least 6 | `create_user`, `reset_password`, `change_password` |
| Change password | Current password must verify. The two new values must match in the browser | `change_password`, `changePasswordModal` |
| Display name | Trimmed. Empty becomes the username. Column is `VARCHAR(128)` | `create_user`, schema |

No rule requires a digit, a symbol, or a different password from the current one. No rule expires a password. No rule locks an account after failed logins.

### BR-004 — A user cannot delete their own account

**Implemented.**

`delete_user` compares `user_id` to the session user and returns `You cannot delete your own account`. The Manage users dialog also hides Delete on that row.

### BR-005 — Deleting a user deletes their diagrams

**Implemented** schema, and **Documented** in the confirm text in `usersModal` ("Their diagrams will be deleted too.").

`diagrams.owner_id` references `users.id` with `ON DELETE CASCADE`. `diagram_versions.diagram_id` references `diagrams.id` with `ON DELETE CASCADE`. Versions of those diagrams go away with them.

`diagram_versions.saved_by` has no foreign key. A version on someone else's diagram can keep a `saved_by` id after that user is deleted. The history list then shows an em dash because the username join fails.

### BR-006 — Who can see a diagram

**Documented** in the README and **Implemented** in `get_accessible_diagram` and `list`.

A signed-in user may list and open a diagram when they are the owner or `is_shared = 1`. Otherwise get/update/delete/duplicate/versions return 403 `Access denied` or, if the id does not exist, 404 `Diagram not found`.

There is no per-person share list. Share is with every account that can sign in.

### BR-007 — Who can change sharing

**Implemented.** The Share button title states the same rule.

Only `owner_id` may set `is_shared`. Anyone else receives 403 `Only the owner can change sharing`. The button is disabled for a non-owner.

New diagrams are private. `create` does not read an `is_shared` field.

### BR-008 — Who can edit a shared diagram

**Documented** in the README ("visible and editable by every signed-in user") and **Implemented**.

`update` allows the owner or any signed-in user, when the diagram is shared, to change `title`, `dbml`, and `layout`. Autosave uses that path, so a teammate's edits are written to the same row.

**Inferred.** Two people with the diagram open can overwrite each other. The code has no version check, lock, or `updated_at` compare on save.

### BR-009 — Who can delete a diagram

**Implemented.**

The caller must already be allowed to open the diagram (BR-006). Then delete is allowed if they are the owner or the session is admin. Otherwise 403 `Only the owner can delete a diagram`.

The Open dialog shows Delete when `is_mine` or `state.user.is_admin`.

**Discrepancy.** The admin checkbox label says an administrator can "delete any diagram". The README says admins "can delete any diagram but see only shared/own ones". Private diagrams owned by someone else fail BR-006 before the admin exception. Those diagrams are not in the list and cannot be deleted through this API.

### BR-010 — Duplicate ownership

**Implemented.**

Duplicate inserts a new row owned by the caller, `is_shared` default 0, title `"{title} (copy)"`, same `dbml` and `layout`. Version history is not copied.

A user may duplicate a shared diagram they do not own, because duplicate calls `get_accessible_diagram`.

### BR-011 — Admin flag is not re-checked per request

**Inferred** from **Implemented** session handling.

`require_admin` and the diagram delete exception use `$_SESSION['is_admin']` from login time. `me` does re-read `users` for the boot payload, which decides whether Manage users is shown after a full reload. API calls in an already-open page keep using the session flag.

## Diagram content

### BR-012 — Empty title

**Implemented.**

The client sends `trim(title) || 'Untitled diagram'`. The server uses the same fallback on create: `trim($b['title'] ?? '') ?: 'Untitled diagram'`.

Column limit is `VARCHAR(255)`. The application does not truncate or reject a longer title before MySQL.

### BR-013 — Autosave versus a version

**Documented** ("every explicit Save (Ctrl+S) snapshots a version") and **Implemented**.

A version row is inserted only when the client sends `snapshot` true. That flag is set for the Save button and for Ctrl/Cmd+S. Autosave (1800ms), the save-before-New/Open path, and the save after a restore send `snapshot` false.

On the first explicit save of a new diagram, the client creates the row and then calls update with `snapshot` true so the version exists.

Restore first snapshots the current buffer, then writes the old content with `snapshot` false. The pre-restore content is a version. The restored content becomes current without its own version until the next explicit save.

### BR-014 — Fifty versions

**Documented** and **Implemented.**

After inserting a snapshot, the server deletes `diagram_versions` for that diagram whose ids are not in the newest 50 ids. The cap is per diagram, by version id descending, not by age in days.

### BR-015 — Dirty navigation still continues if save fails

**Inferred** from **Implemented** promise handling.

`confirmIfDirty` saves when the buffer is dirty and not blank, then runs the next action. `save` catches errors and does not rethrow, so New and Open still proceed after a failed save. The status line shows `Save failed` and a toast.

If the buffer is blank, New and Open do not save, even if the dirty flag is set.

### BR-016 — Relationship cardinality from a drag

**Documented** in the README and **Implemented** in `createRefFromUI`.

| Ends | Operator written |
|---|---|
| Both columns are `pk` or `unique` | `-` (one-to-one) |
| The dragged-from column is `pk` or `unique`, the target is not | `<` |
| Otherwise | `>` |

The line is appended at the end of the buffer. A single-column ref that already joins those two columns in either direction is not added. Composite refs are not created from the canvas. Delete and update actions are not set by a drag.

### BR-017 — MySQL export cardinality

**Implemented** in `assets/js/sql-export.js`.

The foreign key is placed on the many side. For `>` the FK is on `from`. For `<` the ends are swapped so the FK is on the original `to`. For `-` the FK is on `from`. For `<>` the exporter emits a SQL comment and does not create a table.

Export refuses to run while the parser's error list is non-empty. DBML and PNG exports do not apply that check.

### BR-018 — Import does not replace the server copy until save

**Inferred** from **Implemented** client flow.

Import rewrites the editor locally and calls `markDirty`. The server row changes only when autosave or Save succeeds.

### BR-019 — What import keeps

**Implemented** in `assets/js/sql-import.js`.

The importer reads table and column comments, primary keys, indexes, unique keys, nullability, defaults, auto-increment, inline enums, and foreign keys from `CREATE TABLE` or `ALTER TABLE`. It drops SQL comments before parsing. It ignores statements that are not those two forms, and it ignores `CHECK`, `FULLTEXT`, and `SPATIAL` inside a create.

**Inferred.** A dump that is only `INSERT` or only views produces `No CREATE TABLE statements found in the pasted SQL.` Data rows are not imported. That matches a schema tool; the README does not say data is imported.

### BR-020 — Names that need quotes

**Implemented.**

DBML identifiers that are not `^[A-Za-z_][A-Za-z0-9_]*$` are written in double quotes by import and by drag-to-connect.

## Session and browser state

### BR-021 — Cookie flags

**Implemented** in `api/config.php`.

Cookie name `DBDAIGRAM_SESS`. PHP settings: `HttpOnly` on, `SameSite=Lax`, `use_strict_mode` on. The application does not set `Secure`, a custom lifetime, or a CSRF token.

**Inferred.** On the HTTPS host named in the README, `Secure` is not forced by this code. Whether the platform sets it outside PHP is **Unknown / Requires Confirmation**.

### BR-022 — Theme and last diagram are local

**Implemented.**

`dbd_theme` and `dbd_last_diagram` live in `localStorage`. They are not columns on `users`. Signing in as someone else in the same browser still opens the previous diagram id, and the get is then subject to BR-006. If get fails, the sample document opens and the stored id is removed.

### BR-023 — Failed login does not reveal which field was wrong

**Implemented.**

Unknown user and wrong password return the same 401 message: `Invalid username or password`.

## Schema lifecycle

### BR-024 — Database and tables appear on first use

**Documented** ("auto-created with its tables and the admin account on first request") and **Implemented**.

Missing database (MySQL error 1049) causes `CREATE DATABASE` with `utf8mb4` / `utf8mb4_unicode_ci`. Missing `users` table causes the three `CREATE TABLE` statements and the admin insert.

### BR-025 — No later migration

**Inferred** from **Implemented** `bootstrap_schema`.

If `users` exists, the function returns. New columns or indexes added in a future code version would not be applied to an existing database by this function. Nothing else in the repository runs SQL migrations.

### BR-026 — Engine and character set

**Implemented** for tables the app creates: `InnoDB`. The database, when created by the app, is `utf8mb4` / `utf8mb4_unicode_ci`. Exported customer DDL from the diagram tool is also `InnoDB` and `utf8mb4`, which is a separate rule about generated SQL, not about the app's own tables.

## Deployment rules stated by the README

These are **Documented**. They are not enforced by application code.

### BR-027 — Local team access is an Apache change

The README says WAMP blocks non-local clients until `Require local` is widened and Apache is restarted, and that teammates then use `http://<host>/dbdaigram/` and sign in.

### BR-028 — Production is the Dokploy stack

The README says production is Docker Compose on Dokploy, hostname `https://dbdesign.appnotify.co.za/`, project `dbdaigram`, and that a push to `main` auto-deploys.

**Unknown / Requires Confirmation:** that those operational facts are still true. They are not re-checked by this repository's code.

## Rules that are not in the product

No rule was found for billing, retention beyond the 50 versions, audit logging, password recovery by email, multi-factor authentication, or approval before a diagram is shared. Absence is **Implemented** as "no such path in the code", not a documented decision to forbid them.