Every analytics tool wants a subscription. And your data. I built Twiligent instead. YouTube stats. Instagram stats. One dashboard. Runs locally on your machine.
Guide: Adding a New Database Table
When You Need a New Table
Before creating a new table, consider whether the data fits the existing jsonb pattern:
- If it's platform-specific configuration for an account -> add fields to
accounts.datajsonb - If it's per-user settings -> use the
settingstable with a newkey - If it's a new entity with its own lifecycle and queryable status -> new table
Steps
Step 1: Define the Schema
Decide the column structure. Follow the existing conventions:
- Include
id text primary key, filled bynewRowId()frombackend/lib/ids.ts(a UUID) - Include
user_id uuid not null(FK toauth.users, on delete cascade) - Include
created_atandupdated_at(timestamptz not null default now()); theset_updated_at()trigger keepsupdated_at - Use
data jsonb not null default '{}'for flexible platform-specific fields, with a check that it is an object - Use dedicated columns for fields you'll query/filter on (status, timestamps, foreign keys)
- Allowed values are a CHECK constraint, never a Postgres enum (see
docs/architecture/database/SCHEMA.md) - A reference to an account is composite,
(account_id, user_id)->accounts (id, user_id), so a row can only name an account of the same user - Index only what a frequent query filters or orders on, in the order the query uses
Step 2: Write the Migration
The schema is versioned in supabase/migrations/ and applied with the Supabase CLI. Never create or change a table in the SQL editor or the Table Editor.
Keep every migration as small as possible: one database entity per file. A table file holds the table with its columns, keys and checks. Its browser access, each trigger, each index and each function get files of their own, and every file gets a rollback of the same size under the same name. A small file is easy to review, fails alone when it fails, and rolls back one thing. backend/tests/database/migrations.test.ts refuses a migration that creates more than one entity. Never split or edit a migration production has already recorded; write a new one instead.
Make every file with npm run db:new <verb>_<object>. The CLI puts the current UTC time in front of the name, and the CLI matches applied migrations by that version alone, so never type a version by hand or rename a file once it is applied. The name starts with what the file does to its entity: create, alter, add, index, revoke, check, replace, drop or rename. Every migration and rollback opens with one comment line, a plain sentence that says what the file does (-- adds an index that lists a user's drafts newest first); comments further down explain why. The migration test checks all three.
A new table is therefore four or more migrations, run in this order:
| File | Holds | Rollback |
|---|---|---|
create_content_drafts |
create table with columns, keys and checks |
drop table if exists |
revoke_browser_access_content_drafts |
enable row level security and revoke all ... from anon, authenticated |
disable row level security (new tables start without browser grants) |
add_content_drafts_updated_at_trigger |
The set_updated_at() trigger |
drop trigger if exists |
index_content_drafts_<purpose> |
One index, named for the query it serves | drop index if exists |
npm run db:new create_content_drafts
npm run db:new revoke_browser_access_content_drafts
npm run db:new add_content_drafts_updated_at_trigger
npm run db:new index_content_drafts_user_id_created_at
create_content_drafts:
-- creates content_drafts, which holds the drafts a user prepares before scheduling them
create table public.content_drafts (
id text primary key,
user_id uuid not null references auth.users (id) on delete cascade,
account_id text not null,
platform text not null,
status text not null default 'draft',
data jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint content_drafts_account_fkey foreign key (account_id, user_id)
references public.accounts (id, user_id) on delete cascade,
constraint content_drafts_status_check check (status in ('draft', 'ready', 'archived')),
constraint content_drafts_data_check check (jsonb_typeof(data) = 'object')
);
revoke_browser_access_content_drafts:
-- only the Worker's service role reads content_drafts; the anon key ships in every browser
alter table public.content_drafts enable row level security;
revoke all on table public.content_drafts from anon, authenticated;
add_content_drafts_updated_at_trigger:
-- set_updated_at() keeps updated_at, so no query has to set it
create trigger content_drafts_set_updated_at before update on public.content_drafts
for each row execute function public.set_updated_at();
index_content_drafts_user_id_created_at:
-- adds an index that lists a user's drafts newest first
create index content_drafts_user_id_created_at_idx on public.content_drafts (user_id, created_at desc);
Write the reverse of each in supabase/rollbacks/ with the same file name. backend/tests/database/migrations.test.ts fails when a migration has no rollback or an empty one. The table's rollback:
-- removes content_drafts and every draft in it
drop table if exists public.content_drafts;
The others are one statement each under their sentence: alter table public.content_drafts disable row level security;, drop trigger if exists content_drafts_set_updated_at on public.content_drafts; and drop index if exists public.content_drafts_user_id_created_at_idx;. Roll them back newest first, so the table goes last. When a rollback deletes data or must run after another one, its sentence says so (-- removes content_drafts and every draft in it; roll back content_draft_versions first).
If the repository updates data, add a merge function in its own migration (npm run db:new create_merge_content_draft_function), so an update is one statement and two writers never undo each other. Copy create_merge_scheduled_post_function.sql. A function in public is exposed through the Data API, so it always revokes execute from the browser roles and grants it only to the service role; the migration test enforces the revoke:
-- creates merge_content_draft, which merges changes into a draft in one step so two writers never undo each other
create or replace function public.merge_content_draft(p_id text, p_user_id uuid, p_patch jsonb, p_status text default null)
returns boolean
language sql
set search_path = ''
as $$
update public.content_drafts
set data = data || p_patch,
status = coalesce(p_status, status)
where id = p_id and user_id = p_user_id
returning true;
$$;
-- functions in public are exposed through the Data API, so only the service role may call it
revoke execute on function public.merge_content_draft(text, uuid, jsonb, text) from public, anon, authenticated;
grant execute on function public.merge_content_draft(text, uuid, jsonb, text) to service_role;
Its rollback is drop function if exists public.merge_content_draft(text, uuid, jsonb, text);.
Step 3: Apply the Migration
- Run
npm test --workspace backend. The migration test checks the file names, versions and opening sentences, the rollback pairs and the function grants. Until step 6 it also reports that the schema tree lacks the new objects. - Link the CLI to the project once:
npx supabase link --project-ref <ref>. It needsSUPABASE_ACCESS_TOKENin the environment ornpx supabase login. - Run
npm run db:checkand read the list of migrations it would apply. - Run
npm run db:pushbefore merging the code that needs the table, because merging deploys the Worker at once. - Run
npm run db:statusand check that the new version is in both the Local and Remote columns. - Run
npm run db:schemaand commit the changed files insupabase/schemas/with the migrations. The tree is generated from the database, one file per table (with its checks, indexes, trigger and grants) and per function, so a reviewer sees the table as it now stands. Never edit it by hand.
A new instance gets the table from the same npm run db:push; there is no setup SQL to update in the README or the docs site. To undo the change, see Rolling Back a Database Change in docs/workflows/DEPLOYMENT.md.
Step 4: Add the Types
Add the entity as the API returns it to shared/types/domain.ts (types only, no runtime code). Status columns become a union of string literals, never an enum:
// shared/types/domain.ts
export type DraftStatus = 'draft' | 'ready' | 'archived';
export interface ContentDraft {
id: string;
accountId: string;
platform: Platform;
status: DraftStatus;
createdAt: string;
updatedAt: string;
caption?: string;
}
export type ContentDraftUpdate = Partial<Omit<ContentDraft, 'id' | 'accountId' | 'platform' | 'createdAt' | 'updatedAt'>>;
The raw row shape stays private to the table's repository, next to the methods that read it.
Step 5: Add a Repository
Create backend/repositories/contentDraftRepository.ts. Follow the existing repositories (trackedPostRepository.ts, scheduledPostRepository.ts) exactly: an interface with one method per query, a factory that takes the Supabase client, user-scoped methods that end in ForUser and filter by user_id, and every query run through rows, maybeRow or execute from query.ts so a failure becomes a DatabaseError.
// backend/repositories/contentDraftRepository.ts
import { execute, maybeRow, rows } from './query.ts';
import type { SupabaseClient } from '../types/app.ts';
import type { ContentDraft, ContentDraftUpdate, DraftStatus, Platform } from '../../shared/types/domain.ts';
interface ContentDraftRow {
id: string;
account_id: string;
platform: Platform;
status: DraftStatus;
created_at: string;
updated_at: string;
data: Partial<ContentDraft> | null;
}
const COLUMNS = 'id, account_id, platform, status, created_at, updated_at, data';
function toDraft(row: ContentDraftRow): ContentDraft {
return {
...(row.data || {}),
id: row.id,
accountId: row.account_id,
platform: row.platform,
status: row.status,
createdAt: row.created_at,
updatedAt: row.updated_at,
};
}
// every method is scoped to the verified user
export interface ContentDraftRepository {
listForUser(userId: string): Promise<ContentDraft[]>;
findForUser(id: string, userId: string): Promise<ContentDraft | null>;
create(draft: ContentDraft, userId: string): Promise<void>;
updateForUser(id: string, updates: ContentDraftUpdate, userId: string): Promise<void>;
deleteForUser(id: string, userId: string): Promise<void>;
}
export function createContentDraftRepository(db: SupabaseClient): ContentDraftRepository {
async function findForUser(id: string, userId: string): Promise<ContentDraft | null> {
const row = await maybeRow<ContentDraftRow>('contentDrafts.findForUser', () => db
.from('content_drafts')
.select(COLUMNS)
.eq('id', id)
.eq('user_id', userId)
.maybeSingle());
return row ? toDraft(row) : null;
}
return {
async listForUser(userId) {
const found = await rows<ContentDraftRow>('contentDrafts.listForUser', () => db
.from('content_drafts')
.select(COLUMNS)
.eq('user_id', userId)
.order('created_at', { ascending: false }));
return found.map(toDraft);
},
findForUser,
async create(draft, userId) {
const { id, accountId, platform, status, createdAt: _c, updatedAt: _u, ...rest } = draft;
await execute('contentDrafts.create', () => db.from('content_drafts').insert({
id,
user_id: userId,
account_id: accountId,
platform,
status,
data: rest,
}));
},
// one atomic merge in the database, so fields the update does not name are kept
async updateForUser(id, updates, userId) {
const { status, ...patch } = updates;
await execute('contentDrafts.update', () => db.rpc('merge_content_draft', { p_id: id, p_user_id: userId, p_patch: patch, p_status: status ?? null }));
},
async deleteForUser(id, userId) {
await execute('contentDrafts.delete', () => db.from('content_drafts').delete().eq('id', id).eq('user_id', userId));
},
};
}
Whether a missing draft is a NotFoundError is the service's decision: it calls findForUser first and throws. Only add a method without a userId if a cron job needs to scan every user, name it without the ForUser suffix (listByPlatform, findById, updateById), and filter in SQL rather than loading every row and filtering in TypeScript. New ids come from newRowId() in backend/lib/ids.ts; tests pin them with mockRowIds() from backend/tests/fixtures/rowIds.ts.
Build the repository in createServices() in backend/container.ts (const drafts = createContentDraftRepository(clients.supabase);) and pass it to the services that need it. Add a test in backend/tests/repositories/contentDraftRepository.test.ts against the fake Supabase client (see accountRepository.test.ts), then run npm run typecheck from the root.
Step 6: Document the Schema
Update docs/architecture/database/SCHEMA.md with:
- Table name and purpose
- All columns with types, constraints, and descriptions
- The constraints, foreign keys and their delete actions
- The
datajsonb shape - Each index with the query it serves, and the merge function if there is one
Step 7: Wire Up the Feature
Follow the guides for:
docs/features/NEW_API_ENDPOINT.md- add the validator, service, controller, route table and frontendapi.tsmethodsdocs/features/NEW_REACT_PAGE.md- add frontend page
Naming Conventions for New Tables
- Table names:
snake_case, plural -content_drafts,webhook_configs - All tables must have
user_id uuid NOT NULL - If table relates to an account: also include
account_id text NOT NULLand the composite foreign key toaccounts (id, user_id) - Status columns: text with a CHECK constraint listing the values (
<table>_status_check) - Timestamp columns:
timestamptzwithDEFAULT now(); every table hascreated_atandupdated_atwith theset_updated_at()trigger - Migration files: named for the change, verb first (
create_content_drafts,index_content_drafts_list); seedocs/guidelines/NAMING.md - jsonb data column: always name it
data(consistent with existing tables)