Data model — the SQLite schema

This file documents every table in the hub's SQLite database: the columns, what each one is for, the indexes and the relations between them. It also covers how data moves through the system, what is pruned and what is kept, and why SQLite is the right choice here.

The database

One SQLite file, opened with better-sqlite3 in lib/db.js.

const DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', 'data', 'swiftpoint-hub.db');
fs.mkdirSync(path.dirname(DB_PATH), { recursive: true });
const db = new Database(DB_PATH);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

Setting	Value	Why
DB_PATH	env, default ./data/swiftpoint-hub.db	One variable relocates the whole database.
parent directory	mkdirSync(…, { recursive: true }) on load	A fresh clone does not need a data/ directory to exist first.
journal_mode	WAL	Readers do not block the writer, which matters because a page render is a read and an upload is a write.
foreign_keys	ON	SQLite defaults this off, per connection. Without it every ON DELETE clause in this file would be a comment.

Every migration is idempotent. A table is created with CREATE TABLE IF NOT EXISTS; a column added later is guarded by PRAGMA table_info(<table>) plus a conditional ALTER TABLE:

const userCols = db.prepare('PRAGMA table_info(users)').all().map((c) => c.name);
if (!userCols.includes('bio')) {
  db.exec("ALTER TABLE users ADD COLUMN bio TEXT NOT NULL DEFAULT ''");
}

The same shape is used for profiles.updated_at, profiles.game, the four profile counters, comments.kind, the four updates fields and the two notifications pointers. Booting an old database runs every one of these and changes nothing.

Backing up the site is copying that one file. With WAL, copy the -wal and -shm files too, or take the copy after a clean shutdown. There is no second service to snapshot, no dump command and no credential to rotate.

There are seventeen tables. Sixteen are created by lib/db.js; sessions is created by lib/session-store.js so that module is self-contained.

users

One row per Discord account that has ever signed in. Created by the OAuth callback; see Auth and roles.

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT	internal id, referenced everywhere	The site's own identifier for a person
discord_id	TEXT UNIQUE	nullable	The Discord snowflake; the only column that is never published
username	TEXT NOT NULL	global_name or username, else Discord user	Display name, refreshed on every sign-in
avatar_url	TEXT	nullable	https://cdn.discordapp.com/avatars/<id>/<hash>.png?size=64
created_at	TEXT NOT NULL	datetime('now')	The day they joined
bio	TEXT NOT NULL	'' — added by migration	The free text on /users/<id>

Notes. discord_id is UNIQUE, which is what makes INSERT … ON CONFLICT(discord_id) the upsert. It is nullable so the table can exist before Discord login does — the comment in lib/db.js says exactly that: "users is here from day one so Discord login can be bolted on later without a schema migration: profiles.user_id just starts getting filled in". bio was added later by the PRAGMA table_info migration; users has no explicit index beyond the UNIQUE on discord_id. A user is never deleted directly: every reference to it is ON DELETE SET NULL or ON DELETE CASCADE, so the rows either lose the pointer or go with the account.

profiles

An uploaded .spcf. The central table.

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT		The profile id in /profiles/<id>
title	TEXT NOT NULL	cleaned, max 80	Display name
author_name	TEXT NOT NULL	cleaned, max 60, fallback Anonym	Who wrote it, as typed
description	TEXT	nullable, max 400	Free text
source_filename	TEXT	the original upload name, max 255	Provenance
format_header	TEXT	first line of the file, trimmed	Half of the byte-exact download
version	INTEGER	nullable	version from the document
tags	TEXT NOT NULL	'', comma-separated group names	Filter chips and search
total_buttons	INTEGER NOT NULL	0	Rollup: totalButtons
min_dpi	INTEGER	nullable	Rollup
max_dpi	INTEGER	nullable	Rollup
raw_json	TEXT NOT NULL	everything after the first newline	The original document, byte-exact
parsed_summary	TEXT NOT NULL	JSON.stringify(parsed.groups)	The derived display structure
user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL	Uploader
uploaded_by_admin	INTEGER NOT NULL	0	Distinguishes a staff upload
created_at	TEXT NOT NULL	datetime('now')
updated_at	TEXT	nullable — added by migration	Set on edit and on replace
game	TEXT	nullable — added by migration	Canonicalised game name
views	INTEGER NOT NULL	0 — added by migration	Page-view counter
downloads	INTEGER NOT NULL	0 — added by migration	Download counter
upvotes	INTEGER NOT NULL	0 — added by migration	Recounted from votes
downvotes	INTEGER NOT NULL	0 — added by migration	Recounted from votes

