# Guidelines — ARCHITECTURE.md and AUDIT_CHECKLIST.md

This document defines the exact structure both files must follow for the diagram tool to parse them correctly. Read this before writing or generating either file.

---

## Part 1 — ARCHITECTURE.md

### Purpose
Documents your application's pages/screens, data sources, queries, and duplicate fetch patterns. It is read-only documentation — no fixes, no opinions. A snapshot of what the codebase does today.

---

### Required top-level sections

Your file must contain these H2 sections, in any order:

```markdown
## 1. Pages & Screens — Data Fetched
## 2. Database Tables & Columns
## 3. Queries Run Per Page
## 4. Duplicate Data Fetching
## 5. Consistency Risks
```

The parser matches these by keyword, not by number. You may add a number prefix or not. You may add additional H2 sections — the parser ignores unknown ones.

---

### Section 1 — Pages & Screens

Each page is an H3 heading followed by metadata and a data table.

#### Rules
- H3 heading must start with the route path, e.g. `### /stats — Session Analytics`
- The route path must start with `/` or be a named screen like `Home Screen`
- Include a **File:** line with the source file path
- Include an **Auth required:** line with `Yes` or `No`
- Include a markdown table with columns: `Data`, `Source`, `Function / Endpoint`
- The `Source` column must contain one of the recognised source keywords (see below)

#### Recognised source keywords (case-insensitive)
```
SQLite, Turso, PostgreSQL, MySQL, MongoDB, Redis,
localStorage, sessionStorage, AsyncStorage,
API, REST, GraphQL, gRPC, Supabase, Firebase,
Firestore, DynamoDB, S3, IndexedDB, Cookie, Context
```

If your source isn't listed, add it to the table and it will appear as a generic tag.

#### Example
```markdown
### `/stats` — Session Analytics
**File:** `src/app/stats/page.tsx`
**Auth required:** Yes

| Data | Source | Function / Endpoint |
|------|--------|---------------------|
| Overall stats | Turso | `GET /api/stats?session_id=` |
| Per-section breakdown | Turso + SQLite | `GET /api/stats/by-section?session_id=` |
```

---

### Section 2 — Database Tables & Columns

Each table is an H3 heading followed by a markdown table.

#### Rules
- H3 heading must contain the table name, e.g. `### \`attempts\` (Turso — remote)`
- Include which database the table lives in, in parentheses after the table name
- Table must have at minimum: `Column`, `Type`, `Constraints` columns
- A `Notes` column is optional but recommended
- List indexes below the table as plain text: `Indexes: column_a, column_b`

#### Example
```markdown
### `attempts` (Turso — remote)

| Column | Type | Constraints | Notes |
|--------|------|-------------|-------|
| `id` | INTEGER | PK AUTOINCREMENT | |
| `session_id` | TEXT | NOT NULL | User identifier |
| `is_correct` | INTEGER | NOT NULL | 0 or 1 |
| `time_ms` | INTEGER | NOT NULL | Milliseconds |
| `answered_at` | INTEGER | NOT NULL | Unix timestamp (seconds) |

Indexes: `session_id`, `question_id`
```

---

### Section 3 — Queries Run Per Page

Each page's queries are under an H3 heading matching its route, followed by SQL code blocks.

#### Rules
- H3 heading must match the route exactly as used in Section 1
- Each query is a fenced code block with language tag `sql`
- Add a comment above each block naming the function: `-- functionName()`
- Multiple queries per page are allowed — separate them with a comment line

#### Example
```markdown
### Stats `/stats`

```sql
-- GET /api/stats → sessionStats()
SELECT COUNT(*) AS total,
       SUM(is_correct) AS correct,
       AVG(time_ms) AS avg_time_ms
FROM attempts
WHERE session_id = ?
  [AND answered_at > ?];
```
```

---

### Section 4 — Duplicate Data Fetching

Each duplicate is an H3 heading describing what is duplicated, followed by a list of where it is fetched.

#### Rules
- H3 heading describes the data being duplicated, e.g. `### listSections() — Section list`
- Body lists each fetch location as a numbered item
- Include the file path and a short note on why it's separate

#### Example
```markdown
### `listSections()` — Section list with question counts

Fetched in:
1. `src/app/page.tsx` — called directly as a server function on home render
2. `GET /api/sections` — called by `/sections` page and `/practice/custom`
```

---

### Section 5 — Consistency Risks

Each risk is an H3 heading with a numeric ID, followed by body text.

#### Rules
- H3 heading format: `### 5.N Short Title` where N is a number
- Body must describe: what the risk is, when it occurs, what the user sees
- End with a **Fix:** line describing the recommended solution
- Include **Files:** with a comma-separated list of affected file paths

