# Project plan — db_design (DB Diagram)

Evidence date: 2026-10-17.

Source repository: `git@bitbucket.org:overdrive/db_design.git`.

Inspected revision: `main` at `992fbe3` (2026-07-07, jacobrits1, "merge: integrate app into overdrive/db_design repo"). The scan-host path `~/projects/sonar_scan/repos/db_design` was not on this machine. The tree below was read from that git revision.

This plan describes what the repository contains. It does not decide what the product should become.

## Product

The README titles the product **DB Diagram — Internal** and describes it as a self-hosted replacement for dbdiagram.io, for internal team use. The browser title and login heading are **DB Diagram**. The repository slug is `db_design`.

The MySQL database name used everywhere in code, Docker Compose, and the README is **`dbdaigram`** (that spelling). The local WAMP URL in the README is `http://localhost/dbdaigram/`. The Dokploy project name in the README is also `dbdaigram`. The production hostname in the README is `dbdesign.appnotify.co.za`.

Provenance: **Documented** in `README.md` and **Implemented** in `api/config.php`, `docker-compose.yml`, and `index.html`.

**Unknown / Requires Confirmation:** whether the `dbdaigram` spelling is intentional.

There is no separate product requirements document, roadmap, or ticket list in the repository.

## Branch reality

| Line | Tip | Date | What it is |
|---|---|---|---|
| `main` (default; `origin/HEAD`) | `992fbe3` | 2026-07-07 | Only branch. Four commits. |

History, oldest first:

| Commit | Date | Author | Message | What landed |
|---|---|---|---|---|
| `88d998d` | 2026-07-07 | Jaco Brits | Initial commit | Bitbucket `.gitignore` only |
| `334e2aa` | 2026-07-07 | jacobrits1 | feat: add Docker deployment for Dokploy | The application, Dockerfile, Compose, and the first README |
| `c6e0389` | 2026-07-07 | jacobrits1 | docs: add Dokploy production deployment details | Production URL, Dokploy project name, DNS note, default login reminder |
| `992fbe3` | 2026-07-07 | jacobrits1 | merge: integrate app into overdrive/db_design repo | Merge of `88d998d` and `c6e0389` |

No other local or remote branches were present at inspection. Nothing in history is a removed product feature. The initial commit's longer `.gitignore` is not the file at `HEAD`; `HEAD` has the shorter ignore file added with the application.

**Discrepancy:** the task-creation scan (2026-09-28) listed `sonar-project.properties` at the repository root. That file is not in git at `992fbe3`. Provenance: **Unknown** — it may have been an untracked file on the scan host.

## Objectives

Taken from `README.md` (**Documented**) and matched to code (**Implemented**):

- Let a signed-in person design a database schema in DBML and see a live diagram.
- Drag tables, draw relationships from the canvas, and keep positions with the diagram.
- Import a MySQL dump into DBML and export MySQL DDL, DBML source, or a PNG.
- Give each person a login, private diagrams, and a way to share a diagram with every signed-in user.
- Keep the last 50 explicit saves as restorable versions.
- Run on local WAMP (Apache + MySQL) or as a Dokploy Docker Compose stack (PHP 8.2 + Apache + MySQL 8).

No written objective beyond the README was found.

## Scope

### In scope of `main`

1. A single-page browser application: login, DBML editor, diagram canvas, diagram list, version history, SQL import, export, user management.
2. Two PHP JSON endpoints: `api/auth.php` and `api/diagrams.php`.
3. MySQL schema created on first connection if the `users` table is missing, including a seeded admin account.
4. Docker image (`php:8.2-apache`) and Compose file (app + MySQL 8.0).
5. README instructions for local WAMP, LAN exposure, backup, and Dokploy.

### Out of scope of the committed tree

- Automated tests, a CI pipeline file, Sonar configuration, Composer, npm, and a front-end build step.
- Email, password reset by the user, SSO, or an identity provider.
- Databases other than MySQL as an export/import target. The parser accepts a `Project` block `database_type`, and the canvas does not use it.
- A junction-table generator for many-to-many refs. Export writes a SQL comment instead.
- Dokploy domain, TLS, and auto-deploy settings. The README describes them. They are not files in this repository.