Notes. The two JSON columns are the point of the table. raw_json is the original parsed document, kept so a download can be byte-identical to the upload; parsed_summary is this project's own derived structure — the groups, bindings and rollups the UI renders — kept so a profile page does not re-parse a document on every request. Because both are kept, the two can never disagree about what was uploaded, and the download is always the original rather than a reconstruction of the derived form.

tags is a comma-separated string of group names, derived on upload (withGameTag()), not a user-typed list. Tag filtering therefore has to match with ',' || tags || ',' LIKE '%,x,%' so that FPS cannot match FPSMAX. The game name is one of the tags, deduplicated by the same comparison key the taxonomy uses.

Indexes: idx_profiles_created_at(created_at) for the default newest-first gallery, and idx_profiles_tags(tags) for the tag filter. A profile delete cascades to its comments and votes.

comments

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
profile_id	INTEGER NOT NULL REFERENCES profiles(id)	ON DELETE CASCADE	The profile it hangs off
user_id	INTEGER NOT NULL REFERENCES users(id)	ON DELETE CASCADE	The author
body	TEXT NOT NULL	cleaned, max 1000	The text
kind	TEXT NOT NULL	'comment' — added by migration	'comment' or 'patch'
created_at	TEXT NOT NULL	datetime('now')

Notes. kind arrived after the table did, guarded by PRAGMA table_info(comments); the default is 'comment', so existing rows needed no backfill. 'patch' marks an author patch note, which only the profile's uploader may post (Only the profile author can post patch notes.) and which the API reports separately as isAuthor. Index: idx_comments_profile(profile_id, created_at), which is exactly the list query's ordering.

notifications

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
user_id	INTEGER NOT NULL REFERENCES users(id)	ON DELETE CASCADE	Recipient
type	TEXT NOT NULL	new_user, new_profile, profile_updated, new_comment, new_vote, report, new_ticket, forum_reply, ticket_reply, ticket_status, new_update	What happened
actor_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL	Who did it
actor_name	TEXT	nullable	Their name at the time, kept for the text
profile_id	INTEGER REFERENCES profiles(id)	ON DELETE CASCADE	Where it happened, profile surfaces
comment_id	INTEGER REFERENCES comments(id)	ON DELETE CASCADE	The comment, where there is one
text	TEXT NOT NULL		The line the bell shows
read	INTEGER NOT NULL	0	Read flag
created_at	TEXT NOT NULL	datetime('now')
thread_id	INTEGER REFERENCES forum_threads(id)	ON DELETE CASCADE — added by migration	Forum surfaces
post_id	INTEGER REFERENCES forum_posts(id)	ON DELETE CASCADE — added by migration	Forum surfaces

Notes. thread_id and post_id were added when the forum arrived, and the source comment explains why they are not in the CREATE TABLE above:

SQLite resolves a REFERENCES clause against a table that already exists, and the notification table is defined before the forum is.

Indexes: idx_notifications_user(user_id, read) for the unread badge, and idx_notifications_created(user_id, created_at) for the list, which is capped at 50 rows.

reports

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
target_type	TEXT NOT NULL	documented as profile or comment	Which kind of thing was reported
target_id	INTEGER NOT NULL		The reported row's id
reporter_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL	Who reported it
reason	TEXT NOT NULL	cleaned, max 500	Why
status	TEXT NOT NULL	'open'	'open', 'resolved', 'dismissed'
created_at	TEXT NOT NULL	datetime('now')