#### Example
```markdown
### 5.1 Missing `reset_at` Filter

The error-log quiz route silently omits `AND answered_at > reset_at` when
`getStatsResetAt` returns null. A user who pressed Reset is still served
pre-reset questions.

**Fix:** Treat null reset_at as an explicit error. Return 0 results rather
than unfiltered results.

**Files:** `src/app/api/questions/from-error-log/route.ts`
```

---

### General rules for ARCHITECTURE.md

- Use backticks around all file paths, function names, table names, and column names
- Use standard GFM markdown tables (pipes, dashes)
- Do not use HTML inside the file — the parser reads plain markdown only
- Add a generation date at the top: `> Generated: YYYY-MM-DD`
- Mark it read-only: `> Read-only documentation — no fixes applied`
- Keep it factual — no opinions, no suggested fixes (those go in the checklist)

---

---

## Part 2 — AUDIT_CHECKLIST.md

### Purpose
A prioritised, actionable list of everything wrong. References ARCHITECTURE.md section numbers. Gets checked off as fixes land.

---

### Required structure

```markdown
# Project Name — Audit Checklist

> Source: `ARCHITECTURE.md` (generated YYYY-MM-DD). No fixes applied yet.

---

## Critical — Data Inconsistency / Wrong Data Shown to User

- [ ] **Short title**
  Description of the problem.
  _Fix:_ What needs to be done.
  _Files:_ `path/to/file.ts`, `path/to/other.ts`

## High — Slow Queries / Missing Indexes

- [ ] **Short title**
  ...

## Low — Redundant Fetches / Minor Inefficiencies

- [ ] **Short title**
  ...
```

---

### Severity group rules

The parser recognises three severity levels by their H2 heading keyword:

| H2 heading must contain | Parsed as |
|---|---|
| `Critical` | critical (red) |
| `High` | high (orange) |
| `Low` | low (yellow) |

You may add subtitle text after the keyword — e.g. `## Critical — Data Inconsistency` — the parser matches on the keyword only.

---

### Checklist item rules

Each item must follow this exact format:

```markdown
- [ ] **Short title — optional reference like 5.1**
  One or two sentence description of the problem.
  _Fix:_ What to do to resolve it.
  _Files:_ `src/path/to/file.ts`, `src/other/file.ts`
```

#### Rules
- `- [ ]` = open (not fixed). `- [x]` = done (fixed). Use lowercase x only.
- Title is wrapped in `**double asterisks**`
- Description is on the next line, indented two spaces
- `_Fix:_` line is required — this appears in the detail panel
- `_Files:_` line is required — files appear as clickable chips in the detail panel
- Multiple files are comma-separated on the same line
- Do not add blank lines between the description, Fix, and Files lines of the same item
- Do add a blank line between separate checklist items

#### Example
```markdown
- [x] **Home page double-counts PS-tagged questions**
  Total was computed by summing per-section counts, inflating to 2,259 when real count is under 2,000.
  _Fix:_ `getTotalQuestionCount()` added to `db.ts`; page now uses `SELECT COUNT(*) FROM questions`.
  _Files:_ `src/lib/db.ts`, `src/app/page.tsx`

- [ ] **5.10 Session ID split — pre-login attempts invisible after Google sign-in**
  Attempts recorded under the localStorage UUID are silently dropped once the user authenticates.
  _Fix:_ Add `POST /api/migrate-session` to copy attempts from anonymous UUID to Google sub on first login.
  _Files:_ `src/app/api/migrate-session/route.ts`, `src/components/Quiz.tsx`
```

---

### General rules for AUDIT_CHECKLIST.md

- Order items within each severity group by user impact — most impactful first
- Every item must have a corresponding section in ARCHITECTURE.md (reference by 5.N number if applicable)
- Do not include items that are purely cosmetic or outside the codebase scope
- Keep titles short enough to fit on one line (under 80 characters)
- Keep descriptions factual — explain what happens, not how you feel about it
- The _Fix:_ line should be a single sentence of action, not an essay
- Update `- [ ]` to `- [x]` when a fix is merged — do not delete fixed items

---

### What makes a good audit checklist

| Good item | Bad item |
|---|---|
| Describes observable symptom | Vague ("this could be better") |
| Has a clear, single fix action | Fix is "investigate and maybe refactor" |
| Lists exact files | Lists a whole folder |
| Severity matches real user impact | Everything marked Critical |
| Fixed items marked [x], not deleted | Items disappear when fixed |