# Business rules — dbdaigram (DB Diagram)

Evidence date: 2026-10-17.

Source: `git@bitbucket.org:overdrive/dbdaigram.git`, `main` at `c6e0389` (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`, `api/config.php` `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, and 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. The parenthetical in the README matches the code. The phrase "any diagram" does not.

### 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. The function name says "confirm"; the code saves and continues. It does not show a confirm dialog.

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 `/* */`, `--`, and `#` comments before parsing. It ignores statements that are not those two forms, and it ignores `CHECK`, `FULLTEXT`, and `SPATIAL` inside a create. `UNSIGNED` is stripped from the emitted DBML type.

**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`. That export charset is a 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`, Bitbucket repo `overdrive/dbdaigram`, and that a push to `main` auto-deploys.

**Unknown / Requires Confirmation:** that those operational facts are still true, and that Dokploy is attached to this repository rather than `overdrive/db_design`. 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.