Notes. The column comment says 'profile' | 'comment', and that is what the schema was written for — but routes/reports.js sets TARGET_TYPES = new Set(['profile', 'comment', 'post']) and the thread page posts post. A reported post is stored with target_type = 'post' and resolved by joining forum_posts to forum_threads, because it has no profile to point at. The reported item is not a foreign key, deliberately: target_id means a different thing per target_type, and one polymorphic reference cannot be constrained by a single REFERENCES clause.

The duplicate rule is enforced in application code, not by a unique index: one open report per user per target, answered with 409 and You already reported this. A resolved report does not block a second one.

Indexes: idx_reports_status(status, created_at) for the queue and idx_reports_target(target_type, target_id) for "has this been reported".

pageviews

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
visitor_hash	TEXT NOT NULL	HMAC-SHA256 of the client IP, never raw	Counting unique visitors
path	TEXT NOT NULL	truncated to 255	Which page
created_at	TEXT NOT NULL	datetime('now')	The window queries

Notes. One insert per page view and nothing else — no events, no click tracking, no profile of a person. The hash key is SESSION_SECRET (or the development fallback swiftpoint-hub-analytics-salt), so the same IP hashes to the same value within this install and to nothing usable anywhere else. See SEO and analytics and Security and privacy.

Indexes: idx_pageviews_created(created_at) for the h1 / h24 / d7 / d30 windows and for the 30-day prune, and idx_pageviews_visitor(visitor_hash, created_at) for COUNT(DISTINCT visitor_hash) inside a window.

votes

Column	Type	Default / notes	What it is for
profile_id	INTEGER NOT NULL REFERENCES profiles(id)	ON DELETE CASCADE
user_id	INTEGER NOT NULL REFERENCES users(id)	ON DELETE CASCADE
value	INTEGER NOT NULL	1 or -1	Up or down
created_at	TEXT NOT NULL	datetime('now')
—	PRIMARY KEY (profile_id, user_id)	composite	One vote per user per profile

Notes. The composite primary key is the whole design: a vote is an upsert, so voting again replaces the value and there is no way for a user to hold two votes on one profile. value = 0 is accepted by the route and simply removes the vote.

The counters on profiles are recounted, never incremented:

UPDATE profiles SET
  upvotes   = (SELECT COUNT(*) FROM votes WHERE profile_id = ? AND value = 1),
  downvotes = (SELECT COUNT(*) FROM votes WHERE profile_id = ? AND value = -1)
WHERE id = ?

so the two can never drift from the rows that justify them. A vote on your own profile is refused with You can't vote on your own profile. Index: idx_votes_profile(profile_id) serves the recount and the per-profile total.

settings

Column	Type	Default / notes	What it is for
key	TEXT PRIMARY KEY		e.g. max_profiles_per_user
value	TEXT NOT NULL	always a string
updated_at	TEXT NOT NULL	datetime('now')

Notes. The whole module is two functions, lib/settings.js: getSetting(key, fallback) and setSetting(key, value), the second an upsert. Everything is stored as text, so a caller coerces; the one setting that matters for limits is max_profiles_per_user, read as Math.max(1, Number(getSetting('max_profiles_per_user', MAX_PROFILES_PER_USER)) || MAX_PROFILES_PER_USER). The PRIMARY KEY on key is the index.

forum_categories

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
slug	TEXT NOT NULL UNIQUE		The URL segment
name	TEXT NOT NULL		The heading
description	TEXT NOT NULL	''	Shown under the name
kind	TEXT NOT NULL	'discussion'	'discussion' or 'support'
sort	INTEGER NOT NULL	0	Display order
created_at	TEXT NOT NULL	datetime('now')

Notes. kind is what makes the ticket system possible without a second set of tables: a category marked 'support' turns every thread in it into a ticket, and a 'discussion' category carries NULL in the thread's status columns, so "is this a ticket" is answered by the category rather than by a flag somebody can set and forget. A category added later by a moderator can be either kind. slug is UNIQUE, which is what the seed's ON CONFLICT(slug) DO NOTHING needs.

