Database View

Relationships

Relationships express the application's conceptual model. Verify actual foreign keys/cascades against the provisioned database. verification has no user foreign key in the documented auth model. yjs_updates is keyed by room name, not a demonstrated foreign key to user_files.

Identity and Document Tables

Table	Fields consumed by code	Purpose
"user"	id, name, email, image, auth fields; role	Better Auth identity; role='admin' permits daily scheduling
account	Provider/account IDs, userId, OAuth token/expiry fields	Better Auth account linkage
session	id, userId, token, expiresAt, timestamps, client metadata	Better Auth sessions
verification	id, identifier, value, expiry/timestamps	Better Auth verification records
user_files	id, user_id, filename, problem_slug, created_at, updated_at	Saved document metadata; optional catalog association
file_versions	id, file_id, version_number, storage_path, mime_type, size_bytes, created_at	Version metadata; external bytes
contributors	file_id, user_id, added_at	Collaborative access
file_invites	id, file_id, inviter_id, invitee_id, status, created_at, responded_at	Pending/accepted/declined/cancelled invitations

Better Auth has its own PostgreSQL pool; application routes use lib/db.ts. The daily admin check queries the application-owned role column rather than installing the Better Auth admin plugin.

Document Storage

Bytes are uploaded through FILE_SERVER_URL using X-Internal-Key. PostgreSQL records only their path, size, MIME type, and version metadata. Saving keeps the newest four versions; pruning commits database deletion before best-effort removal of old storage bytes.

problem_slug links a saved solution back to a catalog question. GET /api/files/:fileId returns it as X-Problem-Slug when present. Generated daily puzzles use synthetic slugs and are not stored as a catalog association by the editor.

Invitations

Sending an invitation creates a pending file_invites row. Accepting it marks the invitation accepted and inserts the contributor relation in one transaction. Declining does not grant access; an owner can cancel a pending invitation. The handlers rely on uniqueness for duplicate invitations/contributor pairs, but this checkout does not include the DDL defining those constraints.

Problems and Grading

Table	Fields consumed by code
problems	id, slug, title, difficulty, statement, variant, tape_count, starter_source, cases, constraints, is_builtin, author_user_id
problem_submissions	user_id, problem_id, accepted, passed, total, attempts, verdict, machine, created_at, updated_at

Cases and constraints are JSON data. Each case records input, expected output, result (acc, rej, halt, cut), cutoff, and public/private visibility. Client projection hides private cases from non-authors; the server still uses them during grading.

The submission route upserts one progress row per user/problem and increments attempts, rather than appending a row for every attempt. It updates the latest verdict/accepted status and only replaces the previously saved machine on an accepted subsequent submission. Anonymous callers receive grading feedback without a persisted account submission (recorded: false).

Daily Puzzles

Table	Fields consumed by code
daily_puzzles	id, puzzle_date, kind, problem_id, machine, answer_slots, cases, starter_source, hidden_refs, statement, probe_budget, answer_budget, reveal_policy, created_by, updated_at
daily_progress	player_id, puzzle_id, user_id, probes_used, answers_used, solved, solved_at
daily_attempts	id, player_id, puzzle_id, user_id, kind, payload, result, accepted, created_at

• puzzle_date is treated as a calendar date, selected as text to avoid driver timezone conversion shifting the day.
• A daily can be blackbox or build, generated from a reference machine or optionally linked to a catalog problem.
• Reference machines, expected answer slots, and private cases are server-side data. Public projections and reveal policy determine what the browser sees.
• The HttpOnly fla_daily UUID cookie supplies player_id, optionally linked to user_id. There is no demonstrated separate player table.
• Conditional SQL updates spend a probe/answer only while below its budget. Attempts log earned probe results or submitted predictions/verdicts.
• Streaks/history are derived from scheduled puzzles and solved progress, not an assumed separate ranking table.

Admin scheduling can upsert a date or rewrite an existing puzzle while retaining earlier attempts. Those attempts then describe the previous content; deleting and rescheduling is the workflow when that history must be cleared. Route comments assume cascade deletion, which must exist in deployed DDL.

Collaboration Update Log

The dedicated yjs/yjs-server.mjs process reads/writes:

update contains binary Yjs updates. Loaded histories over YJS_COMPACT_AFTER (default 1000) are compacted to a snapshot. The log preserves room contents across restarts; awareness is live presence, not a permanent user/cursor history.

Room names come from the WebSocket URL and must remain consistent through proxy changes. They are not interchangeable with explicit file version numbers. This persistence requires the external database table to be provisioned; starting the container does not create it.