Database

Vistilla stores every record in SQLite, accessed with sqlx 0.8. The SQLite library is compiled into the binary, so the server needs no system SQLite.

Connection settings (db.rs)

Setting	Value
URL	VISTILLA_DATABASE_URL, default sqlite:///data/vistilla.db?mode=rwc
Journal	WAL
synchronous	NORMAL
Foreign keys	on, for every connection
Busy timeout	10 s
Pool	up to 8 connections, 10 s acquire timeout

Conventions

• Times are integers: milliseconds since 1970 (UTC).
• Ids are <prefix>_ plus 18 random characters from an alphabet without look-alikes (no 0 O 1 I l). Examples: usr_…, org_…, tm_…, vid_…, cmt_…, shr_….
• Deletes of videos and comments are soft: deleted_at is set.
• Secrets are never stored raw where they can be avoided:
  • session ids, reset tokens, sign-up codes and guest cookies are stored as SHA-256 hashes;
  • AI keys are encrypted.

Migrations

Migrations are plain SQL files in migrations/. They are embedded into the binary with include_str! and listed in db.rs.

On start, each one not yet recorded in the schema_migrations table runs in its own transaction. There is no down-migration: to change something, a new migration is added.

#	File	What it does
0001	init	Users, sessions, workspaces, teams, invites, collections, videos, tags, chapters, share links, comments, reactions, mentions, views, notifications.
0002	transcripts_replies_delivery	Transcripts and segments, summaries and points, video replies, notification settings, Slack integrations, the outbox.
0003	ai_providers	Per-workspace AI provider settings with the sealed key.
0004	password_resets	One-hour, one-use reset tokens (hashed).
0005	anchor_transcript_timings	One-off fix: rescales old transcripts whose timings ran past the video's end.
0006	seekable_recordings	videos.seekable (0 = not yet, 1 = done, −1 = failed).
0007	one_reaction_each	One reaction per person per comment.
0008	guest_reactions	Reactions from guests on share links.
0009	share_invites	share_links.recipient_email for personal links.
0010	topic_chapters	chapters.source (manual / ai), summaries.chapters_done.
0011	retry_topic_chapters	Re-runs summaries that had no chapters.
0012	unique_workspace_names	Unique workspace names (case-insensitive).
0013	no_notification_email	Removes queued notification emails (activity is in-app only).
0014	live_uploads	videos.multipart_id and upload_parts, for uploading while recording.
0015	trim	videos.trim_start_ms, trim_end_ms.
0016	silences	videos.silences (JSON list of [start_ms, end_ms]).
0017	call_to_action	videos.cta_label, cta_url.
0018	watched_notices	One "watched" notification per viewer; notifications.actor_label.
0019	moment_reactions	Emoji moments on the timeline.
0020	watched_notice_link	Links a watched notice to its notification.
0021	signup_codes	Pending sign-ups waiting for their emailed code.
0022	redo_silent_transcript	One-off data fix: clears the AI output of one silent video so it is redone.
0023	drop_owner_views	Removes views an owner made of their own video.
0024	user_avatars	user_avatars (the photo itself) and users.avatar_v (its version; NULL means initials).
0025	drop_slack	Drops team_integrations, outbox and notification_settings: Slack was removed, and nothing is queued for sending any more.
0026	unique_team_names	Numbers any team names already shared in a workspace, then makes (org_id, lower(trim(name))) unique.
0027	one_reaction_each	Keeps only each person's latest emoji per video, and makes it unique per (video_id, user_id) and per (video_id, guest_hash).
0028	topic_details	summary_points.title, for the topics of the summary's topics map (kind = 'topic'); every ready summary is written again to get them.
0029	index_large_recordings	Sets seekable back to 0 for WebM recordings marked −1, so they get their index as they stream.
0030	link_buttons	videos.cta_links: every link button as JSON [{label, url}]; the single one stored before becomes the first. cta_label/cta_url keep the first, for older apps.
0031	site_logos	site_logos(host, icon, updated_at): a logo remembered per website, for sites that give none (behind a sign-in). Saved whenever a button is given a picked logo; /media/favicon/{host} serves it first. Preloaded with Silsila's icon from its repository.
0032	orphan_replies	Deletes replies whose parent comment was deleted (they were counted but never shown). Deleting a comment now deletes its replies too.
0033	redo_noisy_transcript	Clears one hosted recording's transcript, summary and AI chapters, which had a line invented from noise and lines 3.5 s early, so they are written again with the new checks.
0040	translations	translations(video_id, lang, kind, source_at, status, body, model, error): a transcript or summary in the app's other language (en/ar), body being its translated strings in order. Out of date once source_at differs from the original's updated_at.