forum_threads

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
category_id	INTEGER NOT NULL REFERENCES forum_categories(id)	ON DELETE CASCADE
author_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL	Opener
title	TEXT NOT NULL	cleaned, max 120
pinned	INTEGER NOT NULL	0
locked	INTEGER NOT NULL	0
views	INTEGER NOT NULL	0	Counted for signed-in readers only
created_at	TEXT NOT NULL	datetime('now')
updated_at	TEXT NOT NULL	datetime('now')
last_post_at	TEXT NOT NULL	datetime('now')	Denormalised, for the activity sort
last_post_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL	Who to attribute the bump to
status	TEXT	NULL for a non-ticket	'open', 'in_progress', 'on_hold', 'closed'
status_note	TEXT NOT NULL	''	Why
status_changed_at	TEXT	nullable
status_changed_by	INTEGER REFERENCES users(id)	ON DELETE SET NULL	Who moved it

Notes. status IS NULL is the single field that makes a ticket a ticket, and lib/forum.js refuses a status change on a discussion with That is a discussion, not a ticket — it has no status. The four status columns are denormalised from the trail for the list queries; forum_ticket_events is the record of how they got there. last_post_at and last_post_user_id are maintained on every reply so the activity sort does not have to aggregate the posts table.

Indexes: idx_forum_threads_category(category_id, pinned DESC, last_post_at DESC) for a category listing, idx_forum_threads_last(last_post_at DESC) for the activity feed, idx_forum_threads_status(status, last_post_at DESC) for the ticket queue.

forum_posts

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
thread_id	INTEGER NOT NULL REFERENCES forum_threads(id)	ON DELETE CASCADE
author_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL
body	TEXT NOT NULL	markdown, cleaned, max 8000
edited_at	TEXT	nullable	Set by POST /posts/:id/edit
deleted	INTEGER NOT NULL	0	Soft delete
created_at	TEXT NOT NULL	datetime('now')

Notes. deleted is a soft delete: the row is kept, body becomes (deleted) and the flag is set, so replies keep their order and the fact that something was there survives. Posts are never hard-deleted, which is why the forum's post counts are computed with deleted = 0. The thread's first post cannot be soft-deleted even by a moderator; the answer is to delete the thread, and the refusal says so.

Indexes: idx_forum_posts_thread(thread_id, created_at) for a thread page, idx_forum_posts_author(author_user_id, created_at DESC) for "your posts".

forum_ticket_events

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
thread_id	INTEGER NOT NULL REFERENCES forum_threads(id)	ON DELETE CASCADE
actor_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL	Who moved it
from_status	TEXT	nullable	NULL when a ticket starts as a ticket
to_status	TEXT NOT NULL		Where it went
note	TEXT NOT NULL	''	Why, cleaned
created_at	TEXT NOT NULL	datetime('now')

Notes. The audit trail: who moved a ticket, when, from what to what, and why. A support system without this is a status field people argue about in the thread. to_status is NOT NULL because "what is the ticket now" always has an answer, while from_status is nullable because the first entry has no previous state. Index: idx_forum_ticket_events_thread(thread_id, created_at) for the trail on the thread page.

forum_subscriptions

Column	Type	Default / notes	What it is for
thread_id	INTEGER NOT NULL REFERENCES forum_threads(id)	ON DELETE CASCADE
user_id	INTEGER NOT NULL REFERENCES users(id)	ON DELETE CASCADE
created_at	TEXT NOT NULL	datetime('now')
—	PRIMARY KEY (thread_id, user_id)	composite	One subscription per person per thread

Notes. "Tell me when this changes." The person who opened a ticket is in it from the start, because opening one subscribes the author; every reply also does INSERT OR IGNORE, so replying is subscribing. A moderator can add anybody else. There is no index beyond the composite primary key, because every query asks by (thread_id) and user_id — the watchers of one thread.

updates

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
kind	TEXT NOT NULL	'website'	'website' or 'mouse'
title	TEXT NOT NULL
body	TEXT NOT NULL	'', markdown	The full entry
author_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL
pinned	INTEGER NOT NULL	0
created_at	TEXT NOT NULL	datetime('now')
updated_at	TEXT NOT NULL	datetime('now')
version	TEXT NOT NULL	'' — added by migration	The label, e.g. Firmware 2.1
summary	TEXT NOT NULL	'' — added by migration	One line, written for the list
accent	TEXT NOT NULL	'lime' — added by migration	The colour of the whole entry
tags	TEXT NOT NULL	'[]' — added by migration	JSON array string of tool tags

