The schema behind listmonk, a self-hosted newsletter and mailing-list manager: subscribers and the lists they belong to, campaigns and their deliveries, media, bounces, and the users and roles that run it.
16 tables · 126 columns · 19 foreign keys · 5 domains | 2 errors · 7 warnings · 20 notes
Generated from listmonk@v6.2.0.sql on 2026-09-05.
Conventions are claims. Findings below are where the schema breaks them.
Without a primary key rows are not individually addressable: no safe UPDATE/DELETE of one row, no logical replication, ORMs misbehave, and duplicates are legal.
Fix Add a natural composite key or a surrogate id.
ALTER TABLE campaign_media ADD PRIMARY KEY (...);
Without a primary key rows are not individually addressable: no safe UPDATE/DELETE of one row, no logical replication, ORMs misbehave, and duplicates are legal.
Fix Add a natural composite key or a surrogate id.
ALTER TABLE settings ADD PRIMARY KEY (...);
PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on media must scan campaign_media to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to campaign_media maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often media rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.
Fix Add a partial index, scoped to the rows that have a value. media_id is nullable, so the NULL rows are not indexed at all and the write cost is a fraction of a full index, while the constraint check still uses it.
CREATE INDEX CONCURRENTLY idx_campaign_media_media_id ON campaign_media(media_id) WHERE media_id IS NOT NULL;
PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on templates must scan campaigns to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to campaigns maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often templates rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.
Fix Add a partial index, scoped to the rows that have a value. template_id is nullable, so the NULL rows are not indexed at all and the write cost is a fraction of a full index, while the constraint check still uses it.
CREATE INDEX CONCURRENTLY idx_campaigns_template_id ON campaigns(template_id) WHERE template_id IS NOT NULL;
PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on templates must scan campaigns to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to campaigns maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often templates rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.
Fix Add a partial index, scoped to the rows that have a value. archive_template_id is nullable, so the NULL rows are not indexed at all and the write cost is a fraction of a full index, while the constraint check still uses it.
CREATE INDEX CONCURRENTLY idx_campaigns_archive_template_id ON campaigns(archive_template_id) WHERE archive_template_id IS NOT NULL;
content_type is a short string that clearly takes a fixed set of values, but the database accepts anything. Typos become new states, and nobody can list the legal values without reading application code.
Fix Add a CHECK constraint (cheap, easy to evolve) or a lookup table if values need metadata. Avoid native ENUM types unless the set is truly frozen.
ALTER TABLE media ADD CONSTRAINT media_content_type_check CHECK (content_type IN (...));
PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on lists must scan roles to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to roles maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often lists rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.
Fix Add a partial index, scoped to the rows that have a value. list_id is nullable, so the NULL rows are not indexed at all and the write cost is a fraction of a full index, while the constraint check still uses it.
CREATE INDEX CONCURRENTLY idx_roles_list_id ON roles(list_id) WHERE list_id IS NOT NULL;
PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on roles must scan users to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to users maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often roles rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.
Fix Add an index on the FK column(s).
CREATE INDEX CONCURRENTLY idx_users_user_role_id ON users(user_role_id);
PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on roles must scan users to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to users maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often roles rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.
Fix Add a partial index, scoped to the rows that have a value. list_role_id is nullable, so the NULL rows are not indexed at all and the write cost is a fraction of a full index, while the constraint check still uses it.
CREATE INDEX CONCURRENTLY idx_users_list_role_id ON users(list_role_id) WHERE list_role_id IS NOT NULL;
bounces.campaign_id may be NULL, so the relationship to campaigns is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
campaign_lists.list_id may be NULL, so the relationship to lists is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
campaign_media.campaign_id may be NULL, so the relationship to campaigns is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
campaign_media.media_id may be NULL, so the relationship to media is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
campaign_media has several nullable FKs (campaign_id, media_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.
Fix Add a CHECK that exactly one is non-null, or restructure with a supertype.
ALTER TABLE campaign_media ADD CONSTRAINT campaign_media_one_target CHECK ((campaign_id IS NOT NULL)::int + (media_id IS NOT NULL)::int = 1);
campaign_views.subscriber_id may be NULL, so the relationship to subscribers is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
campaigns.template_id may be NULL, so the relationship to templates is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
campaigns.archive_template_id may be NULL, so the relationship to templates is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
campaigns has several nullable FKs (template_id, archive_template_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.
Fix Add a CHECK that exactly one is non-null, or restructure with a supertype.
ALTER TABLE campaigns ADD CONSTRAINT campaigns_one_target CHECK ((template_id IS NOT NULL)::int + (archive_template_id IS NOT NULL)::int = 1);
Any change to its key, its delete semantics, or its partitioning touches every dependent. Migrations on hub tables need the longest lock windows and the most careful rollout.
Fix Treat schema changes here as breaking changes with a written rollout plan.
link_clicks.campaign_id may be NULL, so the relationship to campaigns is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
link_clicks.subscriber_id may be NULL, so the relationship to subscribers is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
link_clicks has several nullable FKs (campaign_id, subscriber_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.
Fix Add a CHECK that exactly one is non-null, or restructure with a supertype.
ALTER TABLE link_clicks ADD CONSTRAINT link_clicks_one_target CHECK ((campaign_id IS NOT NULL)::int + (subscriber_id IS NOT NULL)::int = 1);
roles.parent_id may be NULL, so the relationship to roles is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
roles.list_id may be NULL, so the relationship to lists is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
roles has several nullable FKs (parent_id, list_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.
Fix Add a CHECK that exactly one is non-null, or restructure with a supertype.
ALTER TABLE roles ADD CONSTRAINT roles_one_target CHECK ((parent_id IS NOT NULL)::int + (list_id IS NOT NULL)::int = 1);
created_at stores wall-clock time with no zone. It reads back differently depending on the session's TimeZone, and DST transitions produce ambiguous values.
Fix Use TIMESTAMPTZ.
ALTER TABLE sessions ALTER COLUMN created_at TYPE timestamptz;
sessions references nothing and nothing references it. Either it is a staging/log table (fine, say so), or it is dead, or it is the seed of a second data model growing beside the first.
Fix Document its purpose or drop it.
settings references nothing and nothing references it. Either it is a staging/log table (fine, say so), or it is dead, or it is the seed of a second data model growing beside the first.
Fix Document its purpose or drop it.
users.list_role_id may be NULL, so the relationship to roles is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.
Fix Confirm the optionality is a domain rule; document it in the column comment.
16 tables · 19 foreign keys · 5 domains. Open the 3D explorer: rotate, zoom, click any table or relationship.
Subscribers, lists, the subscription rows that join them with a status, and the bounces recorded against a subscriber.
subscriber_lists.subscriber_id → subscribers.id — one subscribers, many subscriber_lists · required · ON DELETE CASCADE · indexedsubscriber_lists.list_id → lists.id — one lists, many subscriber_lists · required · ON DELETE CASCADE · indexedbounces.subscriber_id → subscribers.id — one subscribers, many bounces · required · ON DELETE CASCADE · indexedbounces.campaign_id → campaigns.id — one campaigns, many bounces · optional · ON DELETE SET NULL · indexed| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
uuid UK | uuid | NOT NULL | |||
email UK | text | NOT NULL | |||
name | text | NOT NULL | |||
attribs | jsonb | NOT NULL | '{}' | ||
status | subscriber_status | NOT NULL | 'enabled' | ||
created_at | timestamptz | null | now() | ||
updated_at | timestamptz | null | now() |
| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
uuid UK | uuid | NOT NULL | |||
name | text | NOT NULL | |||
type | list_type | NOT NULL | |||
optin | list_optin | NOT NULL | 'single' | ||
status | list_status | NOT NULL | 'active' | ||
tags | varchar(100)[] | null | |||
description | text | NOT NULL | '' | ||
created_at | timestamptz | null | now() | ||
updated_at | timestamptz | null | now() |
| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
subscriber_id PK | integer | NOT NULL | subscribers.id 1:N · CASCADE | ||
list_id PK | integer | NOT NULL | lists.id 1:N · CASCADE | ||
meta | jsonb | NOT NULL | '{}' | ||
status | subscription_status | NOT NULL | 'unconfirmed' | ||
created_at | timestamptz | null | now() | ||
updated_at | timestamptz | null | now() |
campaign_id — bounces.campaign_id may be NULL, so the relationship to campaigns is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
subscriber_id | integer | NOT NULL | subscribers.id 1:N · CASCADE | ||
campaign_id | integer | null | campaigns.id 1:N · SET NULL | ||
type | bounce_type | NOT NULL | 'hard' | ||
source | text | NOT NULL | '' | ||
meta | jsonb | NOT NULL | '{}' | ||
created_at | timestamptz | null | now() |
Campaigns, the lists they target, the templates they render with, and the views and link clicks they earn.
campaigns.template_id → templates.id — one templates, many campaigns · optional · ON DELETE SET NULL · not indexedcampaigns.archive_template_id → templates.id — one templates, many campaigns · optional · ON DELETE SET NULL · not indexedcampaign_lists.campaign_id → campaigns.id — one campaigns, many campaign_lists · required · ON DELETE CASCADE · indexedcampaign_lists.list_id → lists.id — one lists, many campaign_lists · optional · ON DELETE SET NULL · indexedcampaign_views.campaign_id → campaigns.id — one campaigns, many campaign_views · required · ON DELETE CASCADE · indexedcampaign_views.subscriber_id → subscribers.id — one subscribers, many campaign_views · optional · ON DELETE SET NULL · indexedlink_clicks.campaign_id → campaigns.id — one campaigns, many link_clicks · optional · ON DELETE CASCADE · indexedlink_clicks.link_id → links.id — one links, many link_clicks · required · ON DELETE CASCADE · indexedlink_clicks.subscriber_id → subscribers.id — one subscribers, many link_clicks · optional · ON DELETE SET NULL · indexedtemplate_id — PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on templates must scan campaigns to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to campaigns maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often templates rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.archive_template_id — PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on templates must scan campaigns to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to campaigns maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often templates rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.template_id — campaigns.template_id may be NULL, so the relationship to templates is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.archive_template_id — campaigns.archive_template_id may be NULL, so the relationship to templates is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.template_id, archive_template_id — campaigns has several nullable FKs (template_id, archive_template_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
uuid UK | uuid | NOT NULL | |||
name | text | NOT NULL | |||
subject | text | NOT NULL | |||
from_email | text | NOT NULL | |||
body | text | NOT NULL | |||
body_source | text | null | |||
altbody | text | null | |||
content_type | content_type | NOT NULL | 'richtext' | ||
send_at | timestamptz | null | |||
headers | jsonb | NOT NULL | '[]' | ||
attribs | jsonb | NOT NULL | '{}' | ||
status | campaign_status | NOT NULL | 'draft' | ||
tags | varchar(100)[] | null | |||
type | campaign_type | null | 'regular' | ||
messenger | text | NOT NULL | |||
template_id | integer | null | templates.id 1:N · SET NULL | ||
to_send | integer | NOT NULL | 0 | ||
sent | integer | NOT NULL | 0 | ||
max_subscriber_id | integer | NOT NULL | 0 | ||
last_subscriber_id | integer | NOT NULL | 0 | ||
archive | boolean | NOT NULL | FALSE | ||
archive_slug UK | text | null | |||
archive_template_id | integer | null | templates.id 1:N · SET NULL | ||
archive_meta | jsonb | NOT NULL | '{}' | ||
started_at | timestamptz | null | |||
created_at | timestamptz | null | now() | ||
updated_at | timestamptz | null | now() |
list_id — campaign_lists.list_id may be NULL, so the relationship to lists is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | bigserial | NOT NULL | |||
campaign_id | integer | NOT NULL | campaigns.id 1:N · CASCADE | ||
list_id | integer | null | lists.id 1:N · SET NULL | ||
list_name | text | NOT NULL | '' |
| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
name | text | NOT NULL | |||
type | template_type | NOT NULL | 'campaign' | ||
subject | text | NOT NULL | |||
body | text | NOT NULL | |||
body_source | text | null | |||
is_default | boolean | NOT NULL | FALSE | ||
created_at | timestamptz | null | now() | ||
updated_at | timestamptz | null | now() |
subscriber_id — campaign_views.subscriber_id may be NULL, so the relationship to subscribers is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | bigserial | NOT NULL | |||
campaign_id | integer | NOT NULL | campaigns.id 1:N · CASCADE | ||
subscriber_id | integer | null | subscribers.id 1:N · SET NULL | ||
created_at | timestamptz | null | now() |
| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
uuid UK | uuid | NOT NULL | |||
url UK | text | NOT NULL | |||
created_at | timestamptz | null | now() |
campaign_id — link_clicks.campaign_id may be NULL, so the relationship to campaigns is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.subscriber_id — link_clicks.subscriber_id may be NULL, so the relationship to subscribers is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.campaign_id, subscriber_id — link_clicks has several nullable FKs (campaign_id, subscriber_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | bigserial | NOT NULL | |||
campaign_id | integer | null | campaigns.id 1:N · CASCADE | ||
link_id | integer | NOT NULL | links.id 1:N · CASCADE | ||
subscriber_id | integer | null | subscribers.id 1:N · SET NULL | ||
created_at | timestamptz | null | now() |
Uploaded files and which campaigns embed them.
campaign_media.campaign_id → campaigns.id — one campaigns, many campaign_media · optional · ON DELETE CASCADE · indexedcampaign_media.media_id → media.id — one media, many campaign_media · optional · ON DELETE SET NULL · not indexedcontent_type — content_type is a short string that clearly takes a fixed set of values, but the database accepts anything. Typos become new states, and nobody can list the legal values without reading application code.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
uuid UK | uuid | NOT NULL | |||
provider | text | NOT NULL | '' | ||
filename | text | NOT NULL | |||
content_type | text | NOT NULL | 'application/octet-stream' | ||
thumb | text | NOT NULL | |||
meta | jsonb | NOT NULL | '{}' | ||
created_at | timestamptz | null | now() |
media_id — PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on media must scan campaign_media to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to campaign_media maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often media rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.campaign_id — campaign_media.campaign_id may be NULL, so the relationship to campaigns is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.media_id — campaign_media.media_id may be NULL, so the relationship to media is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.campaign_id, media_id — campaign_media has several nullable FKs (campaign_id, media_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
campaign_id | integer | null | campaigns.id 1:N · CASCADE | ||
media_id | integer | null | media.id 1:N · SET NULL | ||
filename | text | NOT NULL | '' |
Admin users, the roles that scope what they can do (including per-list roles), and their sessions.
users.user_role_id → roles.id — one roles, many users · required · ON DELETE RESTRICT · not indexedusers.list_role_id → roles.id — one roles, many users · optional · ON DELETE CASCADE · not indexedroles.parent_id → roles.id — one roles, many roles · optional · ON DELETE CASCADE · indexedroles.list_id → lists.id — one lists, many roles · optional · ON DELETE CASCADE · not indexeduser_role_id — PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on roles must scan users to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to users maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often roles rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.list_role_id — PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on roles must scan users to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to users maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often roles rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.list_role_id — users.list_role_id may be NULL, so the relationship to roles is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
username UK | text | NOT NULL | |||
password_login | boolean | NOT NULL | FALSE | ||
password | text | null | |||
email UK | text | NOT NULL | |||
name | text | NOT NULL | |||
avatar | text | null | |||
type | user_type | NOT NULL | 'user' | ||
user_role_id | integer | NOT NULL | roles.id 1:N · RESTRICT | ||
list_role_id | integer | null | roles.id 1:N · CASCADE | ||
status | user_status | NOT NULL | 'disabled' | ||
twofa_type | twofa_type | NOT NULL | 'none' | ||
twofa_key | text | null | |||
loggedin_at | timestamptz | null | |||
created_at | timestamptz | null | now() | ||
updated_at | timestamptz | null | now() |
list_id — PostgreSQL does not index FK columns automatically. Every DELETE/UPDATE on lists must scan roles to check the constraint, and every join from the parent side is a sequential scan. The scan cost grows with the child table forever, so this gets worse on its own.
An index is not free. Every write to roles maintains it — insert, update and delete alike — and it takes disk. Weigh that against how often lists rows are actually deleted or re-keyed — if the answer is never, the scan never happens and the index only costs.parent_id — roles.parent_id may be NULL, so the relationship to roles is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.list_id — roles.list_id may be NULL, so the relationship to lists is optional. That is legitimate (e.g. approved_by before approval) but often a modelling shrug: a row with no owner, or two nullable FKs that are secretly an either/or.parent_id, list_id — roles has several nullable FKs (parent_id, list_id) that look like an exclusive arc — a row should point at exactly one of them. Nothing stops zero or both.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | serial | NOT NULL | |||
type | role_type | NOT NULL | 'user' | ||
parent_id | integer | null | roles.id 1:N · CASCADE | ||
list_id | integer | null | lists.id 1:N · CASCADE | ||
permissions | text[] | NOT NULL | '{}' | ||
name | text | null | |||
created_at | timestamptz | null | now() | ||
updated_at | timestamptz | null | now() |
created_at — created_at stores wall-clock time with no zone. It reads back differently depending on the session's TimeZone, and DST transitions produce ambiguous values.sessions references nothing and nothing references it. Either it is a staging/log table (fine, say so), or it is dead, or it is the seed of a second data model growing beside the first.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
id PK | text | NOT NULL | |||
data | jsonb | NOT NULL | CAST('{}' AS jsonb) | ||
created_at | timestamp | NOT NULL | now() |
The key-value settings table.
No foreign keys in this domain.
settings references nothing and nothing references it. Either it is a staging/log table (fine, say so), or it is dead, or it is the seed of a second data model growing beside the first.| Column | Type | Null | Default | References | Notes |
|---|---|---|---|---|---|
key UK | text | NOT NULL | |||
value | jsonb | NOT NULL | '{}' | ||
updated_at | timestamptz | null | now() |