## Architecture

```text
Browser (index.html + assets/js)
    |  same-origin JSON, session cookie DBDAIGRAM_SESS
    v
Apache + PHP 8.2
    api/auth.php        login, session, users
    api/diagrams.php    diagrams and versions
    api/db.php          connect, create database, create tables, seed admin
    v
MySQL 8 (Docker) or local WAMP MySQL
    database name: dbdaigram
    tables: users, diagrams, diagram_versions
```

Parsing, diagram drawing, SQL import, and SQL export run in the browser. The server stores accounts, diagram text, layout JSON, and version rows. CodeMirror is vendored under `assets/vendor/`. The README states the app does not need internet access once it is hosted.

Provenance: **Implemented**, with the "no internet" sentence **Documented** in `README.md`.

## Major components

| Component | Path | Role |
|---|---|---|
| Page shell | `index.html` | Login view and editor view |
| App controller | `assets/js/app.js` | Auth, save, share, import/export, modals |
| DBML parser | `assets/js/dbml-parser.js` | DBML to an in-memory model |
| Canvas | `assets/js/canvas.js` | SVG diagram, drag, zoom, PNG |
| Editor | `assets/js/editor.js` | CodeMirror DBML mode |
| SQL export | `assets/js/sql-export.js` | Model to MySQL DDL |
| SQL import | `assets/js/sql-import.js` | MySQL DDL to DBML text |
| API client | `assets/js/api.js` | `fetch` wrapper |
| Auth API | `api/auth.php` | Session and users |
| Diagram API | `api/diagrams.php` | CRUD, share, versions |
| Schema bootstrap | `api/db.php` | Create database, tables, admin row |
| Config | `api/config.php` | DB settings, session, JSON helpers |
| Image | `Dockerfile` | PHP 8.2 Apache |
| Stack | `docker-compose.yml` | `app` + `mysql` |

## Milestones

There is no milestone list in the repository. The commits above are the whole delivery history: one day (2026-07-07), from an empty Bitbucket repository to the application plus Dokploy notes.

**Unknown / Requires Confirmation:** whether later deploys happened after `992fbe3`. Git on `main` has no newer commit.

## Dependencies

Runtime, from the Dockerfile, Compose file, and PHP `require` graph:

- PHP 8.2 with `pdo`, `pdo_mysql`, and `intl`. `intl` is installed in the image. No PHP file in the repository calls an `intl` function.
- Apache with `rewrite` and `headers` enabled. `.htaccess` turns rewrite on and adds a rule that does not map URLs to a front controller.
- MySQL. Compose pins image `mysql:8.0`. The README backup example uses a WAMP path under `mysql9.1.0`.
- Browser APIs used by the client: `fetch`, `localStorage`, SVG, Canvas `toBlob`, `pointer` events.

No `composer.json`, `package.json`, or lockfile is in the tree. CodeMirror ships as four minified files. The exact CodeMirror release is **Unknown** — the filenames do not include a version.

## Current project status

**Implemented** and committed on `main` as of 2026-07-07: the editor, canvas, accounts, diagram storage, versions, import, export, and the Docker stack.

**Documented** deployment target: Dokploy project `dbdaigram`, URL `https://dbdesign.appnotify.co.za/`, DNS A record to `102.37.22.152`, push to `main` auto-deploys. This inspection did not open that URL or Dokploy. Whether that deployment is still current is **Unknown / Requires Confirmation**.

## Known gaps (evidence, not a backlog)

- No test suite and no pipeline definition in the repository.
- `sonar-project.properties` from the discovery scan is not in git.
- Schema changes after the first boot are not applied: `bootstrap_schema` returns as soon as `users` exists.
- Admin rights are copied into the session at login and are not re-read from `users` on later requests.
- Shared diagrams are editable by every signed-in user. The README states this explicitly.