Notes. Two kinds because they are told to different people: a website update is irrelevant to somebody whose problem is the OLED, and vice versa. The four migration-era columns exist because an entry that is only a title and a wall of text cannot be scanned, and the whole point of the page is that somebody can read thirty of them in a minute and work out whether they have to care about this one. summary is what the front page shows, accent gives a mouse entry and a site entry a different colour, and tags is a JSON array string — hence TEXT NOT NULL DEFAULT '[]' rather than a real array. They were added as columns rather than as a second table: an update is one row with one body, and four nullable fields beat a key/value table anybody has to join to read a changelog.

Index: idx_updates_created(pinned DESC, created_at DESC), which is the whole list query.

staff

Column	Type	Default / notes	What it is for
discord_id	TEXT PRIMARY KEY		The Discord snowflake, keyed directly
username	TEXT NOT NULL	'', truncated to 64	For the panel's list
role	TEXT NOT NULL	'moderator'	'moderator' or 'admin'
added_at	TEXT NOT NULL	datetime('now')
added_by	TEXT NOT NULL	''	Who added them

Notes. The table keys on discord_id rather than on a users.id foreign key, because the point of it is to be able to add somebody who has never signed in. It is merged with the .env allowlists at read time by lib/admin.js, with the environment as the stronger claim. There is no index beyond the primary key: the whole table is read on every cache miss. See Auth and roles.

recordings

Column	Type	Default / notes	What it is for
id	INTEGER PK AUTOINCREMENT
slug	TEXT NOT NULL UNIQUE		The guide reference recordings/<slug>.rec.json
title	TEXT NOT NULL
description	TEXT NOT NULL	''
data	TEXT NOT NULL	the whole .rec.json document as text	Step actions and per-step screenshots
step_count	INTEGER NOT NULL	0	Denormalised for the list
published	INTEGER NOT NULL	0	1 when written into docs/recordings/
owner_user_id	INTEGER REFERENCES users(id)	ON DELETE SET NULL
created_at	TEXT NOT NULL	datetime('now')
updated_at	TEXT NOT NULL	datetime('now')

Notes. data is the whole document rather than a set of columns, because a recording is a list of steps whose payloads are per-step screenshots as data URLs — a shape a relational schema would only obscure. step_count is denormalised so the admin list does not have to parse every document. Guides read recordings through a public path in routes/docs.js — the repository file first, this table as a fallback — never through /api/records, which is admin-only. Index: idx_recordings_created(created_at); slug is UNIQUE.

sessions

Created by lib/session-store.js, not by lib/db.js.

Column	Type	Default / notes	What it is for
sid	TEXT PRIMARY KEY	the cookie id
data	TEXT NOT NULL	JSON.stringify(session)	The whole session, including the cookie's own expires
expires_at	INTEGER NOT NULL	epoch milliseconds	When the row stops being valid

Notes. The cookie's expires lives inside data, which is why touch rewrites both columns and why _expiryFor() prefers the cookie's own expiry over the store TTL. get() reports an expired row as missing and deletes it on the way out. Index: idx_sessions_expires(expires_at), which is the only one — every statement filters or orders on it. Full detail in Auth and roles.

The indexes

