Every analytics tool wants a subscription. And your data. I built Twiligent instead. YouTube stats. Instagram stats. One dashboard. Runs locally on your machine.
Database Schema
Overview
Supabase (PostgreSQL 17) with eleven application tables: the four original ones, the six comment automation tables (automation_accounts, automation_rules, automation_comments, automation_followups, automation_public_replies, automation_attempts) and audit_events. Every table has user_id (uuid) referencing auth.users, so rows belong to one user, and created_at and updated_at timestamps.
The backend uses the service-role key and enforces isolation by always appending .eq('user_id', userId) on user-scoped queries. The userId comes exclusively from the verified JWT - never from request input.
The cron jobs (scheduledPublisher.publishDue(), tokenRefresh.refreshExpiringInstagramTokens(), the reply dispatch, the reply queue reconciliation and the retention purge) and the Instagram webhook intentionally use the repository methods without a userId filter to operate across all users.
The browser roles (anon, authenticated) have no access to any table or function: row level security is on with no policies, and their grants are revoked. See SECURITY.md.
How the Schema Is Managed
The schema lives in versioned migrations in supabase/migrations/, applied with the Supabase CLI (a root dev dependency). Production records each applied file in supabase_migrations.schema_migrations. Nothing is pasted into the SQL editor any more. ADR-038 records the decision.
| Path | Holds |
|---|---|
supabase/migrations/<version>_<name>.sql |
One database entity: a table, its browser access, one trigger, one index or one function. The 14 digit version orders the files |
supabase/rollbacks/<version>_<name>.sql |
The reverse of the migration with the same file name. The CLI has no down migrations, so these are run by hand |
supabase/schemas/ |
The schema as it stands, one file per object, generated by npm run db:schema from the database: public/tables/<table>.sql (the table with its checks, indexes, trigger and grants), public/functions/<function>.sql, public/schema.sql and the default privileges, and _cluster/extensions/. Read it to see a table whole; never edit it by hand |
supabase/config.toml |
The CLI's default config, with major_version = 17 to match the hosted database |
supabase/.gitignore |
Keeps the CLI's .temp and .branches folders and local env files out of git |
The migrations stay in one flat folder because the Supabase CLI reads no subfolders there, and it matches applied migrations by their version alone, so an applied file is never renamed or given a new version (ADR-046). New files come from npm run db:new <verb>_<object>, which puts the current UTC time in the version; the files up to 20261016132100 were numbered by hand in steps of 100 and keep those versions. Each migration and rollback opens with one sentence that says what it does, and its name starts with the verb: baseline, create, alter, add, index, revoke, check, replace, drop or rename. backend/tests/database/migrations.test.ts checks these rules and that supabase/schemas/ names the same tables, indexes, functions and triggers as the migrations.
The first four files are baselines. Each recreates one table as it stood before migrations, with if not exists everywhere, so they are safe on a database that already has the tables. They also bring older setups to the same starting point: they add created_at and the claim columns where missing, replace null data and value with {}, and mark posts stuck in publishing with no claim as failed. The baselines have no rollback, because undoing them would drop tables with data.
| Order | Migrations | What they do |
|---|---|---|
| 1 | baseline_accounts, baseline_settings, baseline_scheduled_posts, baseline_tracked_posts |
The pre-migration tables, repeatable and normalising older shapes |
| 2 | revoke_browser_access_<table> (four files) |
Drop the old users_own_* policies and revoke all from anon and authenticated |
| 3 | revoke_browser_default_privileges |
New tables and functions in public start without browser grants |
| 4 | check_<table>_values (four files) |
The CHECK constraints below |
| 5 | add_accounts_id_user_id_key |
unique (id, user_id) on accounts, the target of the composite foreign keys |
| 6 | add_scheduled_posts_account_fkey, add_tracked_posts_account_fkey |
The composite foreign keys below |
| 7 | create_set_updated_at_function, add_<table>_updated_at (three files), add_tracked_posts_updated_at_trigger |
The updated_at column and its trigger on every table |
| 8 | create_merge_account_data_function, create_merge_scheduled_post_function, create_merge_tracked_post_data_function |
The merge functions below |
| 9 | index_accounts_list, index_scheduled_posts_list, index_scheduled_posts_schedulers, index_tracked_posts_list |
The indexes below |
| 10 | For each of automation_accounts, automation_rules, automation_comments, automation_attempts and audit_events: create_<table>, revoke_browser_access_<table>, add_<table>_updated_at_trigger, then one index_<table>_<purpose> per index |
The automation and audit tables, one entity per file (29 files) |
| 11 | index_accounts_instagram_user |
The webhook's lookup of an account by its Instagram id |
| 12 | create_automation_rules_for_ig_user_function, create_claim_automation_comments_function, create_reserve_automation_send_function, create_finish_automation_send_function, create_automation_stats_function |
The automation functions below |
| 13 | alter_automation_rules_match_options, alter_automation_comments_followup_review, alter_automation_accounts_limit_fields, alter_automation_attempts_kind_digest |
The reply queue columns: any-comment and followers-only rules, the follow-up wait and review fields, limited_until, the attempt kind and digest (#99, #100, #102, #103) |
| 14 | create_automation_followups, revoke_browser_access_automation_followups, add_automation_followups_updated_at_trigger, then index_automation_followups_<purpose> (four files) and index_automation_comments_<purpose> (three files) |
The follow-up table and the indexes the turn-taking claim, the follow-up lookup and the review read |
| 15 | replace_claim_automation_comments_function, replace_reserve_automation_send_function, replace_finish_automation_send_function, then create_<name>_function for the follow-up, review, expiry, purge and queue health functions (nine files) |
The reply queue functions below; the replaced ones keep their old arguments working |
| 16 | alter_automation_rules_reply_actions, alter_automation_accounts_public_reply_fields, alter_automation_attempts_public_reply |
The public reply columns on rules, accounts and attempts (#107) |
| 17 | create_automation_public_replies, revoke_browser_access_automation_public_replies, add_automation_public_replies_updated_at_trigger, then index_automation_public_replies_<purpose> (five files) and index_automation_attempts_public_reply |
The public reply table and its indexes |
| 18 | replace_automation_rules_for_ig_user_function, create_store_automation_comments_function, the four public reply functions, create_automation_public_review_candidates_function, create_record_automation_public_review_function, then replace_ for the expiry, queue health, stats and comment claim functions |
The public reply functions below, and the existing functions taught about public replies |
| 19 | index_accounts_identity |
The unique index accounts_identity_key below, one row per account (#116) |
| 20 | alter_tracked_posts_youtube_only, drop_tracked_posts_account_id, alter_accounts_without_reddit, replace_accounts_identity_index |
The Reddit cleanup (#145) |
| 21 | create_mcp_access_tokens, revoke_browser_access_mcp_access_tokens, add_mcp_access_tokens_updated_at_trigger, index_mcp_access_tokens_user_created, the same four for mcp_client_grants, alter_audit_events_subject_types |
The Claude (MCP) tables below (#146) |
| 22 | replace_automation_rules_for_ig_user_function |
Comments route only to accounts that still hold an unexpired Instagram token (audit lead L2, #161) |
| 23 | replace_reserve_automation_send_function |
The hourly account limit counts replies through every connection to the same Instagram account (audit lead L2, #161) |
From order 10 on, every migration creates at most one entity, and the migration test enforces it. The files before it were applied to production first and stay as recorded.
To change the schema, follow docs/features/NEW_DATABASE_TABLE.md (Write the Migration). The commands are in docs/workflows/DEPLOYMENT.md.
Table: accounts
Stores connected YouTube channels and Instagram accounts.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
text | NOT NULL | PK. A UUID from newRowId() (crypto.randomUUID()); older rows keep their earlier ids |
user_id |
uuid | NOT NULL | FK -> auth.users.id ON DELETE CASCADE. All user-scoped queries filter on this |
platform |
text | NOT NULL | 'youtube' or 'instagram' |
data |
jsonb | NOT NULL DEFAULT '{}' |
All platform-specific fields (see below) |
created_at |
timestamptz | NOT NULL DEFAULT now() | When the account was connected. The list order |
updated_at |
timestamptz | NOT NULL DEFAULT now() | Set by the trigger on every update |
| Constraint | Rule |
|---|---|
accounts_platform_check |
platform is 'youtube' or 'instagram' |
accounts_data_check |
data is a JSON object |
accounts_id_user_id_key |
unique (id, user_id), so posts can reference an account together with its owner |
accounts_identity_key |
Unique index on (user_id, platform, identity), where the identity is data ->> 'channelId' for YouTube and data ->> 'igUserId' for Instagram. Two adds of the same account at the same moment store one row; the other gets the usual 409 |
accountRepository still selects only youtube and instagram rows, which the check now guarantees. The rows, check value and index branch of the platform removed in v2.0.0 were deleted by the cleanup migrations of #145 (docs/platform-v2/DATABASE_MIGRATION.md, ADR-049 in DECISIONS.md).
data jsonb shape for YouTube accounts
{
"channelId": "UCxxxxxx",
"title": "Channel Name",
"description": "...",
"customUrl": "@handle",
"country": "US",
"publishedAt": "2020-01-01T00:00:00Z",
"subscriberCount": 12345,
"viewCount": 456789,
"videoCount": 100,
"thumbnailUrl": "https://...",
"uploadPlaylistId": "UUxxxxxx",
"lastRefreshed": "2025-07-01T00:00:00Z"
}
data jsonb shape for Instagram accounts
{
"igUserId": "17841400000000000",
"username": "myaccount",
"accountType": "BUSINESS",
"mediaCount": 200,
"followersCount": 5000,
"accessToken": "EAAxxxx...",
"tokenExpiresAt": "2025-09-01T00:00:00Z",
"lastRefreshed": "2025-07-01T00:00:00Z"
}
Critical: accessToken is always stripped by safeAccount() before being returned in any API response. It is intentionally stored server-side for use by the publishing and token-refresh pipelines.
Table: settings
Per-user key-value configuration store.
| Column | Type | Nullable | Description |
|---|---|---|---|
user_id |
uuid | NOT NULL | PK (composite). FK -> auth.users.id ON DELETE CASCADE |
key |
text | NOT NULL | PK (composite). Currently only 'api_keys' |
value |
jsonb | NOT NULL DEFAULT '{}' |
Configuration payload |
created_at |
timestamptz | NOT NULL DEFAULT now() | When the row was first saved |
updated_at |
timestamptz | NOT NULL DEFAULT now() | Set by the trigger on every update, the upsert's update included |
| Constraint | Rule |
|---|---|
| Primary key | (user_id, key): one row per user per key. Upserts use onConflict: 'user_id,key' |
settings_value_check |
value is a JSON object |
value jsonb shape for key='api_keys'
{
"github": {
"repoOwner": "owner",
"repoName": "repo-name",
"branch": "main"
}
}
The api_keys setting stores the GitHub repository used to build the scheduling guide links. No token is stored, and saving the repository drops a token an older version left behind (ADR-031). Other keys in value are kept as they are, so the key name can hold future per-user settings.
Table: scheduled_posts
Stores Instagram posts queued for future publishing.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
text | NOT NULL | PK. A UUID from newRowId(); older rows keep their earlier ids |
user_id |
uuid | NOT NULL | FK -> auth.users.id ON DELETE CASCADE |
account_id |
text | NOT NULL | With user_id, FK -> accounts (id, user_id) ON DELETE CASCADE |
status |
text | NOT NULL DEFAULT 'pending' | 'pending', 'publishing', 'published', 'failed' |
scheduled_at |
timestamptz | NOT NULL | UTC timestamp when the post should be published |
claimed_at |
timestamptz | NULL | When a scheduler last claimed the post. A publishing post whose claim is older than 20 minutes is taken over by the next run |
attempts |
integer | NOT NULL DEFAULT 0 | How many times a scheduler has claimed the post. The third failed attempt is final |
data |
jsonb | NOT NULL DEFAULT '{}' |
Post content and result data (see below) |
created_at |
timestamptz | NOT NULL DEFAULT now() | When the post was scheduled |
updated_at |
timestamptz | NOT NULL DEFAULT now() | Set by the trigger on every update, the claim included |
claimed_at and attempts change only in the claim, never through updateById or updatePendingForUser. The API returns them as claimedAt and attempts on ScheduledPost.
| Constraint | Rule |
|---|---|
scheduled_posts_status_check |
status in ('pending', 'publishing', 'published', 'failed') |
scheduled_posts_attempts_check |
attempts >= 0 |
scheduled_posts_claim_check |
A publishing post has a claimed_at, because the lease is measured from it |
scheduled_posts_data_check |
data is a JSON object |
scheduled_posts_account_fkey |
(account_id, user_id) -> accounts (id, user_id) ON DELETE CASCADE |
The composite foreign key means a post can only name an account of the same user. Removing an account removes its scheduled posts, published records included.
Status lifecycle
pending -> publishing -> published
│ ↘ failed
├─> pending a passing failure, while attempts < 3
└─> publishing a takeover after the 20 minute lease
A claim is one UPDATE that sets status = 'publishing', claimed_at = now() and attempts = attempts + 1, and only matches the post as the scheduler listed it. Two schedulers never hold the same post, and a run that died leaves a claim the next run can take over. See docs/architecture/data-flows/SCHEDULING.md.
data jsonb shape
{
"mediaType": "IMAGE | REELS | STORIES",
"mediaUrl": "https://res.cloudinary.com/...",
"caption": "Post caption text",
"shareToFeed": true,
"coverUrl": "https://...",
"audioName": "...",
"thumbOffset": 1000,
"locationId": "...",
"userTags": [{ "username": "...", "x": 0.5, "y": 0.5 }],
"altText": "...",
"collaborators": ["username1", "username2"],
"containerId": "17900000000000001",
"publishedMediaId": "17841400000000001",
"publishedAt": "2025-07-01T10:00:00Z",
"error": "Error message if failed"
}
containerIdis the Instagram media container, saved right after it is created and before the scheduler waits or publishes. A later run reads itsstatus_codeto find out whether the post already went out. It is cleared when the container expired and a retry needs a new one.automation({ replyText, publicReplyTexts }, optional) answers every comment on the post once it is published;automationRuleIdis the rule made for it then, andautomationErrorwhy it could not be made (docs/architecture/data-flows/SCHEDULING.md).publishedMediaIdisnullwhen a run found the post already published through the container status, because that status does not carry the media id.erroris set on afailedpost, and also on apendingpost that is being retried, where it holds the reason the last attempt failed.
Table: tracked_posts
Stores the YouTube videos the user wants to monitor. trackedPostRepository writes content_type: 'youtube' on every insert and reads only youtube rows, which the check now guarantees.
| Column | Type | Nullable | Description |
|---|---|---|---|
id |
text | NOT NULL | PK. A UUID from newRowId(); older rows keep their earlier ids |
user_id |
uuid | NOT NULL | FK -> auth.users.id ON DELETE CASCADE |
url |
text | NOT NULL | Full URL of the YouTube video |
label |
text | NOT NULL DEFAULT '' | User-defined name for the item |
category |
text | NOT NULL DEFAULT '' | User-defined grouping label |
content_type |
text | NOT NULL DEFAULT 'youtube' | 'youtube'; kept as a column so another kind of tracked item can be added with one check change |
created_at |
timestamptz | NOT NULL DEFAULT now() | When the user added the item |
updated_at |
timestamptz | NOT NULL DEFAULT now() | Set by the trigger on every update; the code no longer sets it |
data |
jsonb | NULL | Cached data fetched from the platform API, stored in the same insert that creates the row (see below) |
| Constraint | Rule |
|---|---|
tracked_posts_content_type_check |
content_type is 'youtube' |
tracked_posts_data_check |
data is null or a JSON object |
The cleanup migrations of #145 deleted the tracked items of the platform removed in v2.0.0 and dropped account_id with its foreign key and check, which only that platform used (docs/platform-v2/DATABASE_MIGRATION.md, ADR-049 in DECISIONS.md).
data jsonb shape for YouTube items
Populated by fetchTrackedYouTubeData() in backend/services/youtube/youtubeTracked.ts:
{
"contentType": "youtube",
"title": "Video Title",
"channelTitle": "Channel Name",
"channelId": "UCxxxxxx",
"thumbnail": "https://i.ytimg.com/vi/xxxx/mqdefault.jpg",
"publishedAt": "2026-01-01T00:00:00Z",
"viewCount": 12345,
"likeCount": 567,
"commentCount": 89,
"isShort": false,
"permalink": "https://www.youtube.com/watch?v=xxxx",
"lastFetchedAt": "2026-08-04T10:00:00Z"
}
Data is fetched on create and on manual refresh only. Not re-fetched on every GET. A refresh merges the fetched keys into data with patchDataForUser() (merge_tracked_post_data) rather than replacing the blob, so anything writing to data merges, never replaces.
Comment Automation Tables
The data flow is in docs/architecture/data-flows/AUTOMATION.md. Every table references its account with the composite (account_id, user_id) foreign key to accounts (id, user_id) on delete cascade, so removing an Instagram account removes its rules, controls, comments, follow-ups, public replies and attempts.
Table: automation_accounts
One row per Instagram account whose automation was paused, subscribed or cooled down; an account without a row has the defaults.
| Column | Type | Constraints | Description |
|---|---|---|---|
account_id |
text | primary key, part of the account foreign key | The Twiligent account |
user_id |
uuid | not null, references auth.users on delete cascade |
Owner |
paused |
boolean | not null, default false | Replies are held while true |
paused_reason |
text | at most 200 characters | Null when the owner paused it; set when the Worker held replies because Instagram refused the token |
cooldown_until |
timestamptz | No reply is sent before this time; set from Instagram rate limits and blocks, and only ever moved later | |
cooldown_reason |
text | at most 200 characters | Shown on the page |
subscribed_at |
timestamptz | When Instagram confirmed it sends this account's comments to the webhook | |
subscribed_fields |
text[] | The webhook fields Instagram confirmed (comments, and messages for followers-only rules); null for a subscription made before fields were recorded, which asked for comments only |
|
limited_until |
timestamptz | Set by reserve_automation_send when the account reached its hourly reply limit; the claim leaves the account's jobs alone until then |
|
public_cooldown_until |
timestamptz | No public reply is posted before this time; set from Instagram rate limits and blocks on public replies, only ever moved later. Private replies ignore it | |
public_cooldown_reason |
text | at most 200 characters | Shown on the page |
public_limited_until |
timestamptz | Set by reserve_automation_public_reply when the account reached its hourly public reply limit |
|
created_at, updated_at |
timestamptz | not null, default now() |
Table: automation_rules
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId() |
user_id, account_id |
uuid, text | not null, composite foreign key | Owner and Instagram account |
name |
text | 1 to 80 characters | |
keywords |
text[] | 1 to 10 items; none when match_mode is any |
Matched as whole words or as the whole comment |
match_mode |
text | contains, exact or any, default contains |
any answers every comment with text |
case_sensitive |
boolean | not null, default false | |
media_ids |
text[] | not null, default {}, at most 50 |
Empty means every post and reel |
private_reply_enabled |
boolean | not null, default true | The rule sends a private reply; at least one of the two replies is on (automation_rules_actions_check) |
reply_text |
text | 1 to 1,000 characters; may be empty while private_reply_enabled is false |
The service also checks text plus link against Instagram's 1,000 bytes |
link_url |
text | null or ^https:// |
Added on its own line after the text |
enabled |
boolean | not null, default false | |
public_reply_enabled |
boolean | not null, default false | The rule posts a public reply under the comment |
public_reply_texts |
text[] | not null, default {}, at most 5; at least 1 while public_reply_enabled |
The wordings; one is picked per comment. The Worker keeps each to 300 characters, 4 hashtags and 1 link |
hourly_limit |
integer | null or 1 to 750 | Private replies an hour for this rule |
require_follow |
boolean | not null, default false; needs private_reply_enabled |
Followers-only: the private reply asks the person to follow and reply, and the reply and link go out once Instagram says they follow |
follow_prompt_text |
text | null or 1 to 1,000 characters; required when require_follow |
The private reply of a followers-only rule |
not_following_text |
text | null or 1 to 1,000 characters; required when require_follow |
The reminder for someone who replies but does not follow |
created_at, updated_at |
timestamptz | not null, default now() |
Table: automation_comments
Every comment the webhook delivered for an account with an enabled rule. A matched comment is also the job that sends its reply.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId() |
user_id, account_id |
uuid, text | not null, composite foreign key | The member whose rule (or oldest account) owns it |
rule_id |
text | references automation_rules on delete set null |
The matched rule; null when none matched |
ig_user_id |
text | not null | The professional account the webhook named |
comment_id |
text | not null; unique (ig_user_id, comment_id) |
Refuses a notification Meta delivered again |
media_id, from_id |
text | The post and the commenter's Instagram-scoped id | |
from_username, text |
text | text at most 2,200 characters |
Kept only for matched comments |
status |
text | not null, one of the values below | |
status_reason, error_message |
text | error_message at most 500 characters |
Why it is in its status, for people |
attempts |
integer | not null, default 0, at least 0 | Requests sent to Instagram |
next_attempt_at |
timestamptz | required while queued or retry_wait |
When it is due |
claimed_at |
timestamptz | When a run took it; the lease is 20 minutes | |
message_id, accepted_at |
text, timestamptz | Instagram's message id and when it accepted the reply | |
recipient_id |
text | Who Instagram says received the reply, else the commenter; a followers-only answer is matched to the comment by it | |
gate_status |
text | null, awaiting_reply, completed or closed |
Followers-only: waiting for the person's answer, link sent, or every reminder used |
gate_prompts |
integer | not null, default 0, at least 0 | Reminders sent |
review_checks, review_checked_at |
integer, timestamptz | review_checks not null, default 0 |
Looks at the conversation for a reply whose answer was lost |
notified_at, expires_at |
timestamptz | not null | When Meta sent it, and 7 days later |
created_at, updated_at |
timestamptz | not null, default now() |
status is one of ignored, queued, processing, retry_wait, sending, accepted, failed, needs_review, skipped, expired. The status is the private reply's; a comment whose rule sends no private reply is skipped with reason private_reply_off. The lifecycle is in AUTOMATION.md.
Table: automation_followups
The message sent after a person answered a followers-only private reply: the reply and link once Instagram says they follow, a reminder otherwise. Each row is a job like a comment row. The person's message text is never stored.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId(), made by the Worker before the insert |
user_id, account_id |
uuid, text | not null, composite foreign key | |
automation_comment_id |
text | not null, references automation_comments on delete cascade |
The followers-only reply it answers |
ig_user_id, recipient_id |
text | not null | The professional account and the person |
trigger_message_id |
text | not null; unique (ig_user_id, trigger_message_id) |
The person's message; refuses a message Meta delivered again |
status |
text | the comment statuses except ignored |
|
status_reason, error_message |
text | error_message at most 500 characters |
|
kind |
text | null, link or reminder |
Set when the follow check answers |
attempts |
integer | not null, default 0, at least 0 | |
next_attempt_at |
timestamptz | required while queued or retry_wait |
|
claimed_at |
timestamptz | The lease is 20 minutes | |
message_id, accepted_at |
text, timestamptz | ||
window_expires_at |
timestamptz | not null | The person's message time plus 24 hours, Instagram's standard messaging window |
created_at, updated_at |
timestamptz | not null, default now() |
Table: automation_public_replies
The public reply under a matched comment, one per comment, created with the comment in one transaction. Each row is a job like a comment row, tracked apart from the private reply.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId(), made by the Worker before the insert |
user_id, account_id |
uuid, text | not null, composite foreign key | |
automation_comment_id |
text | not null, references automation_comments on delete cascade; unique |
The comment answered; refuses a second public reply job for it |
rule_id |
text | references automation_rules on delete set null |
|
ig_user_id, comment_id |
text | not null | The professional account and the Instagram comment replied to |
media_id, from_id |
text | The post and the commenter, for the limit of one public reply per person and post a day | |
reply_text |
text | not null, 1 to 2,200 characters | The wording picked when the comment arrived |
status |
text | the comment statuses except ignored |
|
status_reason, error_message |
text | error_message at most 500 characters |
|
attempts |
integer | not null, default 0, at least 0 | |
next_attempt_at |
timestamptz | required while queued or retry_wait |
|
claimed_at |
timestamptz | The lease is 20 minutes | |
reply_id, accepted_at |
text, timestamptz | The id Instagram gave the reply, and when | |
review_checks, review_checked_at |
integer, timestamptz | review_checks not null, default 0 |
Looks at the comment's replies for a reply whose answer was lost |
expires_at |
timestamptz | not null | The comment's 7 days |
created_at, updated_at |
timestamptz | not null, default now() |
Table: automation_attempts
One row per message or public reply request sent to Instagram. The private reply limits count the private_reply rows, the public reply limit the public_reply rows.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId(), made before the reservation |
user_id, account_id |
uuid, text | not null, composite foreign key | |
automation_comment_id |
text | not null, references automation_comments on delete cascade |
|
rule_id |
text | references automation_rules on delete set null |
|
recipient_id |
text | The commenter | |
outcome |
text | sending, accepted, rejected, rate_limited, ambiguous |
sending while the request is out |
kind |
text | not null, default private_reply; private_reply, followup or public_reply |
Follow-ups use the Send API and public replies the comment endpoint, so each kind's limits count only their own |
followup_id |
text | references automation_followups on delete cascade; set exactly when kind is followup |
|
public_reply_id |
text | references automation_public_replies on delete cascade; set exactly when kind is public_reply |
|
message_digest |
text | sha-256 of the text sent, to recognise a lost reply in the conversation or among the comment's replies | |
error_code, error_subcode, http_status |
integer | Instagram's answer | |
started_at, finished_at |
timestamptz | started_at not null, default now() |
|
created_at, updated_at |
timestamptz | not null, default now() |
Claude (MCP) Tables
How Claude clients sign in to the MCP endpoint (docs/platform-v2/MCP_DOCUMENTATION.md). Both are read and written only by the Worker with the service key; browser access is revoked like every other table.
Table: mcp_access_tokens
Personal access tokens a person creates in Settings, Claude. The token is shown once; only its hash is kept.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId() |
user_id |
uuid | not null, references auth.users on delete cascade |
|
label |
text | not null, 1 to 80 characters | The person's name for it, such as the computer it is for |
token_hash |
text | not null, unique, 64 lowercase hex characters | SHA-256 of the token (twl_ and 43 base64url characters from 32 random bytes); a fast hash is enough because the token is random, not a password |
token_hint |
text | not null, 4 characters | The token's last four characters, shown in Settings |
permission |
text | read or write |
Which tools the token may list and run |
last_used_at |
timestamptz | Written at most once an hour per token | |
expires_at |
timestamptz | Null when the token does not expire; at most 365 days ahead when set | |
revoked_at |
timestamptz | Set on revoke; a revoked token is refused and no longer listed | |
created_at, updated_at |
timestamptz | not null, default now() |
A person may have 20 active tokens.
Table: mcp_client_grants
The OAuth clients (Claude's apps) a person approved on the consent page, and what each may do. Supabase Auth issues the client's tokens; this table is Twiligent's own record, and the MCP endpoint refuses a client without a row here.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId() |
user_id |
uuid | not null, references auth.users on delete cascade |
|
client_id |
text | not null, unique with user_id |
The OAuth client id Supabase Auth issued, the client_id claim of its tokens |
client_name |
text | not null, 1 to 200 characters | The name the client registered, shown in Settings |
permission |
text | read or write |
Chosen on the consent page |
last_used_at |
timestamptz | Written at most once an hour per client | |
created_at, updated_at |
timestamptz | not null, default now() |
Approving the same client again updates its row, so its id stays the same.
Table: media_assets
The images and videos each person imported through the Claude media tools. The Cloudinary account is shared by everyone on an instance, so this row is what ties a file to one person; listing, renaming and deleting only ever reach the caller's rows.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId() |
user_id |
uuid | not null, references auth.users on delete cascade |
|
public_id |
text | not null, unique with user_id |
Cloudinary's id for the file; it never leaves the Worker |
resource_type |
text | image or video |
|
url |
text | not null, starts with https:// |
The public address Instagram publishes from |
format, bytes, width, height, duration_seconds |
text, bigint, integer, integer, numeric | As Cloudinary reported them | |
label |
text | not null, default '', at most 200 characters |
The person's name for the file |
owns_file |
boolean | not null, default false | True only when Twiligent created the file for this person (an import Cloudinary did not answer with existing); deleting the row deletes the file from Cloudinary only then, because a browser upload proves no ownership on the shared account |
created_at, updated_at |
timestamptz | not null, default now() |
Index media_assets_user_created_idx on (user_id, created_at desc) serves the list.
YouTube Publishing Tables
See docs/platform-v2/YOUTUBE_INTEGRATION.md for the flow. All three are reached only by the Worker with the service key.
Table: youtube_connections
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId() |
user_id |
uuid | not null, references auth.users on delete cascade |
|
account_id |
text | references accounts (id, user_id) on delete set null (account_id) |
The analytics account for the same channel, if any |
channel_id, channel_title |
text | unique (user_id, channel_id) |
|
scopes |
text[] | not null | As Google granted them |
status |
text | active or needs_reconnect |
|
refresh_token_enc, access_token_enc |
text | refresh not null | AES-GCM sealed under a key derived from TOKEN_ENCRYPTION_KEY; never leave the Worker |
access_token_expires_at |
timestamptz | ||
created_at, updated_at |
timestamptz | not null, default now() |
Table: youtube_uploads
| Column | Type | Constraints | Description |
|---|---|---|---|
id, user_id |
text, uuid | primary key; references auth.users on delete cascade |
|
connection_id |
text | references youtube_connections on delete cascade |
|
source_url, source_bytes, content_type |
text, bigint, text | https; bytes above 0 | The Cloudinary source, read in byte ranges |
thumbnail_url, is_short |
text, boolean | ||
title, description, tags, category_id |
text, text, text[], text | title 1 to 100 characters | The metadata snapshot |
privacy, publish_at |
text, timestamptz | private, unlisted or public; a publish time only with private |
|
made_for_kids, synthetic_media, notify_subscribers |
boolean | not null | |
state |
text | queued, uploading, processing, finalizing, done, waiting_quota, needs_reconnect, needs_review, failed, cancelled |
|
session_uri, bytes_confirmed, video_id |
text, bigint, text | bytes between 0 and the source size | Saved before any byte is sent |
attempts, next_attempt_at, lease_owner, lease_until, last_error |
integer, timestamptz, text, timestamptz, text | The runner's bookkeeping | |
client_request_id |
text | unique (user_id, client_request_id) |
The same request twice queues once |
created_at, updated_at |
timestamptz | not null, default now() |
Indexes: youtube_uploads_due_idx on (next_attempt_at) for the runnable states; youtube_uploads_user_created_idx on (user_id, created_at desc).
Table: youtube_quota_usage
The Google project's units per Pacific day and bucket (uploads: 100 inserts; default: 10,000 units), primary key (day, bucket).
Functions: claim_youtube_upload(p_owner, p_lease_seconds) leases one due job with for update skip locked; reserve_youtube_quota(p_day, p_bucket, p_units, p_limit) adds units only while they fit and answers whether it did. Both are granted to service_role only.
Table: audit_events
Who connected, reconnected or removed an account, changed a rule, paused or subscribed an account, or retried a reply. Rows reference only the user, so they outlive the account they describe.
| Column | Type | Constraints | Description |
|---|---|---|---|
id |
text | primary key | newRowId() |
user_id |
uuid | not null, references auth.users on delete cascade |
|
action |
text | ^[a-z_]+\.[a-z_]+$ |
account.connected, account.reconnected, account.removed, automation_rule.created, .updated, .duplicated, .deleted, automation_account.paused, .resumed, .subscribed, automation_comment.retried, automation_public_reply.retried, mcp_access_token.created, .revoked, mcp_client.approved, .revoked, media_asset.imported, .deleted, youtube_connection.connected, .reconnected, .removed, youtube_upload.created, .cancelled, and mcp_access_token.called_tool or mcp_client.called_tool for each write tool a Claude client ran (with the tool name and outcome) |
subject_type |
text | account, automation_rule, automation_account, automation_comment, mcp_access_token, mcp_client, scheduled_post, tracked_post, media_asset, youtube_connection, youtube_upload, settings |
The last six are for changes made through Claude clients, publishing, media and YouTube |
subject_id |
text | ||
details |
jsonb | not null, default {}, an object |
Small facts such as the platform or the changed field names; never tokens or comment text |
created_at, updated_at |
timestamptz | not null, default now() |
Retention: the daily cron deletes comments (and their attempts, follow-ups and public replies) older than 90 days and audit events older than 365 days.
Why CHECK Constraints, Not Enums
Allowed values (platform, status, content_type) are CHECK constraints, not Postgres enum types. A value is added or removed by replacing one constraint in a migration; an enum value cannot be removed, and adding one needs care inside a transaction. The constraint still records the allowed values in the schema, and a wrong value fails at the write instead of breaking a later read.
Indexes
An index exists because a frequent query filters or orders on its columns. Each list query is served by its index with no sort step.
| Index | Columns | Query it serves | Why |
|---|---|---|---|
accounts_user_id_created_at_idx |
accounts (user_id, created_at, id) |
accounts.listForUser: where user_id = $1 order by created_at, id |
Every page load lists the user's accounts; id breaks ties so the order never changes |
scheduled_posts_user_id_scheduled_at_idx |
scheduled_posts (user_id, scheduled_at) |
scheduledPosts.listForUser: where user_id = $1 order by scheduled_at, and the counts behind delete all |
Replaces scheduled_posts_user_id_idx |
scheduled_posts_due_idx |
scheduled_posts (scheduled_at) where status = 'pending' |
listDue: pending posts at or before now |
Partial, so the growing published history is left out. Runs every 15 minutes from two schedulers |
scheduled_posts_claimed_idx |
scheduled_posts (claimed_at) where status = 'publishing' |
listStale: publishing posts whose claim is older than the lease |
Partial, the same reason |
tracked_posts_user_id_created_at_idx |
tracked_posts (user_id, created_at desc) |
trackedPosts.listForUser: where user_id = $1 order by created_at desc |
Replaces tracked_posts_user_id_idx |
accounts_instagram_user_idx |
accounts ((data ->> 'igUserId')) where platform = 'instagram' |
automation_rules_for_ig_user: the accounts a webhook names |
An expression index, since the id lives in data |
accounts_identity_key |
accounts (user_id, platform, <identity>), unique |
Not a read path: it stops a second row for the same account | An expression on data, chosen per platform with case |
automation_rules_user_id_created_at_idx |
automation_rules (user_id, created_at, id) |
automationRules.listForUser |
|
automation_rules_enabled_account_idx |
automation_rules (account_id) where enabled |
The webhook's rule lookup | Partial: only enabled rules are read |
automation_comments_due_idx |
automation_comments (next_attempt_at) where status in ('queued', 'retry_wait') |
Due jobs by time; the queue health | Partial, so the growing history is left out |
automation_comments_due_account_idx |
automation_comments (account_id, next_attempt_at) where status in ('queued', 'retry_wait') |
claim_automation_comments: the accounts with waiting jobs (a loose index scan, one probe per account) and each account's oldest due jobs |
Partial; turn-taking reads a few rows per account however long one queue is |
automation_comments_claimed_idx |
automation_comments (claimed_at) where status in ('processing', 'sending') |
The claim's lease recovery | Partial |
automation_comments_awaiting_reply_idx |
automation_comments (ig_user_id, recipient_id, accepted_at) where gate_status = 'awaiting_reply' |
automation_followups_for_messages: a sender's waiting followers-only reply |
Partial: only replies that wait |
automation_comments_needs_review_idx |
automation_comments (review_checked_at nulls first, updated_at) where status = 'needs_review' |
automation_review_candidates, the queue health |
Partial |
automation_followups_due_idx, automation_followups_claimed_idx |
(account_id, next_attempt_at) and (claimed_at), partial like the comment ones |
claim_automation_followups, the expiry |
|
automation_followups_open_comment_key |
unique (automation_comment_id) where status in ('queued', 'processing', 'retry_wait', 'sending') |
One open follow-up per comment | Several messages from one person make one answer |
automation_followups_comment_id_idx |
(automation_comment_id, created_at) |
The cascade from a comment; the latest follow-up in the activity list | |
automation_public_replies_due_account_idx, automation_public_replies_claimed_idx |
(account_id, next_attempt_at) and (claimed_at), partial like the comment ones |
claim_automation_public_replies, the expiry, the queue health |
|
automation_public_replies_needs_review_idx |
(review_checked_at nulls first, updated_at) where status = 'needs_review' |
automation_public_review_candidates, the queue health |
Partial |
automation_public_replies_recipient_idx |
(account_id, from_id, media_id, created_at) where status in ('sending', 'accepted', 'needs_review') |
reserve_automation_public_reply: one public reply per person and post a day |
Partial: only replies that went out or may have |
automation_public_replies_user_created_idx |
(user_id, created_at) |
automation_stats |
|
automation_attempts_public_reply_idx |
automation_attempts (public_reply_id) where public_reply_id is not null |
The cascade from a public reply; the review's attempt | |
automation_comments_user_id_created_at_idx |
automation_comments (user_id, created_at desc) |
automationComments.listForUser, automation_stats |
|
automation_comments_rule_id_idx |
automation_comments (rule_id) where rule_id is not null |
Removing a rule (set null), stats by rule | |
automation_comments_created_at_idx |
automation_comments (created_at) |
purge_automation_comments: the oldest comments first, a batch at a time |
|
automation_attempts_*_started_at_idx |
(started_at), (user_id, started_at), (account_id, started_at), (rule_id, started_at), (account_id, recipient_id, started_at) |
reserve_automation_send: sends in the last hour per scope, and per recipient in the last day |
One per limit the reservation counts |
automation_attempts_comment_idx |
automation_attempts (automation_comment_id) |
Finishing and recovering one comment's attempt; the cascade | |
mcp_access_tokens_user_created_idx, mcp_client_grants_user_created_idx |
(user_id, created_at desc) |
Listing a person's tokens and approved clients in Settings | The token lookup by hash uses the unique token_hash key, and a client lookup the unique (user_id, client_id) key |
audit_events_user_id_created_at_idx, audit_events_created_at_idx |
(user_id, created_at desc), (created_at) |
Listing a user's events; the retention delete |
The primary keys serve every lookup by id, and settings is read through its (user_id, key) primary key.
Not indexed on purpose
| Column | Why not |
|---|---|
accounts.platform |
Only the daily token refresh filters on it (listByPlatform('instagram')), once a day over a small table. A scan is cheaper than keeping an index on every write |
scheduled_posts.account_id |
Only used when an account is removed (the cascade), which is rare |
tracked_posts.account_id |
Only used when an account is removed (the set null), which is rare |
No query uses window functions or ranking. The channel comparison ranks a handful of YouTube channels in TypeScript, so no ranking index applies.
Triggers and Functions
Every function sets search_path = '' and names its tables with the schema. Functions in public are exposed through the Data API, so each one revokes execute from public, anon and authenticated, and the merge functions grant it only to service_role. backend/tests/database/migrations.test.ts fails if a migration creates a function without the revoke.
| Function | Kind | What it does |
|---|---|---|
set_updated_at() |
plpgsql trigger | Sets updated_at = now(). A before update trigger on each table (<table>_set_updated_at) calls it, so no query has to set the column |
merge_account_data(p_id, p_user_id, p_patch) |
sql | data = data || p_patch on one account |
merge_scheduled_post(p_id, p_user_id, p_patch, p_status, p_scheduled_at, p_only_status) |
sql | Merges p_patch into data and sets status and scheduled_at when given, in one UPDATE. p_only_status limits the update to a post still in that status |
merge_tracked_post_data(p_id, p_user_id, p_patch) |
sql | data = coalesce(data, '{}') || p_patch on one tracked item |
| automation_rules_for_ig_user(p_ig_user_id) | sql, stable | The enabled rules of every connected account with that Instagram id whose stored token is present and not expired, oldest account first, with their private and public reply settings. A member whose access ended no longer receives the account's comments |
| store_automation_comments(p_comments) | plpgsql | Inserts a webhook's comments (on conflict do nothing) and then a public reply job for each one whose rule asks for one, joined to the stored comment while the same rule answers it, in one transaction. Returns the new comment ids and statuses and the new public reply ids |
| claim_automation_comments(p_limit, p_per_account, p_lease_seconds, p_ids) | plpgsql | Marks sending jobs past the lease needs_review (their attempts ambiguous), finds the accounts with waiting jobs, leaves out paused, cooling and limited ones, locks each account's oldest due jobs with for update skip locked as it reads them, and claims them with accounts taking turns. Returns each job with its rule's reply, its followers-only fields and the account's token; rule_enabled is false once the rule or its private reply is off |
| reserve_automation_send(p_id, p_attempt_id, p_global_limit, p_user_limit, p_account_limit, p_recipient_limit, p_message_digest) | plpgsql | Under pg_advisory_xact_lock, checks pause, cooldown, the recipient's daily replies and the hourly limits per rule, account, user and instance, counting private replies only; a full account gets limited_until. On success moves the job to sending and inserts the attempt with the digest. Returns outcome and, for a full scope, retry_at |
| finish_automation_send(p_id, p_attempt_id, p_status, p_outcome, ..., p_recipient_id, p_gate_status) | plpgsql | Settles a sending job (or an interrupted one) and its attempt in one transaction, keeps the recipient and starts a followers-only wait, and sets the account's cooldown or hold when asked. Returns false and changes nothing when the job was already settled |
| automation_followups_for_messages(p_ig_user_id, p_messages) | sql | Inserts a follow-up for each message whose sender has a followers-only reply waiting for them (accepted in the last 7 days); returns the new ids. Duplicates and a second open follow-up insert nothing |
| claim_automation_followups(p_limit, p_lease_seconds, p_ids) | plpgsql | The follow-up claim: lease recovery, paused and cooling accounts left out, turns between accounts. Returns each follow-up with the rule's texts, the comment's wait and the token |
| reserve_automation_followup(p_id, p_attempt_id, p_kind, p_message_digest) | plpgsql | Checks pause and cooldown, moves the follow-up to sending and inserts a followup attempt |
| finish_automation_followup(p_id, p_attempt_id, p_status, p_outcome, ..., p_gate_status, p_count_reminder) | plpgsql | Settles a follow-up and its attempt, updates the comment's wait and reminder count, and the account's cooldown or hold |
| automation_review_candidates(p_limit, p_settle_seconds, p_max_checks, p_recheck_seconds) | sql, stable | Needs review replies ready to be looked for in their conversation, with the attempt's time and digest and the token |
| record_automation_review(p_id, p_found, p_message_id, p_sent_at, p_note) | plpgsql | Found: the comment and its ambiguous attempt become accepted. Not found: one more check with the note. Changes only a comment still waiting for review |
| claim_automation_public_replies(p_limit, p_per_account, p_lease_seconds, p_ids) | plpgsql | The public reply claim: lease recovery (sending past the lease becomes needs_review, reason interrupted), paused accounts and public cooldowns and limits left out, for update skip locked, turns between accounts. Returns each reply with its text, whether the rule and its public reply are on, and the token |
| reserve_automation_public_reply(p_id, p_attempt_id, p_account_limit, p_message_digest) | plpgsql | Under its own advisory lock, checks pause, the public cooldown, one public reply per person and post a day, and the account's hourly public limit (marking public_limited_until); on success moves the reply to sending and inserts a public_reply attempt with the digest |
| finish_automation_public_reply(p_id, p_attempt_id, p_status, p_outcome, ..., p_reply_id, ...) | plpgsql | Settles a sending (or interrupted) public reply and its attempt, keeps the reply id, and sets the public cooldown or the account's hold |
| automation_public_review_candidates(p_limit, p_settle_seconds, p_max_checks, p_recheck_seconds) | sql, stable | Public replies of unknown outcome ready to be looked for, with the latest ambiguous attempt's time and digest, the token, and how many of its sends had no answer |
| record_automation_public_review(p_id, p_found, p_reply_id, p_sent_at, p_note, p_resend) | plpgsql | Found: the reply and its latest ambiguous attempt become accepted. p_resend: back to queued once, after the replies were read. Otherwise one more check with the note |
| expire_automation_jobs(p_limit) | sql | Expires up to p_limit overdue replies, follow-ups and public replies each, skipping rows a run holds; returns the three counts |
| purge_automation_comments(p_before, p_limit) | sql | Deletes up to p_limit comments created before p_before, oldest first; returns the count |
| automation_queue_health(p_lease_seconds) | sql, stable | Due and waiting jobs, the oldest due time, stale claims, replies needing review, follow-ups due, public replies due, stale and needing review, paused and limited accounts, each through its partial index |
| automation_stats(p_user_id, p_from, p_to, p_account_id, p_rule_id) | sql, stable | Comment counts by status, public reply counts by status and attempt counts by outcome for a period, as jsonb |
The merge functions return true when a row matched and null when none did. A null p_user_id is the cron form, which reaches any user's row; merge_tracked_post_data always needs a user. The merge is one statement, so two writers at the same moment (a token refresh and an analytics refresh on one account) never put an old value back.
data || patch is a shallow, top-level merge, like the spread it replaced. A field sent as undefined is dropped from the JSON request, so it keeps its stored value instead of being removed.
Repositories - Query Layer
All SQL operations live in backend/repositories/, one repository per table. Services receive repositories from backend/container.ts and call their methods - they never call supabase.from() directly. Every query runs through execute, rows or maybeRow in repositories/query.ts, which turn a failed query into a DatabaseError named after the operation (a 500 whose driver message goes to the log, not the client).
Updates to accounts.data and scheduled_posts go through the merge functions with db.rpc(...), never a read followed by a write. trackedPosts.patchDataForUser uses merge_tracked_post_data.
Available methods
Each repository is built with createXRepository(supabase) and returns types from shared/types/domain.ts. Methods named ...ForUser take the verified userId and always filter by it. The methods without a userId (listByPlatform, findById, updateById, listDue, listStale, claim) reach every user and are only for the cron jobs.
// Accounts (accountRepository.ts)
listForUser(userId): Promise<Account[]> // ordered by created_at, then id
listByPlatform(platform): Promise<Account[]> // cron only - filtered in SQL
findForUser(id, userId): Promise<Account | null>
findById(id): Promise<Account | null> // cron only
create(account: NewAccount, userId): Promise<void>
updateForUser(id, updates: Partial<Account>, userId): Promise<void> // merge_account_data; NotFoundError when no row matched
updateById(id, updates: Partial<Account>): Promise<void> // cron only - merge_account_data
deleteForUser(id, userId): Promise<void> // also removes the account's scheduled posts (cascade)
// Settings (settingsRepository.ts)
getForUser(userId): Promise<Settings>
saveForUser(settings: Settings, userId): Promise<void>
// Scheduled Posts (scheduledPostRepository.ts)
listForUser(userId): Promise<ScheduledPost[]>
findForUser(id, userId): Promise<ScheduledPost | null>
listDue(): Promise<ScheduledPost[]> // cron only - pending and overdue, every user
listStale(claimedBefore): Promise<ScheduledPost[]> // cron only - publishing with a claim older than the cutoff
claim(post): Promise<ScheduledPost | null> // cron only - the claimed post; null when another run won
create(post: ScheduledPost, userId): Promise<void>
updatePendingForUser(id, updates: Partial<ScheduledPost>, userId): Promise<boolean> // merge_scheduled_post with p_only_status 'pending'; false when the post is no longer pending
updateById(id, updates: Partial<ScheduledPost>): Promise<void> // cron only - merge_scheduled_post
deleteForUser(id, userId): Promise<boolean> // never deletes a 'publishing' post; false when nothing was deleted
deleteAllForUser(userId): Promise<{ deleted: number; kept: number }> // skips 'publishing' status posts
// Tracked Content (trackedPostRepository.ts)
listForUser(userId): Promise<TrackedPost[]>
findForUser(id, userId): Promise<TrackedPost | null>
create(post: NewTrackedPost, userId): Promise<void> // data goes in the same insert
updateForUser(id, updates: TrackedPostUpdates, userId): Promise<void>
patchDataForUser(id, patch: Record<string, unknown>, userId): Promise<void> // merge_tracked_post_data; NotFoundError when no row matched
deleteForUser(id, userId): Promise<void>
// Automation rules (automationRuleRepository.ts)
listForUser(userId) / findForUser(id, userId) / create(rule, userId) / updateForUser(id, changes, userId) / deleteForUser(id, userId)
listEnabledForInstagramUser(igUserId): Promise<WebhookRule[]> // webhook only - automation_rules_for_ig_user, every user
// Automation comments (automationCommentRepository.ts)
store(comments): Promise<StoredComments> // webhook only - store_automation_comments: comments and their public replies in one transaction; returns the new ones
claim(options) / reserve(id, attemptId, limits, digest?) / finish(id, attemptId, plan) / release(id, plan) / returnUnstarted(ids) // processor only, every user
listForUser(userId, filter) / findForUser(id, userId) / requeueForUser(id, userId) / releaseHeldForAccount(accountId, userId)
statsForUser(userId, filter): Promise<AutomationStats> // automation_stats
expireDue(limit) / reviewCandidates(options) / recordReview(id, result) / health(leaseSeconds) // reconciliation only, every user; expireDue also expires public replies
purgeCreatedBefore(cutoff, limit): Promise<number> // daily retention only, one batch per call
// Automation follow-ups (automationFollowupRepository.ts)
insertForMessages(igUserId, messages): Promise<string[]> // webhook only - automation_followups_for_messages
claim(options) / reserve(id, attemptId, kind, digest) / finish(id, attemptId, plan) / release(id, plan) / returnUnstarted(ids) // follow-up processor only, every user
requeueLatestForUser(commentRowId, userId): Promise<string | null>
// Automation public replies (automationPublicReplyRepository.ts)
claim(options) / reserve(id, attemptId, accountLimit, digest) / finish(id, attemptId, plan) / release(id, plan) / returnUnstarted(ids) // public reply processor only, every user
reviewCandidates(options) / recordReview(id, result) // reconciliation only, every user
requeueForUser(commentRowId, userId): Promise<string | null> / releaseHeldForAccount(accountId, userId)
// Automation account controls (automationAccountRepository.ts)
listForUser(userId) / findForUser(accountId, userId) / setPausedForUser(accountId, userId, paused) / markSubscribedForUser(accountId, userId, fields)
// Audit events (auditRepository.ts)
recordForUser(userId, event): Promise<void>
deleteCreatedBefore(cutoff): Promise<number> // daily retention only
Rules and controls are plain columns, so their repositories update columns directly; only data jsonb needs the merge functions.
The Redis analytics cache has its own repository, analyticsCacheRepository.ts; see docs/architecture/backend/CACHING.md.
Adding a New Table
See docs/features/NEW_DATABASE_TABLE.md for the full workflow: the migration, its rollback, the repository and the docs.