Tables

People and workspaces

Table	Key columns	Purpose
users	id, email, email_lower (unique), name, password_hash, avatar_hue, avatar_v	Accounts.
user_avatars	user_id, content_type, bytes, updated_at	A person's photo (256 px, kept in the database, not the bucket).
sessions	id (= SHA-256 of the cookie), user_id, expires_at, user_agent	Sign-in sessions.
signup_codes	email_lower (PK), name, password_hash, org_name, code_hash, attempts, expires_at	A sign-up waiting for its 6-digit code.
password_resets	id (= SHA-256 of the token), user_id, expires_at, used_at	Reset links.
orgs	id, name (unique, case-insensitive), slug	Workspaces.
org_members	(org_id, user_id), role	Workspace role: owner, admin, member or viewer.
teams	id, org_id, name, slug	Teams in a workspace.
team_members	(team_id, user_id), role	Team role.
invites	org_id, team_id, email_lower, role, token, expires_at, accepted_at	Team invitations (14 days).

Videos

Table	Key columns	Purpose
videos	id, team_id, collection_id, uploader_id, title, description, storage_key, thumb_key, content_type, size_bytes, duration_ms, width, height, visibility, status, kind, reply_to_comment_id, seekable, multipart_id, trim_start_ms, trim_end_ms, silences, cta_label, cta_url, cta_links, deleted_at	One row per recording or upload. status is uploading, processing (a trim is being applied), ready or failed. kind is recording or comment_reply.
upload_parts	(video_id, part_number), etag, size_bytes	Parts of a live upload.
video_tags	(video_id, tag)	Tags (lower-case, up to 25 per video).
collections	id, team_id, name, created_by	Folders.
chapters	id, video_id, title, timecode_ms, source	Manual or AI chapters.

Feedback

Table	Key columns	Purpose
comments	id, video_id, author_id or guest_name, share_link_id, parent_id, body, timecode_ms, pinned, resolved_at, reply_video_id, deleted_at	Comments and replies. A NULL timecode means "not pinned to a moment".
comment_reactions	(comment_id, user_id) unique, emoji	Members' reactions.
guest_reactions	(comment_id, guest_hash), emoji	Guests' reactions.
moment_reactions	video_id, user_id or guest_hash, emoji, timecode_ms	Emoji moments on the timeline.
mentions	comment_id, user_id	@mentions.

Sharing and viewing

Table	Key columns	Purpose
share_links	id, video_id, token, password_hash, expires_at, allow_download, allow_comments, recipient_email, revoked_at	Public links (recipient_email is NULL) and personal email links.
views	video_id, session_key (unique together), viewer_id, share_link_id, watched_ms, completed	One row per viewing session. watched_ms only grows.
watched_notices	(video_id, viewer_key), notification_id	Makes sure the owner is told only once per viewer.

AI

Table	Key columns	Purpose
ai_providers	(org_id, purpose) unique, kind, vendor, base_url, model, api_key_sealed, api_key_hint, last_error, last_ok_at	The workspace's provider for transcription and for summary.
transcripts	video_id (PK), status, engine, language, text, error	Also the transcription job queue (pending → running → ready / failed).
transcript_segments	video_id, idx, start_ms, end_ms, text	Timed lines.
summaries	video_id (PK), status, model, headline, body_md, chapters_done, error	Also the summary job queue.
translations	video_id, lang, kind (transcript / summary), source_at, status, body	The transcript or summary in English or Arabic, made from the original when someone reads it in the other language.
summary_points	video_id, kind (highlight / action / topic), idx, text, timecode_ms, title	Key moments, action items, and the topics of the topics map (title is the topic's name, text what was said).

Notifications

Table	Key columns	Purpose
notifications	user_id, kind, actor_id or actor_label, video_id, comment_id, read_at	The activity bell. Kinds in use: comment, reply, mention, resolved, reaction, member_joined, watched.

Search

There is no full-text index. Search uses LIKE with %, _ and \ escaped, across:

• titles and descriptions;
• tags;
• transcript lines;
• comments.

For the size of a team's library this is fast enough, and it works on any SQLite build.

Backups

On the hosted instance, Litestream continuously replicates the database file to the same bucket as the videos (litestream/loom.db).

A new server takes the database over from the one still running rather than from the bucket (handoff.rs): the old one turns new requests away with 503 and X-Vistilla-Updating (the website and the app send them again), waits for those under way, and sends a VACUUM INTO copy, checked by SHA-256 and SQLite's quick_check on arrival. Nothing written is lost, and the bucket is not asked for anything. The bucket's copy is restored only when no server is running. See Deployment.