Index	Columns	The query it serves
idx_profiles_created_at	profiles(created_at)	the gallery's default newest-first order
idx_profiles_tags	profiles(tags)	the gallery's tag filter
idx_comments_profile	comments(profile_id, created_at)	a profile's comments, oldest first
idx_notifications_user	notifications(user_id, read)	the unread-count badge
idx_notifications_created	notifications(user_id, created_at)	the 50-row notification list
idx_reports_status	reports(status, created_at)	the report queue, open first then newest
idx_reports_target	reports(target_type, target_id)	whether something has been reported
idx_pageviews_created	pageviews(created_at)	the h1 / h24 / d7 / d30 windows and the 30-day prune
idx_pageviews_visitor	pageviews(visitor_hash, created_at)	COUNT(DISTINCT visitor_hash) within a window
idx_votes_profile	votes(profile_id)	the up/down recount on profiles
idx_forum_threads_category	forum_threads(category_id, pinned DESC, last_post_at DESC)	one category's thread list
idx_forum_threads_last	forum_threads(last_post_at DESC)	the forum's activity feed
idx_forum_threads_status	forum_threads(status, last_post_at DESC)	the moderator's ticket queue
idx_forum_posts_thread	forum_posts(thread_id, created_at)	a thread page window
idx_forum_posts_author	forum_posts(author_user_id, created_at DESC)	a user's own posts
idx_forum_ticket_events_thread	forum_ticket_events(thread_id, created_at)	the ticket trail
idx_updates_created	updates(pinned DESC, created_at DESC)	the updates list
idx_recordings_created	recordings(created_at)	the admin recordings list
idx_sessions_expires	sessions(expires_at)	the sweep and the live-session count

Indexes that exist because of a constraint rather than a CREATE INDEX: users.discord_id (UNIQUE), forum_categories.slug (UNIQUE), recordings.slug (UNIQUE), settings.key, staff.discord_id, votes(profile_id, user_id) and forum_subscriptions(thread_id, user_id) (both composite primary keys), and sessions.sid (primary key).

The seeded forum categories

DEFAULT_CATEGORIES in lib/db.js, inserted with ON CONFLICT(slug) DO NOTHING inside a transaction, so a fresh install has a usable forum on the first request and an install that already has categories keeps exactly the ones it has.

slug	name	kind	sort	description
general	General	discussion	10	The Z3 itself, this hub, and anything that does not fit anywhere else.
profiles	Profiles & bindings	discussion	20	Share a .spcf, ask about DPI steps, deep-click, tilt and OLED setups.
help	Support	support	30	Stuck on something? Open a ticket here and a moderator picks it up and keeps you posted.
bugs	Bugs	support	40	Something on the site or in the mouse behaves wrongly. Tickets here carry a status.
off-topic	Off-topic	discussion	50	Everything else.

The kind is the part that matters: the two support categories turn every thread in them into a ticket, and the three discussion categories never carry a status. The seed is idempotent, so re-running it after a moderator renamed a description does not put the old text back.

How data flows through the system

An upload. POST /api/profiles with a multipart file:

multer (memory storage, 2 MB cap) → cleanField on title / author / description / game
  → per-user cap check (429 for a non-admin over the limit)
  → parseSpcf(text): strip BOM, take the header line, test ^json([,\s]|$),
                     refuse a NUL byte (binary), JSON.parse, validateRoot()
  → raw_json       = text.slice(firstNewline + 1)      — everything after the first newline
  → format_header  = parsed.formatHeader
  → parsed_summary = JSON.stringify(parsed.groups)
  → rollups: total_buttons, min_dpi, max_dpi
  → tags = withGameTag(parsed.rollup.tags, game)       — comma-separated
  → INSERT INTO profiles, one row
  → notifyAdmins({ type: 'new_profile', … })

A download. GET /api/profiles/:id/download:

UPDATE profiles SET downloads = downloads + 1 WHERE id = ?
body = format_header + "\n" + raw_json
Content-Type: application/octet-stream
Content-Disposition: attachment; filename="<title, [a-z0-9_-] only>.spcf"

The download is a concatenation of two stored columns, not a re-serialisation, which is what makes it byte-identical to the upload.

A vote. POST /api/profiles/:id/vote:

refuse a value outside {1, -1, 0}; refuse a vote on your own profile
INSERT INTO votes (profile_id, user_id, value) … ON CONFLICT DO UPDATE   -- an upsert
UPDATE profiles SET upvotes = COUNT(votes WHERE value = 1),
                     downvotes = COUNT(votes WHERE value = -1)          -- a recount, not +1
