The lists tables
crm_lists and crm_list_members model member collections in the CRM. A list is either static — explicit member rows in the polymorphic join — or dynamic, where a filter_definition is evaluated at read time and nothing is stored. This page traces them top-down: where they are served from, how a list and its members come to be and are read, then their schema, relationships and rules.
System design
interactive Start from the top. The Lists UI never touches storage directly — it talks to the lists subdomain, which is the only thing that reads or writes crm_lists and crm_list_members, and which evaluates a dynamic list's rules instead of storing members. Click a node to trace what it connects to.
Data flow — a list and its members
interactiveHow a list lands and is read back, as a closed loop. A list is created with a type and a member_type; the path branches into static rows or dynamic rules; the static path writes a member, a trigger keeps the type honest, the count is cached, then the list is served — and the read closes back to the start. Click a stage to follow it.
Schema
interactive Two tables. crm_lists holds the list and, for dynamic lists, its rules; crm_list_members holds the polymorphic membership rows for static lists. Both are org-scoped. Pick a table; colour marks each column's kind, and clicking a column expands its category and any table it references.
One row per list per org. A static list owns member rows; a dynamic list owns rules. Both carry a cached count for the UI.
- primary key
Primary key.
- text
Display name.
- enum
static · dynamic. Decides materialised rows vs evaluated rules.
- text
person · company · mixed. Constrains what can belong.
- jsonb
Rules for dynamic lists. GIN-indexed.
- number
Cached count for the UI; recomputed on change.
- text
Freeform description (ENG-204).
- uuid
Set when a workflow built the list.
- timestamp
Audit timestamps.
Relationships
interactiveA list fans out to its members, and each member points polymorphically at a person or a company. Hover or click a table to trace its link and see its key columns.
crm_lists owns members; each member row points at a person XOR a company.
Indexes, RLS & triggers
modelThe database is storage only — no business logic. What it does enforce: the static / dynamic split, the polymorphic XOR on members, the cached count, and the tenant isolation that scopes every read.
A static list stores member rows in crm_list_members. A dynamic list stores no rows — its filter_definition is evaluated against companies / people at read time, backed by a GIN index on the jsonb rules.
Each member row sets person_id XOR company_id. The CHECK constraint enforces exactly one, and check_list_member_type_consistency() rejects a row whose target conflicts with the list member_type — together they keep member_type honest.
member_count is denormalised onto crm_lists so the UI can show a list size without a join. It is recomputed on every membership change.
Row-level security on every read: is_member_of_organization via the list foreign key. Superadmins bypass. Member rows inherit the scope of their parent list.
A dynamic list is a saved query, not a materialised set — its membership is computed from filter_definition at read time and nothing is stored. A static list is the opposite: it materialises member rows with provenance (added_via, added_by_user_id). The tables hold no rules of their own beyond the constraints, triggers and org-scoped RLS that keep both shapes honest.