thptqg/docs/system-architecture.md

# System Architecture

Static site, no backend. The browser downloads the whole SQLite file once and
queries it in memory with `sql.js` (SQLite compiled to WebAssembly). 2016 costs
about 31 MB to fetch and 142 MB of memory to hold; 2017, 36 MB and 119 MB. Every
query after that is local — an exam-number lookup is immediate, and a name
search scans all 877,460 rows in a few hundred milliseconds.

The download is kept on the device, so the transfer is paid once per device
rather than once per visit.

That is a reversal of the previous design, which read the file where it lay
over HTTP range requests. See [Considered and not taken](#considered-and-not-taken).

One frontend, one parser, one schema, two datasets.

## Data flow

Each stage is a directory; `data/` and `_site/` are the stores they hand work
through. `assembler/` sequences everything from the parser onwards.

```
        ▲  crawler/   (Go — manual refresh only, never part of the build)
data/<id>/*.xls(x)
        │
        ▼  parser/    (Go, one binary, one config per dataset)
   .build/public/db/<id>.sqlite3
        │
        ▼  assembler/ — row count and size must match datasets.json
   .build/public/db/<id>.sqlite3    (uncompressed: the host gzips it on the
        │                            wire, so a .gz would decompress twice)
        ▼  assembler/ → npm run build (SvelteKit static, assets = .build/public)
   web/dist/
        │
        ▼  assembler/ — one index.html per dataset; every database must be present
   _site/   →  GitHub Pages
        │
        ▼  browser
   download gate → whole file → sql.js in memory → queries never leave the tab
                        ↕
                 Cache Storage, keyed by ETag — a second visit skips the gate
```

## The download, and keeping it

`web/src/lib/database.svelte.js` owns the transfer; `web/src/lib/db-cache.js`
owns what survives it. Opening a dataset page:

1. A conditional request asks the server for the database's current ETag.
2. If a copy of exactly that version is in Cache Storage, it opens without
   asking — consent was given once and reuse costs no network.
3. Otherwise the gate states the transfer and the memory cost, and downloads
   only when the visitor accepts. The response is stored under
   `<url>?v=<etag>`, and every other version of that database is dropped, so a
   dataset never occupies the disk twice.
4. If the server cannot be reached at all, any stored version is used. Slightly
   old frozen exam results beat an error page, and this is what lets the site
   answer offline.

Cache Storage rather than IndexedDB: the thing being stored is an HTTP
response, the browser can stream it to disk instead of holding it as one
buffer, and eviction under storage pressure is the browser's business.

Progress is measured against `dbSizeMb` from the registry, not
`Content-Length`. The server sends the file gzipped, so `Content-Length` counts
compressed bytes while the stream yields decompressed ones; the compressed
figure is reported separately as the transfer size.

## The dataset id

One identifier ties the whole pipeline together:

```
data/2017/  →  parser/configs/2017.yml  →  db/2017.sqlite3   →  /thptqg/2017/
```

`datasets.json` at the repository root declares the ids once, with the row count
and artifact size the assembler enforces. It is JSON rather than a module
because the assembler is a Go program and the web app is not, and JSON is the
only format both parse without a dependency.

Presentation — titles, labels, search examples, SQL presets — stays in
`web/src/lib/datasets.js`, keyed by id. That file cross-checks the two: a registry
entry with no content, or content for a dataset that was never built, throws at
module load rather than rendering a page with no title or a link to a database
that does not exist.

| id | Exam | Rows | Source |
| --- | --- | --- | --- |
| `2016` | 2016 | 877,460 | `dtnt.bacninh.edu.vn` |
| `2017` | 2017 | 861,068 | `baotintuc.vn` |

Full source URLs are in [data-pipeline](./data-pipeline.md#sources); the web
footer links to them per dataset.

## Canonical schema

Defined once in `parser/internal/schema/schema.go` — DDL, INSERT, column order and the 16
subject regexes. The two YAML configs carry no SQL at all, only per-dataset
parse rules. Config parsing sets `KnownFields(true)`, so a leftover `schema:`
block fails loudly instead of looking effective while `schema.go` drives the
build.

```sql
CREATE TABLE student (
  so_bao_danh   TEXT PRIMARY KEY,   -- 2017: 8 digits; 2016: 9 digits or a
                                    -- 2-4 letter cluster code then digits
  ho_ten        TEXT NOT NULL,
  ho_ten_ascii  TEXT NOT NULL,      -- NFD-stripped lowercase, for accent-insensitive search
  ngay_sinh     TEXT,               -- dd/mm/yyyy
  ten_cum_thi   TEXT,               -- 2016 only
  gioi_tinh     TEXT,               -- 2016 only
  toan, ngu_van, vat_ly, hoa_hoc, sinh_hoc, khtn,
  lich_su, dia_ly, gdcd, khxh,
  tieng_anh, tieng_phap, tieng_nga, tieng_duc, tieng_nhat, tieng_trung   REAL
);
```

One table and no secondary indexes. The primary key is the only one, and it
comes free with the table. Everything else is a scan, which in memory costs a
few hundred milliseconds — an index would buy that back at the price of
megabytes on every visitor's network. `TestDDLIsFrozen` in `parser` holds an
independent copy of this DDL, so changing the shape has to be deliberate.

Every dataset gets all 22 columns; ones it has no data for are NULL, costing
about a byte per row. `khtn`, `khxh` and `gdcd` are empty on 2016;
`ten_cum_thi` and `gioi_tinh` are empty on 2017.

## Routing

URLs are flat, one segment per dataset, and the segment is the id:

```
/thptqg/            hub
/thptqg/2016/
/thptqg/2017/
```

The route is `web/src/routes/[dataset]/`, and its entry generator reads the same
`datasets.json` the assembler does, so the set of pages and the set of databases
cannot drift apart. Unknown paths fall through to the hub.

SvelteKit prerenders one HTML file per route, each with its own `<title>`.
Asset URLs stay absolute (`paths.relative: false`), so the copy of the hub that
serves as `404.html` resolves its assets from any depth. **No SPA 404-fallback
redirect is used** — the usual hack rewrites URLs and would interfere with the
deep links.

## Serving both exam years without branching

No component contains a per-dataset conditional. Two mechanisms do the work:

- **All-NULL columns are hidden.** `score-table.svelte` drops any column where
  every row in the result set is NULL, so 2016 rows surface Cụm thi / GT / Đức /
  Nhật and 2017 rows surface KHTN / KHXH / GDCD / Nga.
- **Incomplete admission blocks are skipped.** `computeBlocks()` only returns a
  block when the student has all three subjects, so one block list covers both
  years: GDCD blocks self-exclude on 2016, German and Japanese blocks
  self-exclude wherever those languages were not sat.

Anything genuinely per-dataset — title, source, database size, search examples,
SQL presets — lives in `web/src/lib/datasets.js`.

## Exam ID formats

`web/src/lib/query-mode.js` decides whether a query is an exam ID or a name, and
is shared by the dataset page and `search-form.svelte` (they previously held
separate copies and had drifted apart on exactly this rule). `query-mode.test.js`
covers every form in the table below.

| Form | Example | Where |
| --- | --- | --- |
| 8 digits | `49008235` | 2017 — first two digits are the province |
| 9 digits with leading zero | `017006021` | 2016 |
| 2-4 letters then digits | `BAL000001` | 2016 — exam cluster code |

The letter-prefixed form is the majority case for 2016: 624,424 of 877,460
candidates (71.2%); the remaining 253,036 are all 9-digit. Letter prefixes are
upper-cased before lookup, so `bal000001` resolves.

## Score tiers

Six-level ladder in `scoreTier()` (`web/src/lib/admission-blocks.js`), paired with a
symbol so meaning is never colour-only.

| Tier | Range | Vietnamese |
| --- | --- | --- |
| common | ≤ 1 | Điểm liệt |
| uncommon | < 5 | Chưa đạt |
| rare | 5–6.5 | Trung bình |
| epic | 6.5–8 | Khá |
| legendary | 8–9 | Giỏi |
| prismatic | 9–10 | Xuất sắc |

## Admission blocks

Vietnamese universities admit on three-subject combinations (khối thi).
`web/src/lib/admission-blocks.js` lists the blocks computable from this schema
(A00–A11, B00–B08, C00–C20, D01–D15, plus D05/D06 for German and Japanese).
`computeBlocks(student)` returns those where all three scores exist, sorted by
total descending.

## Design decisions

| Concern | Choice | Rationale |
| --- | --- | --- |
| Storage | Static SQLite file, downloaded whole | No backend; the datasets are frozen, and one large transfer is something browsers and CDNs are both good at |
| Download gate | Blocking, no dismiss | The page has no answers before the file arrives, and 31 MB of someone's mobile data should be asked for rather than spent silently |
| Keeping the download | Cache Storage, versioned by ETag | The transfer is paid once per device instead of once per visit; the ETag is what stops a redeploy being answered with last week's data |
| Searching | On submit, never while typing | A name search scans every row, so a keystroke-per-search would run hundreds of full scans to answer one question |
| Compression | None published | The host gzips on the wire, so a `.gz` artifact would only be decompressed twice |
| WASM hosting | Bundled with the app | `sql.js` ships its own build; one less third-party runtime dependency |
| Diacritics search | Pre-computed `ho_ten_ascii` | `LOWER(REPLACE(...))` at query time is far slower over 877,460 rows than a column computed once at build |
| Secondary indexes | None | In memory a full scan costs a few hundred milliseconds; an index costs every visitor megabytes of download. The name index alone was 146 MB |
| Row count in the footer | Read from `datasets.json` | Costs nothing and is the same number the assembler enforces |
| Page size | 4 KiB | SQLite's default, and nothing on the client depends on it any more |
| SQL safety | Leading-keyword allowlist | The copy is the visitor's own, so this guards their session against a typo rather than protecting data |
| Row caps | 100 (lookup), 1000 (SQL) | Keeps DOM render sizes reasonable |
| Routing | SvelteKit file routes, prerendered | Each dataset gets a real HTML file with its own title |
| Styling | Tailwind, with tier colours as CSS variables | Tier classes are chosen at runtime, which no utility generator can see |

### Considered and not taken

- **Reading the file over HTTP range requests** (`sql.js-httpvfs`), which this
  site did until it was measured. Two costs killed it. Reads are serial —
  the worker uses synchronous XHR — so a name search that touched 390 pages
  waited 17 seconds to move 608 KB, and roughly one request per result row is a
  floor no page size removes. And the first visitor after each deploy waited
  ~26 s for the CDN to fill its cache with a 288 MB object. The download pays
  once, up front, visibly.
- **`sqlite-wasm-http`.** Built on the official SQLite WASM and maintained,
  but it sizes the file from a HEAD request's `Content-Length` and exposes no
  option to override it, so on a host that gzips it would silently use the
  compressed size. Same class of problem, less recourse.
- **DuckDB-WASM over Parquet.** Genuinely maintained, async, parallel range
  requests. Its binaries are 32–37 MB before the Parquet extension, which is
  more than the entire database download for a phone looking up one score.
- **Static pre-generated shards**, one file per exam-number bucket. The most
  robust option and the fastest single lookup, but it cannot answer arbitrary
  SQL, and a static file cannot stop early the way `LIMIT` does — a common
  Vietnamese surname would mean fetching a very large posting list.

## Risks and limitations

- **Memory is the binding constraint.** The database lives in the tab's
  WebAssembly memory for as long as the page is open: 142 MB for 2016, 119 MB
  for 2017. A low-memory phone may have the tab killed, which is why the gate
  states the figure before the download starts.
- **A deploy costs returning visitors the transfer again.** Every rebuild lays
  SQLite pages out differently, so the file — and its ETag — changes even when
  the data does not. The stored copy is then a stale version and is replaced.
- **The stored copy can be evicted.** Cache Storage is subject to the browser's
  own storage pressure, so a device short on disk falls back to downloading.
- **A visitor who will not download cannot use the site.** That is the
  deliberate shape of the gate, and it makes the first impression a 31 MB ask.
- **Hosted size.** 526 MB for both datasets against the 1 GB GitHub Pages
  limit; a third dataset of this size would not fit.
- **Excel format drift.** A new source file with an unseen header layout needs a
  new branch in `parser/internal/ingest/detect2016.go` or a new config.