notifyAdmins({ type: 'new_vote' }) and notifyUser(the owner), unless the owner is an admin

A forum reply. POST /api/forum/threads/:id/posts, in one transaction:

INSERT INTO forum_posts (thread_id, author_user_id, body)
UPDATE forum_threads SET last_post_at = datetime('now'), last_post_user_id = ?, updated_at = datetime('now')
INSERT OR IGNORE INTO forum_subscriptions (thread_id, user_id)

then, after the transaction, notifyThreadAudience(): every watcher except the person who just wrote this, plus the thread's author if they are not already on that list and are not the actor. The author is removed from the watcher list rather than notified twice, because a support desk that sends two bells for one answer trains people to ignore the bell. A new thread in a support category additionally notifies the moderators (new_ticket), because a ticket nobody is told about is not a ticket.

A ticket status change. POST /api/forum/threads/:id/status, requireModerator, also in one transaction:

INSERT INTO forum_ticket_events (thread_id, actor_user_id, from_status, to_status, note, created_at)
UPDATE forum_threads SET status = ?, status_note = ?, status_changed_at = ?, status_changed_by = ?

The trail row goes in before the thread update and both are in the same transaction, so if the write of the trail fails the status does not move. Then the watchers and the author are notified with the new status and the note.

A page view. On a server-rendered page, outside any guard:

INSERT INTO pageviews (visitor_hash, path)
    visitor_hash = HMAC-SHA256(req.ip, SESSION_SECRET || 'swiftpoint-hub-analytics-salt')
    path         = String(req.path).slice(0, 255)
every 200th insert: DELETE FROM pageviews WHERE created_at < datetime('now', '-30 days')

Page views from an admin session are skipped on the static and tool pages, and on a profile page the admin's view is excluded from the views counter as well — that counter answers "how many readers has this profile had", and an operator checking their own work is not a reader. The gallery and the user page count everybody.

Retention

What	When it goes	Where
pageviews rows older than 30 days	on every 200th insert	lib/analytics.js
sessions rows past expires_at	in the store's constructor, then every 100 writes	lib/session-store.js
everything else — accounts, profiles, comments, votes, threads, posts, reports, updates, recordings, notifications, staff rows	kept until somebody asks	—

Both prunes ride along with writes rather than a timer, because a timer would keep the process alive for no reason. Both are approximate by design, and the session one is backstopped at read time: an expired session reads as missing even before the sweep reaches it.

Nothing else is deleted automatically. Deleting a profile cascades to its comments, votes and notifications; deleting a user cascades to their comments, votes, subscriptions and notifications, and nulls the authorship pointers on threads, posts and profiles, so nothing is left dangling and nothing is silently rewritten. The privacy page's retention claim is generated from FACTS.retention in lib/legal.js, which is taken from these two prune points, and test/legal-pages.test.js fails when the page and the code disagree — see Security and privacy.

Why SQLite here

One file. No separate database to install, run, back up, secure, or explain to whoever takes the site over. The backup is cp. That is most of the argument.

The workload fits. A page render is a handful of small indexed reads plus a page-view insert; a write is an upload, a post, a vote or a status change — a few hundred a day on a community site, and read-mostly in aggregate. better-sqlite3 is synchronous, which is finer here rather than a compromise: the statements are prepared once, the queries are indexed and sub-millisecond, and an async driver would add a promise per query and buy nothing. It is also why lib/db.js can prepare a statement and call .run() in the middle of an ordinary function, which is what makes lib/forum.js and lib/admin.js readable.

WAL keeps the reader and the writer out of each other's way, and foreign_keys = ON gives the relations real enforcement instead of documentation.

The honest constraint is that SQLite means a single process. An SSE notification event only reaches the connection held by the process that produced it, so a second worker behind a load balancer would serve pages perfectly and quietly break live notifications; lib/session-store.js documents the same limitation for sessions and solves it by putting them in the database rather than in RAM. That trade is accepted deliberately — one process, one file, one box, no orchestration — until the site outgrows it, at which point the migrations are the only work.

---

Next: The API reference · Auth and roles · Security and privacy

---

Back to the documentation index.