Data model

Deep reference: CLAUDE.md → “Data model”

Everything lives in one SQLite file, DATA_DIR/site.db (./data-local/site.db in development), plus the uploaded bytes under DATA_DIR/uploads/{images,documents}/. The schema is the sum of src/db/migrations/*.sql, applied in filename order at startup; schema_migrations records what ran, with a checksum of each file’s text (migration 0020, review D-02 — a changed applied file is a warning at boot, and in production a refusal to start). Open the file with any SQLite client to explore it — the migrations are commented and are the authoritative description of every column.

Connection settings (src/db/index.js): journal_mode = WAL, foreign_keys = ON, busy_timeout = 5000.

Tables

users ─┬─< sessions (by sid, not FK)                    settings (key/value)
       └─< audit_log.user_id (SET NULL)                 schema_migrations

pages ─┬─< documents ──> files          (RESTRICT / RESTRICT)
       ├─< page_files ──> files         (CASCADE / RESTRICT)
       ├─< news ─┬─< news_pages >── pages       (post also shown on other pages)
       │         ├─< news_authors >── authors ──> files (photo, SET NULL)
       │         └──> files (cover, SET NULL)
       ├─< albums ─< gallery ──> files  (CASCADE / CASCADE; albums.cover_file_id SET NULL)
       ├─< staff ──> files (photo, SET NULL)
       ├─< menu_items (page_id CASCADE; parent_id self-FK CASCADE)
       ├──> library_groups (page_group, ON UPDATE CASCADE / ON DELETE RESTRICT)
       └──> files (header_file_id, SET NULL)

transparency (item_key → a key of src/config/transparency.js; ref_id → pages.id or
              documents.id, ref_key → library_groups.key — no FK, dangling reads as missing)

search_index (FTS5, maintained by triggers on pages, news, documents, page_files)
media_search (FTS5, maintained by triggers on files)
revisions (entity 'page'|'news' + entity_id, no FK; created_by ──> users SET NULL)

Table	What a row is
users	Admin-panel account: email (unique, NOCASE), argon2 password_hash, role (admin/editor), is_active, last_login_at
sessions	express-session store rows (src/admin/sessionStore.js); purged when expired
files	Every upload: kind (image/document), stored_name (<ulid>.<ext>), original name, MIME (PDF, an office type from config/documentTypes.js, or image/webp), size, dimensions, alt_text (≤ 200, images; 0026); deleted_at/deleted_by for the bin
pages	Every page: slug (unique), title, type (info, posts, gallery, people, library, system), status (draft/published), placement (nav, or menu = a document-library section), page_group (library column), body_md, description, header_file_id, is_active, sort_order, deleted_at/deleted_by. Since 0019 type and status have no CHECK: src/config/pageTypes.js (PAGE_TYPES, PAGE_STATUSES, assertPageTypeAndStatus) is the one list, checked by every write path
documents	A document (PDF or office file) in a library section: page_id (a placement = 'menu' page), file_id, title, published_on
page_files	A page’s own attachment (PDF or image) from the editor’s «Файли» tab: title, published_on, sort_order
news	A post of a feed: page_id (a posts page), slug (globally unique), category (free-text rubric ≤ 60), status (draft, pending, published, archived), published_on, cover_file_id, body_md, deleted_at/deleted_by, publish_at (0025: «Опублікувати о …», Kyiv YYYY-MM-DDTHH:MM; a draft with a future publish_at is shown as «Заплановано», see below)
news_pages	Extra pages a post is also shown on («Показувати також на сторінках»)
authors	Byline catalogue: name, signature, bio, photo_file_id, name_display (full/short), show_photo, is_active, consent_at/consent_by (parents’ written consent; without it only the short name, no photo, no author page)
news_authors	Post ↔ author with role (text/photo) and order
albums	Album of a gallery page: slug unique within the page, title, event_date, cover_file_id, is_active, deleted_at/deleted_by
gallery	A photo in an album: file_id, album_id, caption, sort_order
staff	A person card on a people page: page_id, full_name, position, qualification, experience_years, bio, photo_file_id, is_active, deleted_at/deleted_by. category is legacy and unread
menu_items	Main navigation, max two levels: kind (page, dropdown, link, section), label (empty = page title), url, open_in_new, is_visible, is_cta (top level only), sort_order
library_groups	A column of the document library (migration 0022, CMS story 08): key ([a-z0-9-], 2–40, never changes — ?group= links use it), title (1–60), sort_order. Seeded with zaklad, osvitniy-proces, zvitnist; pages.page_group is a foreign key into it, so a group that holds a section (recycle bin included) cannot be deleted
transparency	The school’s answer for one item of the article 30 checklist (migration 0023): item_key, kind (page/document/group/na), ref_id or ref_key, note (≤ 300), updated_at/updated_by. The status (є / застаріло / немає / не стосується) is never stored — src/lib/transparency.js derives it on every read
settings	Key/value (see below)
audit_log	«Журнал змін»: who (user_id + denormalised user_name), action, entity, entity_id, summary, ip; purged after 12 months
search_index	FTS5 index for /poshuk (see below)
revisions	A state of a page or a post that an edit replaced (0024, epic A9): entity (page/news), entity_id, snapshot (JSON of the edited fields — src/lib/revisions.js), created_at, created_by, reason (save, restore, publish); newest 50 per entity (see below)

Dates of the content (published_on, event_date) are ISO YYYY-MM-DD strings, formatted DD.MM.YYYY at render time (src/lib/dates.js). Timestamps are ISO-8601 UTC strings.

Indexes beyond the primary keys and UNIQUE constraints: every column that points at files has one (idx_gallery_file, idx_documents_file, idx_page_files_file, idx_albums_cover, and the partial idx_news_cover, idx_staff_photo, idx_pages_header, idx_authors_photo — migration 0021, review D-04), so «who uses file N» and DELETE FROM files are index lookups; idx_news_status / idx_news_deleted serve the admin counters and the bin. The public read paths are covered by idx_news_public, idx_news_rubric, idx_news_pages_page, idx_news_authors_author, idx_documents_page, idx_page_files_published, idx_albums_*, idx_gallery_album, idx_staff_page and idx_menu_items_parent. idx_pages_group (0022) serves the library_groups foreign key and the library’s per-group lists.

The library’s column titles were settings rows (library.group.<key>) until migration 0022 moved them into library_groups and deleted the rows.

Revisions and scheduled posts (epic A9)

• Revisions (revisions, migration 0024). src/lib/revisions.js owns the field list per entity (a page: title, description, body_md, status, header_file_id; a post: title, slug, summary, category, body_md, cover_file_id, published_on and the author ids by role), the serialisation and the comparison. The stores write them — withRevision() in src/admin/stores/pages.js / news.js snapshots the row, applies the update and stores the old state in the same transaction, only when the update changed a field and the old state is not already the newest revision (src/admin/stores/revisions.js record()). A new write path that changes one of those fields goes through withRevision(), or the history misses it. After each insert the rows beyond the newest 50 of that entity are deleted. There is no foreign key (two parent tables): purgeItem() in src/admin/lib/trash.js deletes a purged page’s revisions, those of its posts and a purged post’s, and purgeTrash() sweeps any orphans. «Відновити» first stores the current state as a restore revision, then applies the chosen one through the same update — status is never restored. Replacing a file in place (FileStorage.replace(), issue #265) rewrites its old stored name inside every snapshot as well (rewriteTextReferences() in src/lib/fileUsages.js), so a restored text links to the live file, not to the old bytes in the bin.
• Scheduled posts (news.publish_at, migration 0025). No new status: a post is scheduled while status = 'draft' AND publish_at > now (Kyiv minute, nowIso() in src/lib/dates.js); SCHEDULED_SQL / effectiveStatus() in src/admin/stores/news.js are the one derivation the list, its chips and the dashboard use. Any status change and a soft delete clear publish_at. src/admin/lib/publishScheduled.js publishes every due post (published_on = the day of publish_at) in one transaction, with a publish revision and an audit_log row whose user_name is «система (за розкладом)»; src/admin/index.js runs it at boot and every 60 s through safeInterval.

Soft delete and the recycle bin

Two separate mechanisms:

• Hidden — is_active = 0 on pages, albums, staff, users, authors: the row is simply not shown (for users: cannot log in). Reversible at any time.
• Recycled — deleted_at + deleted_by on pages, news, albums, staff, files: the item is in «Кошик» for 30 days. Restoring is one UPDATE clearing both columns, so text, attachments, menu placement and status come back untouched (a page’s delete also drafts it). src/admin/lib/trash.js owns restore and what “permanent” means per kind; src/admin/lib/purgeTrash.js purges expired rows at boot and every 24 h.

Every read must filter deleted_at IS NULL on these tables — files included: every public statement that joins a file carries f.deleted_at IS NULL (review A-22), so a file in the bin renders as «no photo», the same as a missing row. Real row deletions are rare (the purge, a photo, a page attachment, a menu item, an author without posts), which is why the ON DELETE CASCADE clauses on pages children almost never fire.

Settings

settings(key, value, updated_at) holds everything a school configures that is not a row of its own. Reads go through typed helpers, never raw strings in routes:

Keys	Owner / reader
site.name, site.shortName, logo, directorPIB, contact.*, social.*, topbar.*, footer.*, announcement.*, posts.reviewRequired	src/config/siteSettings.js (FLAG_DEFAULTS for booleans)
home.blocks (JSON), heroTitle, heroTagline, heroPhoto, hero.*	src/config/homeBlocks.js
theme.primary, theme.accent, theme.font, favicon	src/config/theme.js
backup.enabled, backup.time, backup.keep, backup.lastRun (JSON), backup.lastUpload (JSON), backup.lastDrillAt	src/lib/backupSettings.js
mainPageText, pronasText, directorText, directorPhoto, adminBanner, kafedryBanner	Legacy fallbacks from before pages had their own text; read only when the page field is empty

An absent or empty key renders nothing — there are no built-in texts. Image keys (logo, favicon, heroPhoto, …) hold a files.id without a real foreign key, which is why src/lib/fileUsages.js checks them by hand; every reader goes through readFileIdSetting(raw, key) (src/config/siteSettings.js), which turns '', NaN and anything else that is not a positive whole number into null.

installation.origin may still exist in the first installation’s database: the early releases’ one-off import of its old site wrote it (review D-12). That import has left the tree with the public repository (docs/dev/public-snapshot.md), nothing reads or writes the key any more, and no code may recognise a particular installation — a school’s own data lives in its own database.

SqliteContentRepository memoises getSettings(), getPages() and getMenu() (review D-09 / A-23). A cached value is used only while total_changes() of the connection and PRAGMA data_version are both unchanged — any write, on this connection or another, re-reads it — and never longer than 10 minutes; invalidate() (called after every admin write) drops it at once. Every call returns its own copy.

Files

A files row plus bytes at uploads/<kind>s/<stored_name>; images also get <name>.thumb.webp (and <name>.medium.webp when wider than 800 px). Everything is written by src/services/FileStorage.js:

• the type is sniffed from the content (file-type); PDFs up to 15 MB are copied as is;
• images up to 10 MB and 40 megapixels (review S-02: refused before any decode) are re-oriented, stripped of metadata, resized to at most 1600 px and re-encoded to WebP via sharp; the limits are src/config/limits.js and MAX_INPUT_PIXELS (src/lib/imageRenditions.js);
• bytes before row, row before bytes (review D-05): an upload writes every rendition under <name>.tmp, renames them into place, and only then inserts the row, so a row never exists without its bytes; remove() checks usage and deletes the row in one synchronous step, then unlinks. Whatever a crash leaves behind is bytes with no row — findOrphanBytes() lists them (renditions count as their full file, .tmp always counts), the media page shows the admin «Файлів без запису: N», and the nightly job (src/admin/lib/fileMaintenance.js, next to the bin purge) deletes the ones older than 30 days;
• remove() refuses while anything references the file. The single list of references — foreign keys, settings image keys, /uploads/... links inside any body_md or legacy text, and the header/cover ids and body links inside revision snapshots (epic A9, labelled «Попередня версія …») — is src/lib/fileUsages.js: fileUsages(db, id) for one file with labels, collectFileReferences(db) for the set of every used id in one pass (the media list, the sweep, the purge). The media library’s «Де використовується» is a view of the same list;
• the same nightly job moves unreferenced files older than 7 days to the bin (the «Очистити невикористані» sweep, review D-13).

An image inserted into Markdown through the editor’s «Фото» button is referenced by nothing but that link, which is why the text scan exists.

Search index

Migration 0017-search-index.sql: FTS5 table search_index(kind, ref_id, title, body) with tokenizer unicode61 remove_diacritics 2, kept in sync by AFTER INSERT/UPDATE/DELETE triggers:

Source	Indexed	kind code
pages	title; description + body_md	0
news	title; summary + body_md	1
documents	title	2
page_files	title	3

rowid = id * 4 + kind code, so every trigger deletes by rowid. The index holds every row; visibility (published, active, not recycled, on a public page) is decided at query time by joining back to the source table in SqliteContentRepository.search(). Bodies are raw Markdown, cleaned only for the snippet (src/lib/searchText.js, which also turns user input into quoted prefix tokens so no query can be an FTS5 syntax error).

A migration that rebuilds any of these four tables drops its triggers and must recreate them — see writing-a-migration.md.

Migration 0026-files-alt-text.sql (issue #265) adds a second, smaller FTS5 table for the media library’s search: media_search(name, alt), rowid = files.id, same tokenizer, kept by media_search_{ai,au,ad} triggers on files (insert, update of original_name/alt_text, delete). SQLite’s LIKE and lower() fold case for ASCII only; the tokenizer folds it for Cyrillic, which is what makes «Статут» find «статут.pdf». src/admin/stores/files.js ORs it with a plain LIKE so a substring inside a word is still found. A rebuild of files must recreate those three triggers too.

What a backup contains

src/services/backup.js — used by both the scheduler and scripts/backup.js, its command line — takes a consistent snapshot with SQLite’s online backup API, deletes the snapshot’s sessions rows (review S-11), and packs it with uploads/ and a manifest.json (row counts, file count) into a .tar.gz, written as <name>.part and renamed when complete. The skip-when-unchanged hash covers every table from sqlite_master except sessions, audit_log, schema_migrations, sqlite_*, virtual tables and their shadow tables — search_index is derived (contentTables() in src/lib/backupUtils.js, review D-07), so a new table is covered automatically. The archive itself contains the whole database, user accounts included — everything but live sessions.

scripts/restore.js verifies the archive (backup-verify.js) before touching anything, accepts it when every migration it has applied is one this installation knows — by name in the current database or the migration files, or by checksum under a new name — and says which side is newer, merges --keep-users accounts into the extracted copy before the swap, and re-checks the server lock right before DATA_DIR is emptied (review D-02 / D-06).