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 (no0 O 1 I l). Examples:usr_…,org_…,tm_…,vid_…,cmt_…,shr_…. - Deletes of videos and comments are soft:
deleted_atis 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.