| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT · required | primary key → users.id | |
granted_at | TEXT · required |
Data model
Every table, generated from the migrations.
Generated by applying all 47 migrations in packages/core/migrations to an in-memory SQLite, the same SQL D1 runs, and reading the result: 87 tables and views (2 views), 156 triggers. Each entry shows the columns with their keys and checks, the rules over whole rows (composite keys, unique indexes, table checks) and the triggers. A table's purpose and its columns' notes are the comments written above them in the migrations; 37 tables have no such comment, so no purpose line is shown for them.
How the schema holds its rules
Nearly every table is STRICT, ids are text, and times are ISO 8601 text in UTC. Every chamber's rows carry its chamber id, and composite foreign keys such as (chamber_id, committee_id) → units (chamber_id, id) make it impossible for a row to point into another chamber.
Because a command reads, decides in code and then writes in one batch, the database itself enforces what must never happen, whatever raced in between. Triggers refuse impossible states: a membership moved to another branch, a position held by someone who is not an active member, an event moved to another unit, a public wave on an event that does not reach the whole chamber. History tables are append-only: triggers refuse any update or delete on application_transitions, audit_entry_units, audit_log, data_request_transitions, membership_transitions, points_entries, tier_transitions. A command maps the message a trigger raises to a refusal code.
The *_guard tables hold no data. A command adds an insert into one to its batch only when what it read has since changed (a photo replaced meanwhile, a member who now holds a higher admin right), and the insert fails on a named CHECK constraint, or on a trigger added later for a further case, named after the refusal the command's onConflict maps it to. So a stale batch fails as a whole and is answered with that refusal.
| Guard table | Named constraints | Triggers |
|---|---|---|
batch_guard | holds_admin_role import_gone | batch_guard_board_post_changed |
chamber_logo_guard | brand_logo_changed | |
event_photo_guard | event_photo_changed event_photo_not_allowed | |
event_staff_guard | event_staff_removed | |
higher_admin_guard | higher_admin | |
profile_photo_guard | photo_changed |
Domains
- Accounts and sessions6
- Chambers and structure4
- Applications and membership5
- Notices and delivery4
- Positions2
- Tiers and points4
- Visibility2
- Data requests and erasure3
- News and the digest12
- Member import3
- Audit and operations4
- Brands4
- Events, registration and check-in11
- Profiles, media and the directory10
- Matching, introductions and the board10
- Event photos3
Accounts and sessions
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
email | TEXT · required | unique check email = lower(trim(email)) AND length(email) BETWEEN 3 AND 254 | |
created_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
user_id | TEXT · required | → users.id | |
notice_version | TEXT · required | check length(notice_version) BETWEEN 1 AND 32 | |
locale | TEXT · required | ||
accepted_at | TEXT · required |
- unique
(user_id, notice_version)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
code_id | TEXT · required | primary key → login_codes.id | |
used_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
email | TEXT · required | ||
code_hash | TEXT · required | ||
attempts | INTEGER · required | check attempts >= 0 | Default 0. |
created_at | TEXT · required | ||
expires_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
user_id | TEXT · required | → users.id | |
token_hash | TEXT · required | unique | |
created_at | TEXT · required | ||
rotated_at | TEXT · required | ||
expires_at | TEXT · required | ||
revoked_at | TEXT · may be null |
Chambers and structure
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
slug | TEXT · required | unique check slug = lower(slug) AND length(slug) BETWEEN 2 AND 64 | |
name | TEXT · required | check length(trim(name)) BETWEEN 1 AND 160 | |
created_by | TEXT · required | → users.id | |
created_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
unit_id | TEXT · required | ||
user_id | TEXT · required | → users.id | |
role | TEXT · required | check role IN ('chamber_admin', 'branch_admin', 'exco_admin') | |
granted_by | TEXT · required | → users.id | |
granted_at | TEXT · required | ||
grant_reason | TEXT · required | check length(trim(grant_reason)) BETWEEN 1 AND 500 | |
revoked_by | TEXT · may be null | → users.id | |
revoked_at | TEXT · may be null | ||
revoke_reason | TEXT · may be null |
(chamber_id, unit_id)→units (chamber_id, id)- unique index
admin_grants_one_active:(unit_id, user_id) where revoked_at IS NULL - check:
(revoked_at IS NULL) = (revoked_by IS NULL) AND (revoked_at IS NULL) = (revoke_reason IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
admin_grants_role_fits_unit | before insert | the admin role does not fit the unit |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
kind | TEXT · required | check kind IN ('chamber', 'branch', 'committee') | |
parent_id | TEXT · may be null | ||
name | TEXT · required | check length(trim(name)) BETWEEN 1 AND 160 | |
created_at | TEXT · required |
(chamber_id, parent_id)→units (chamber_id, id)- unique index
units_sibling_name:(chamber_id, parent_id, name) where parent_id IS NOT NULL - unique
(chamber_id, id)
| Trigger | On | Refuses with |
|---|---|---|
units_fixed_shape | before update of id, chamber_id, kind, parent_id | a unit cannot be moved or change kind |
units_shape | before insert | a chamber unit is the root of its own chamber |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
host | TEXT · required | primary key check host = lower(host) AND length(host) BETWEEN 1 AND 253 AND host NOT LIKE '%:%' AND host NOT LIKE '% %' | |
chamber_id | TEXT · required | → chambers.id | |
created_at | TEXT · required | ||
created_by | TEXT · required | → users.id |
Applications and membership
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
application_id | TEXT · required | → applications.id | |
version | INTEGER · required | check version >= 0 | |
from_status | TEXT · may be null | ||
to_status | TEXT · required | ||
actor_user_id | TEXT · required | → users.id | |
reason | TEXT · may be null | ||
occurred_at | TEXT · required | ||
operation_id | TEXT · may be null | Every transition names the operation that made it, as the history of an application, a membership, a committee place, a tier or a data request is meant to. The executor's operation id is written in the same batch as the transition; rows made before this migration, and by scheduled work, have none. |
- unique
(application_id, version)
| Trigger | On | Refuses with |
|---|---|---|
application_transitions_no_delete | before delete | application_transitions is append-only |
application_transitions_no_update | before update | application_transitions is append-only |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
committee_id | TEXT · required | ||
user_id | TEXT · required | → users.id | |
branch_id | TEXT · may be null | The branch the applicant chose. Required only when a first membership starts in a national committee. | |
full_name | TEXT · required | check length(trim(full_name)) BETWEEN 1 AND 160 | |
phone | TEXT · required | check length(trim(phone)) BETWEEN 6 AND 32 | |
company | TEXT · required | check length(trim(company)) BETWEEN 1 AND 160 | |
position | TEXT · required | check length(trim(position)) BETWEEN 1 AND 120 | |
industry | TEXT · required | check length(trim(industry)) BETWEEN 1 AND 120 | |
referrer_email | TEXT · may be null | ||
locale | TEXT · required | check locale IN ('zh', 'en') | The language the applicant used, so messages about the decision reach them in it. |
status | TEXT · required | check status IN ('submitted', 'approved', 'declined', 'withdrawn') | |
submitted_at | TEXT · required | ||
decided_at | TEXT · may be null | ||
decided_by | TEXT · may be null | → users.id | |
decision_note | TEXT · may be null | check decision_note IS NULL OR length(decision_note) <= 500 | |
version | INTEGER · required | check version >= 0 | Default 0. |
(chamber_id, branch_id)→units (chamber_id, id)(chamber_id, committee_id)→units (chamber_id, id)- unique index
applications_one_open:(committee_id, user_id) where status = 'submitted' - check:
(status = 'submitted') = (decided_at IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
applications_committee_is_committee | before insert | an application is made to a committee |
member_search_application_insert | after insert | keeps derived rows in step |
member_search_application_update | after update | keeps derived rows in step |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
membership_id | TEXT · required | ||
committee_id | TEXT · required | ||
user_id | TEXT · required | → users.id | |
status | TEXT · required | check status IN ('active', 'ended') | |
application_id | TEXT · may be null | → applications.id | |
joined_at | TEXT · required | ||
ended_at | TEXT · may be null | ||
ended_by | TEXT · may be null | → users.id | |
end_reason | TEXT · may be null | ||
ended_how | TEXT · may be null | check ended_how IS NULL OR ended_how IN ('left', 'removed', 'membership_ended') | How a place in a committee ended: the member left, an admin removed them, or their membership ended. |
joined_operation_id | TEXT · may be null | A committee place keeps its current row only: the operation that opened it, and the one that ended it. | |
ended_operation_id | TEXT · may be null |
(chamber_id, committee_id)→units (chamber_id, id)(chamber_id, membership_id)→memberships (chamber_id, id)- unique index
committee_memberships_one_active:(committee_id, user_id) where status = 'active' - check:
(status = 'active') = (ended_at IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
committee_memberships_active_member | before insert | membership_not_active: a committee place is only for an active membership |
committee_memberships_ended_final | before update | committee_membership_ended: this committee membership has already ended |
committee_memberships_keep_one | after update of status | last_committee: a member must keep at least one committee |
committee_memberships_no_delete | before delete | committee memberships are never deleted |
committee_memberships_only_end | before update | a committee membership can only be ended, saying who ended it, when and how |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
membership_id | TEXT · required | → memberships.id | |
version | INTEGER · required | check version >= 0 | |
from_status | TEXT · may be null | ||
to_status | TEXT · required | ||
actor_user_id | TEXT · required | → users.id | |
reason | TEXT · may be null | ||
occurred_at | TEXT · required | ||
operation_id | TEXT · may be null |
- unique
(membership_id, version)
| Trigger | On | Refuses with |
|---|---|---|
membership_transitions_no_delete | before delete | membership_transitions is append-only |
membership_transitions_no_update | before update | membership_transitions is append-only |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
branch_id | TEXT · required | ||
status | TEXT · required | check status IN ('active', 'suspended', 'resigned', 'terminated') | |
joined_at | TEXT · required | ||
ended_at | TEXT · may be null | ||
version | INTEGER · required | check version >= 0 | Default 0. |
tier_id | TEXT · may be null | → tiers.id | A member's current tier, and the version every points or tier change bumps. |
standing_version | INTEGER · required | Default 0. |
(chamber_id, branch_id)→units (chamber_id, id)- unique index
memberships_one_open:(chamber_id, user_id) where status IN ('active', 'suspended') - unique
(chamber_id, id) - check:
(status IN ('active', 'suspended')) = (ended_at IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
matching_membership_ended | after update of status | keeps derived rows in step |
memberships_allowed_moves | before update of status | membership_move: that change of membership is not allowed |
memberships_branch_is_branch | before insert | a membership belongs to a branch |
memberships_final | before update | membership_final: an ended membership cannot change |
memberships_fixed_branch | before update of chamber_id, user_id, branch_id | a membership cannot change chamber, person or branch |
memberships_no_delete | before delete | memberships are never deleted |
memberships_standing_version | before update of standing_version | standing changed: member |
memberships_start_tier | after insert | keeps derived rows in step |
memberships_tier_in_chamber | before update of tier_id | a member |
memberships_tier_not_retired | before update of tier_id | standing changed: tier retired |
Notices and delivery
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
user_id | TEXT · required | → users.id | |
chamber_id | TEXT · may be null | → chambers.id | |
kind | TEXT · required | check length(kind) BETWEEN 1 AND 64 | |
params_json | TEXT · required | Default '{}'. | |
created_at | TEXT · required | ||
read_at | TEXT · may be null |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
channel | TEXT · required | check channel IN ('email', 'push') | |
payload_json | TEXT · required | ||
status | TEXT · required | check status IN ('pending', 'sending', 'sent', 'failed') | |
attempts | INTEGER · required | check attempts >= 0 | Default 0. |
created_at | TEXT · required | ||
sent_at | TEXT · may be null | ||
last_error | TEXT · may be null | ||
chamber_id | TEXT · may be null | Email queued by a chamber is dropped when that chamber deletes the person's data, so each outbox row records the chamber it was sent for (null for platform email). Email queued before this column existed is marked '*': its chamber is unknown, so any chamber's deletion of the person's data reaches it. |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT · required | primary key → users.id | |
topic | TEXT · required | primary key check length(topic) BETWEEN 1 AND 64 | |
channel | TEXT · required | primary key check channel IN ('push', 'email', 'inbox') | |
enabled | INTEGER · required | check enabled IN (0, 1) | |
updated_at | TEXT · required |
- check:
NOT (channel = 'email' AND enabled = 0 AND topic IN ('applicationDecided', 'membershipChanged', 'dataRequestDecided'))
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
user_id | TEXT · required | → users.id | |
session_id | TEXT · may be null | ||
endpoint | TEXT · required | unique check substr(endpoint, 1, 8) = 'https://' AND length(endpoint) <= 2048 | |
p256dh | TEXT · required | check length(p256dh) = 87 | |
auth | TEXT · required | check length(auth) = 22 | |
locale | TEXT · required | check locale IN ('zh', 'en') | The language the person last used the app in on this device; notices are written in it. |
label | TEXT · required | check length(label) BETWEEN 1 AND 80 | A short description of the device, such as "Safari on iPhone", never the full user agent. |
created_at | TEXT · required | ||
updated_at | TEXT · required |
Positions
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
membership_id | TEXT · required | ||
committee_id | TEXT · required | ||
user_id | TEXT · required | → users.id | |
title | TEXT · required | check length(trim(title)) BETWEEN 1 AND 80 AND title = trim(title) | Free text in whatever language the chamber uses, such as 主席 or Chairman. |
starts_on | TEXT · required | check coalesce(date(starts_on) = starts_on, 0) | Calendar dates (YYYY-MM-DD) in the chamber's time zone. The end date is the first day the position no longer counts. |
ends_on | TEXT · may be null | check ends_on IS NULL OR coalesce(date(ends_on) = ends_on, 0) | |
recorded_by | TEXT · required | → users.id | |
recorded_at | TEXT · required | ||
ended_by | TEXT · may be null | → users.id | |
ended_at | TEXT · may be null | ||
end_reason | TEXT · may be null | check end_reason IS NULL OR length(trim(end_reason)) BETWEEN 1 AND 500 | |
ended_how | TEXT · may be null | check ended_how IS NULL OR ended_how IN ('left_committee', 'removed_from_committee', 'membership_ended') |
(chamber_id, committee_id)→units (chamber_id, id)(chamber_id, membership_id)→memberships (chamber_id, id)- check:
ends_on IS NULL OR ends_on >= starts_on - check:
(ended_at IS NULL) = (ended_by IS NULL) AND (ended_at IS NULL) = (end_reason IS NULL) - check:
ended_at IS NULL OR ends_on IS NOT NULL
| Trigger | On | Refuses with |
|---|---|---|
positions_end_once | before update of ends_on, ended_by, ended_at, end_reason | position already ended |
positions_fit | before insert | a position is held in a committee |
positions_fixed | before update of id, chamber_id, membership_id, committee_id, user_id, title, starts_on, recorded_by, recorded_at | a position cannot be changed, only ended |
positions_no_delete | before delete | positions are never deleted |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
refusal | TEXT · required |
- check
higher_admin:refusal IS NULL
Tiers and points
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
membership_id | TEXT · required | ||
source | TEXT · required | check source IN ('attendance', 'organising', 'referral', 'contribution', 'correction', 'manual') | |
amount | INTEGER · required | check amount <> 0 AND amount BETWEEN -1000000 AND 1000000 | |
reason | TEXT · may be null | check reason IS NULL OR length(trim(reason)) BETWEEN 1 AND 500 | |
corrects_id | TEXT · may be null | → points_entries.id | |
cause_kind | TEXT · may be null | What caused an automatic award, such as an event attended or the application of a person referred. | |
cause_id | TEXT · may be null | ||
actor_user_id | TEXT · may be null | → users.id | |
occurred_at | TEXT · required |
(chamber_id, membership_id)→memberships (chamber_id, id)- unique index
points_entries_once_per_cause:(membership_id, source, cause_kind, cause_id) where cause_id IS NOT NULL - check:
(source = 'correction') = (corrects_id IS NOT NULL) - check:
source = 'correction' OR amount > 0 - check:
source NOT IN ('correction', 'contribution', 'manual') OR reason IS NOT NULL - check:
(cause_kind IS NULL) = (cause_id IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
points_entries_correction_same_member | before insert | a correction refers to an entry of the same member |
points_entries_no_delete | before delete | points_entries is append-only |
points_entries_no_update | before update | points_entries is append-only |
points_entries_referral_once | before insert | standing changed: referral already paid |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
attendance | INTEGER · required | check attendance BETWEEN 0 AND 100000 | |
organising | INTEGER · required | check organising BETWEEN 0 AND 100000 | |
referral | INTEGER · required | check referral BETWEEN 0 AND 100000 | |
ladder_version | INTEGER · required | Default 0. | |
ladder_check | INTEGER · required | Default 0. | |
points_version | INTEGER · required | Default 0. | |
points_check | INTEGER · required | Default 0. | |
updated_at | TEXT · required | ||
updated_by | TEXT · may be null | → users.id |
| Trigger | On | Refuses with |
|---|---|---|
points_settings_ladder_check | before update of ladder_check | standing changed: ladder |
points_settings_ladder_version | before update of ladder_version | standing changed: ladder |
points_settings_points_check | before update of points_check | standing changed: points |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
membership_id | TEXT · required | ||
from_tier_id | TEXT · may be null | → tiers.id | |
to_tier_id | TEXT · required | → tiers.id | |
kind | TEXT · required | check kind IN ('automatic', 'manual') | |
actor_user_id | TEXT · may be null | → users.id | |
reason | TEXT · may be null | check reason IS NULL OR length(trim(reason)) BETWEEN 1 AND 500 | |
occurred_at | TEXT · required | ||
operation_id | TEXT · may be null |
(chamber_id, membership_id)→memberships (chamber_id, id)- check:
kind <> 'manual' OR (reason IS NOT NULL AND actor_user_id IS NOT NULL)
| Trigger | On | Refuses with |
|---|---|---|
tier_transitions_no_delete | before delete | tier_transitions is append-only |
tier_transitions_no_update | before update | tier_transitions is append-only |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
name_zh | TEXT · required | check length(trim(name_zh)) BETWEEN 1 AND 60 | |
name_en | TEXT · required | check length(trim(name_en)) BETWEEN 1 AND 60 | |
threshold | INTEGER · required | check threshold >= 0 | |
created_at | TEXT · required | ||
retired_at | TEXT · may be null |
- unique
(chamber_id, id)
| Trigger | On | Refuses with |
|---|---|---|
tiers_fixed_chamber | before update of chamber_id | a tier cannot move to another chamber |
tiers_no_delete | before delete | tiers are retired, not deleted |
tiers_retire_unheld | before update of retired_at | standing changed: tier held |
Visibility
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | → chambers.id | |
item | TEXT · required | ||
audience | TEXT · required | check audience IN ('public', 'friend', 'office_holder', 'tier') | |
tier_id | TEXT · may be null | → tiers.id | |
allowed | INTEGER · required | check allowed IN (0, 1) |
- unique index
visibility_rules_one:(chamber_id, item, audience, coalesce(tier_id, '')) - check:
(audience = 'tier') = (tier_id IS NOT NULL)
| Trigger | On | Refuses with |
|---|---|---|
visibility_rules_known_item | before insert | unknown gateable item |
visibility_rules_no_update | before update | visibility rules are replaced, not updated |
visibility_rules_tier_in_chamber | before insert | a visibility tier belongs to its chamber |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
version | INTEGER · required | check version >= 1 | |
updated_at | TEXT · required | ||
updated_by | TEXT · required | → users.id |
| Trigger | On | Refuses with |
|---|---|---|
visibility_settings_version | before update of version | visibility changed |
Data requests and erasure
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
request_id | TEXT · required | → data_requests.id | |
version | INTEGER · required | check version >= 0 | |
from_status | TEXT · may be null | ||
to_status | TEXT · required | check to_status IN ('received', 'completed', 'refused') | |
actor_user_id | TEXT · required | → users.id | |
note | TEXT · may be null | ||
occurred_at | TEXT · required | ||
operation_id | TEXT · may be null |
- unique
(request_id, version)
| Trigger | On | Refuses with |
|---|---|---|
data_request_transitions_no_delete | before delete | data_request_transitions is append-only |
data_request_transitions_no_update | before update | data_request_transitions is append-only |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
user_id | TEXT · required | → users.id | |
kind | TEXT · required | check kind IN ('chamber', 'account') | |
chamber_id | TEXT · may be null | → chambers.id | The chamber asked to delete what it holds. Null for an account erasure. |
status | TEXT · required | check status IN ('received', 'completed', 'refused') | |
received_at | TEXT · required | ||
due_at | TEXT · required | ||
decided_at | TEXT · may be null | ||
decided_by | TEXT · may be null | → users.id | |
outcome_note | TEXT · may be null | check outcome_note IS NULL OR length(trim(outcome_note)) BETWEEN 1 AND 500 | What the person deciding wrote: why it was refused, or anything about how it was completed. |
version | INTEGER · required | check version >= 0 | Default 0. |
- unique index
data_requests_one_open:(user_id, kind, coalesce(chamber_id, '')) where status = 'received' - check:
(kind = 'chamber') = (chamber_id IS NOT NULL) - check:
due_at > received_at - check:
(status = 'received') = (decided_at IS NULL) - check:
(decided_at IS NULL) = (decided_by IS NULL) - check:
status <> 'refused' OR outcome_note IS NOT NULL
| Trigger | On | Refuses with |
|---|---|---|
data_requests_final | before update | data_request_decided: this request has already been decided |
data_requests_fixed | before update of id, user_id, kind, chamber_id, received_at, due_at | a data request cannot change who asked, about what or when |
data_requests_move | before update of status | data_request_decided: a decision takes the next version |
data_requests_no_delete | before delete | data requests are never deleted |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
refusal | TEXT · required |
- check
holds_admin_role:refusal <> 'holds_admin_role' - check
import_gone:refusal <> 'import_gone'
| Trigger | On | Refuses with |
|---|---|---|
batch_guard_board_post_changed | before insert | board_post_changed |
News and the digest
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
source_id | TEXT · required | → news_sources.id | |
attempted_at | TEXT · required | ||
outcome | TEXT · required | check outcome IN ('fetched', 'not_modified', 'failed') | |
http_status | INTEGER · may be null | ||
items_found | INTEGER · required | Default 0. | |
items_new | INTEGER · required | Default 0. | |
error | TEXT · may be null | check error IN ('unreachable', 'timeout', 'http_error', 'too_large', 'not_a_feed') | |
error_detail | TEXT · may be null |
- check:
(outcome = 'failed') = (error IS NOT NULL)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
source_id | TEXT · required | → news_sources.id | |
url_key | TEXT · required | unique | |
link | TEXT · required | check link LIKE 'https://%' OR link LIKE 'http://%' | |
title | TEXT · required | check length(title) BETWEEN 1 AND 500 | |
title_key | TEXT · required | ||
language | TEXT · required | check language IN ('zh', 'en', 'ms') | |
published_at | TEXT · may be null | ||
description | TEXT · may be null | check description IS NULL OR length(description) <= 600 | The feed's own short description; only for a source whose terms are 'summary_allowed'. |
terms | TEXT · required | check terms IN ('headline_link', 'summary_allowed') | |
fetched_at | TEXT · required |
- check:
description IS NULL OR terms = 'summary_allowed'
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
name | TEXT · required | check length(trim(name)) BETWEEN 1 AND 120 | |
feed_url | TEXT · required | unique check feed_url LIKE 'https://%' OR feed_url LIKE 'http://%' | |
kind | TEXT · required | check kind IN ('rss', 'atom', 'json_api') | |
language | TEXT · required | check language IN ('zh', 'en', 'ms') | |
terms | TEXT · required | check terms IN ('headline_link', 'summary_allowed') | |
permission_note | TEXT · required | check length(trim(permission_note)) BETWEEN 1 AND 2000 | What permits us to use the source, with the evidence (a terms page, a letter, a date). |
active | INTEGER · required | check active IN (0, 1) | |
min_interval_minutes | INTEGER · required | check min_interval_minutes BETWEEN 15 AND 1440 | |
version | INTEGER · required | Default 0. | |
created_at | TEXT · required | ||
created_by | TEXT · may be null | → users.id | |
updated_at | TEXT · required | ||
updated_by | TEXT · may be null | → users.id | |
etag | TEXT · may be null | Fetch state, written by the fetch job only. | |
last_modified | TEXT · may be null | ||
next_due_at | TEXT · may be null | ||
last_attempt_at | TEXT · may be null | ||
last_success_at | TEXT · may be null | ||
last_error | TEXT · may be null | check last_error IN ('unreachable', 'timeout', 'http_error', 'too_large', 'not_a_feed') | Why the last fetch failed: a code the interface explains, the server's status, and the technical detail. |
last_error_status | INTEGER · may be null | ||
last_error_detail | TEXT · may be null | ||
consecutive_failures | INTEGER · required | Default 0. |
| Trigger | On | Refuses with |
|---|---|---|
news_sources_descriptions_withdrawn | after update of terms | keeps derived rows in step |
news_sources_digest_withdrawn | after update of terms, active | keeps derived rows in step |
news_sources_no_delete | before delete | news sources are paused, not deleted |
news_sources_terms_restored | after update of terms | keeps derived rows in step |
news_sources_terms_withdrawn | after update of terms | keeps derived rows in step |
news_sources_version | before update of version | source changed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
item_id | TEXT · required | primary key → news_items.id | |
topics | TEXT · required | check json_valid(topics) AND json_type(topics) = 'array' AND json_array_length(topics) BETWEEN 1 AND 3 | The topic names, as a JSON array of one to three entries from the fixed list in core (news/topics.ts). |
score | INTEGER · required | check score BETWEEN 0 AND 100 | |
classified_by | TEXT · required | check classified_by IN ('keywords', 'model') | |
summary_en | TEXT · may be null | check summary_en IS NULL OR length(summary_en) <= 600 | Machine-written; each is labelled as such wherever it is shown. |
summary_zh | TEXT · may be null | check summary_zh IS NULL OR length(summary_zh) <= 300 | |
title_en | TEXT · may be null | check title_en IS NULL OR length(title_en) <= 500 | |
title_zh | TEXT · may be null | check title_zh IS NULL OR length(title_zh) <= 500 | |
text_status | TEXT · required | check text_status IN ('pending', 'done', 'failed') | pending: the model has not answered usefully yet; done: the text is here; failed: given up after repeated unusable answers. |
model | TEXT · may be null | ||
attempts | INTEGER · required | Default 0. | |
attempted_at | TEXT · may be null | ||
updated_at | TEXT · required |
| Trigger | On | Refuses with |
|---|---|---|
news_analysis_summary_insert | before insert | summary not allowed for this source |
news_analysis_summary_update | before update of summary_en, summary_zh | summary not allowed for this source |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
unit_id | TEXT · required | ||
digest_date | TEXT · required | check digest_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' | |
kind | TEXT · required | check kind IN ('added', 'announcement') | |
headline | TEXT · required | check length(trim(headline)) BETWEEN 1 AND 200 | |
summary_zh | TEXT · may be null | check summary_zh IS NULL OR length(trim(summary_zh)) BETWEEN 1 AND 600 | |
summary_en | TEXT · may be null | check summary_en IS NULL OR length(trim(summary_en)) BETWEEN 1 AND 1200 | |
link | TEXT · may be null | check link IS NULL OR link LIKE 'https://%' OR link LIKE 'http://%' | |
added_by | TEXT · required | → users.id | |
added_at | TEXT · required | ||
withdrawn_by | TEXT · may be null | → users.id | |
withdrawn_at | TEXT · may be null |
(chamber_id, unit_id)→units (chamber_id, id)- check:
summary_zh IS NOT NULL OR summary_en IS NOT NULL - check:
(withdrawn_at IS NULL) = (withdrawn_by IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
digest_added_items_withdrawn_once | before update of withdrawn_at | digest item already withdrawn |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
digest_date | TEXT · required | primary key | |
version | INTEGER · required | check version >= 1 |
| Trigger | On | Refuses with |
|---|---|---|
digest_days_version | before update of version | digest changed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
unit_id | TEXT · required | ||
digest_date | TEXT · required | check digest_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' | |
item_id | TEXT · required | → news_items.id | |
action | TEXT · required | check action IN ('pin', 'hide') | |
decided_by | TEXT · required | → users.id | |
decided_at | TEXT · required |
(chamber_id, unit_id)→units (chamber_id, id)- unique index
digest_edits_one_per_item:(unit_id, digest_date, item_id)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
topics | TEXT · required | check json_valid(topics) AND json_type(topics) = 'array' AND json_array_length(topics) >= 1 | Topic names from the fixed list in core (news/topics.ts), as a JSON array of at least one. |
source_ids | TEXT · required | check json_valid(source_ids) AND json_type(source_ids) = 'array' | Ids of news sources from the operator's allowed list, as a JSON array; empty means only the admins' own items. |
ready_minute | INTEGER · required | check ready_minute BETWEEN 0 AND 1439 | Minutes after midnight, Malaysia time, when the digest is ready. |
varies_by | TEXT · required | check varies_by IN ('chamber', 'branch', 'committee') | |
version | INTEGER · required | ||
updated_at | TEXT · required | ||
updated_by | TEXT · required | → users.id |
| Trigger | On | Refuses with |
|---|---|---|
digest_settings_version | before update of version | digest settings changed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
unit_id | TEXT · required | ||
digest_date | TEXT · required | check digest_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' | |
ready_at | TEXT · required | ||
assembled_at | TEXT · required | ||
settings_json | TEXT · required | check json_valid(settings_json) | |
items_json | TEXT · required | check json_valid(items_json) AND json_type(items_json) = 'array' | |
ranked_json | TEXT · required | check json_valid(ranked_json) AND json_type(ranked_json) = 'array' | |
updated_at | TEXT · required |
(chamber_id, unit_id)→units (chamber_id, id)- unique index
digest_snapshots_one_per_day:(unit_id, digest_date)
| Trigger | On | Refuses with |
|---|---|---|
digest_snapshots_no_summary_insert | before insert | summary not allowed for this source |
digest_snapshots_no_summary_update | before update of items_json, ranked_json | summary not allowed for this source |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT · required | primary key → users.id | |
chamber_id | TEXT · required | primary key → chambers.id | |
frequency | TEXT · required | check frequency IN ('daily', 'weekly') | |
locale | TEXT · required | check locale IN ('zh', 'en') | The language the email is written in: the interface language the member chose it in. |
updated_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT · required | primary key → users.id | |
chamber_id | TEXT · required | primary key → chambers.id | |
period | TEXT · required | primary key check period GLOB 'daily:[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' OR period GLOB 'weekly:[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' | |
outcome | TEXT · required | check outcome IN ('sent', 'skipped') | |
at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
item_id | TEXT · required | primary key → news_items.id |
Member import
| Column | Type | Keys and checks | Note |
|---|---|---|---|
membership_id | TEXT · required | primary key | |
chamber_id | TEXT · required | ||
user_id | TEXT · required | → users.id | |
import_id | TEXT · required | ||
full_name | TEXT · may be null | check full_name IS NULL OR length(trim(full_name)) BETWEEN 1 AND 160 | |
phone | TEXT · may be null | check phone IS NULL OR length(trim(phone)) BETWEEN 6 AND 32 | |
company | TEXT · may be null | check company IS NULL OR length(trim(company)) BETWEEN 1 AND 160 | |
position | TEXT · may be null | check position IS NULL OR length(trim(position)) BETWEEN 1 AND 120 | |
industry | TEXT · may be null | check industry IS NULL OR length(trim(industry)) BETWEEN 1 AND 120 | |
locale | TEXT · required | check locale IN ('zh', 'en') | The language the row asked for, else the chamber's default, so later messages reach them in it. |
imported_by | TEXT · required | → users.id | |
imported_at | TEXT · required |
(chamber_id, membership_id)→memberships (chamber_id, id)
| Trigger | On | Refuses with |
|---|---|---|
imported_member_details_erase_only | before update | imported details are kept as they were imported, until erased |
imported_member_details_no_delete | before delete | imported details are kept as they were imported |
member_search_import_insert | after insert | keeps derived rows in step |
member_search_import_update | after update | keeps derived rows in step |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
import_id | TEXT · required | primary key → member_imports.id | |
chunk | INTEGER · required | primary key check chunk >= 0 | |
rows_json | TEXT · required | ||
checks_json | TEXT · required | ||
status | TEXT · required | check status IN ('pending', 'done') | |
results_json | TEXT · may be null | ||
done_at | TEXT · may be null |
- check:
(status = 'done') = (done_at IS NOT NULL) AND (status = 'done') = (results_json IS NOT NULL)
| Trigger | On | Refuses with |
|---|---|---|
member_import_chunks_once | before update | import chunk already imported |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
branch_id | TEXT · required | The branch new memberships are fixed to, and the committee rows without one join. | |
committee_id | TEXT · required | ||
created_by | TEXT · required | → users.id | |
created_at | TEXT · required | ||
expires_at | TEXT · required | Thirty minutes after the last thing done with it. An expired preview cannot be used. | |
headers_json | TEXT · required | ||
mapping_json | TEXT · required | ||
row_count | INTEGER · required | check row_count BETWEEN 1 AND 2000 | |
version | INTEGER · required | Default 0. |
(chamber_id, committee_id)→units (chamber_id, id)(chamber_id, branch_id)→units (chamber_id, id)
| Trigger | On | Refuses with |
|---|---|---|
member_imports_fixed | before update of id, chamber_id, branch_id, committee_id, created_by, created_at, headers_json, row_count | an import cannot change what it imports into |
member_imports_version | before update of version | import changed |
Audit and operations
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
occurred_at | TEXT · required | ||
capability | TEXT · required | ||
actor_user_id | TEXT · may be null | ||
chamber_id | TEXT · may be null | ||
outcome | TEXT · required | check outcome IN ('executed', 'refused') | |
refusal_code | TEXT · may be null | ||
operation_id | TEXT · may be null | ||
detail_json | TEXT · required | Default '{}'. | |
unit_id | TEXT · may be null |
- check:
(outcome = 'refused') = (refusal_code IS NOT NULL)
| Trigger | On | Refuses with |
|---|---|---|
audit_log_no_delete | before delete | audit_log is append-only |
audit_log_no_update | before update | audit_log is append-only |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key check length(id) BETWEEN 8 AND 128 | |
capability | TEXT · required | ||
actor_user_id | TEXT · may be null | ||
input_hash | TEXT · required | ||
result_json | TEXT · required | ||
created_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
task | TEXT · required | ||
period | TEXT · may be null | For work done once per period, such as the daily backup: the period it covers ('2026-11-13'). | |
started_at | TEXT · required | ||
finished_at | TEXT · may be null | ||
outcome | TEXT · required | check outcome IN ('running', 'succeeded', 'failed') | |
counts_json | TEXT · required | Default '{}'. | |
error | TEXT · may be null | Scrubbed of personal data before it is stored. |
- unique index
scheduled_runs_one_per_period:(task, period) where period IS NOT NULL AND outcome IN ('running', 'succeeded') - check:
(outcome = 'running') = (finished_at IS NULL)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
audit_id | TEXT · required | primary key → audit_log.id | |
unit_id | TEXT · required | primary key → units.id |
| Trigger | On | Refuses with |
|---|---|---|
audit_entry_units_no_delete | before delete | audit_entry_units is append-only |
audit_entry_units_no_update | before update | audit_entry_units is append-only |
Brands
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
name_zh | TEXT · required | check length(trim(name_zh)) BETWEEN 1 AND 80 | |
name_en | TEXT · required | check length(trim(name_en)) BETWEEN 1 AND 80 | |
mark | TEXT · required | check length(mark) = 1 | One character, pressed into the chamber's mark. |
colour | TEXT · required | check length(colour) = 7 AND colour GLOB '#[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]' | #RRGGBB, upper case. Its contrast against white text is checked in code (WCAG AA). |
version | INTEGER · required | check version >= 1 | |
updated_at | TEXT · required | ||
updated_by | TEXT · required | → users.id |
| Trigger | On | Refuses with |
|---|---|---|
chamber_brands_version | before update of version | brand changed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
refusal | TEXT · required |
- check
brand_logo_changed:refusal IS NULL
| Column | Type | Keys and checks | Note |
|---|---|---|---|
key | TEXT · required | primary key check key LIKE 'media/%' | |
media_id | TEXT · required | ||
height | INTEGER · required | check height > 0 | |
content_type | TEXT · required | check content_type IN ('image/webp') | |
byte_size | INTEGER · required | check byte_size > 0 |
- unique
(media_id, height)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
media_id | TEXT · required | unique | |
version | INTEGER · required | check version >= 1 | |
updated_at | TEXT · required | ||
updated_by | TEXT · required | → users.id |
| Trigger | On | Refuses with |
|---|---|---|
chamber_logos_version | before update | brand_logo_changed: a logo takes the next version |
Events, registration and check-in
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
gap_hours | INTEGER · required | check gap_hours BETWEEN 1 AND 720 | |
include_public | INTEGER · required | check include_public IN (0, 1) | |
version | INTEGER · required | check version >= 1 | |
updated_at | TEXT · required | ||
updated_by | TEXT · required | → users.id | |
include_exco | INTEGER · required | check include_exco IN (0, 1) | Default 1. |
include_tiers | INTEGER · required | check include_tiers IN (0, 1) | Default 1. |
include_friends | INTEGER · required | check include_friends IN (0, 1) | Default 1. |
| Trigger | On | Refuses with |
|---|---|---|
event_wave_patterns_members_insert | before insert | event_pattern_no_members |
event_wave_patterns_members_update | before update of include_tiers, include_friends | event_pattern_no_members |
event_wave_patterns_version | before update of version | pattern changed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
event_id | TEXT · required | primary key → events.id | |
position | INTEGER · required | primary key check position BETWEEN 0 AND 49 | |
audience | TEXT · required | check audience IN ('exco', 'tier', 'friend', 'public') | |
tier_id | TEXT · may be null | → tiers.id | |
opens_at | TEXT · required |
- unique index
event_waves_one_audience:(event_id, audience, coalesce(tier_id, '')) - check:
(audience = 'tier') = (tier_id IS NOT NULL)
| Trigger | On | Refuses with |
|---|---|---|
event_waves_completed_delete | before delete | event completed |
event_waves_completed_insert | before insert | event completed |
event_waves_fit | before insert | a wave |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
unit_id | TEXT · required | ||
reach | TEXT · required | check reach IN ('committee', 'branch', 'chamber') | |
reach_unit_id | TEXT · required | ||
title_zh | TEXT · required | check length(title_zh) <= 160 | Default ''. |
title_en | TEXT · required | check length(title_en) <= 160 | Default ''. |
description_zh | TEXT · required | check length(description_zh) <= 5000 | Default ''. |
description_en | TEXT · required | check length(description_en) <= 5000 | Default ''. |
venue | TEXT · required | check length(trim(venue)) BETWEEN 1 AND 300 | |
starts_at | TEXT · required | Instants in UTC (ISO 8601); shown in the chamber's time zone. | |
ends_at | TEXT · required | ||
capacity | INTEGER · required | check capacity BETWEEN 1 AND 100000 | |
guest_allowance | INTEGER · required | check guest_allowance BETWEEN 0 AND 10 | |
status | TEXT · required | check status IN ('draft', 'published', 'cancelled') | |
created_by | TEXT · required | → users.id | |
created_at | TEXT · required | ||
updated_by | TEXT · required | → users.id | |
updated_at | TEXT · required | ||
published_by | TEXT · may be null | → users.id | |
published_at | TEXT · may be null | ||
cancelled_by | TEXT · may be null | → users.id | |
cancelled_at | TEXT · may be null | ||
cancel_reason | TEXT · may be null | check cancel_reason IS NULL OR length(trim(cancel_reason)) BETWEEN 1 AND 500 | |
version | INTEGER · required | check version >= 0 | Default 0. |
(chamber_id, reach_unit_id)→units (chamber_id, id)(chamber_id, unit_id)→units (chamber_id, id)- unique
(chamber_id, id) - check:
length(trim(title_zh)) > 0 OR length(trim(title_en)) > 0 - check:
ends_at > starts_at - check:
(published_at IS NULL) = (published_by IS NULL) - check:
status = 'draft' OR published_at IS NOT NULL OR status = 'cancelled' - check:
(status = 'cancelled') = (cancelled_at IS NOT NULL) - check:
(cancelled_at IS NULL) = (cancelled_by IS NULL) AND (cancelled_at IS NULL) = (cancel_reason IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
events_cancelled_final | before update | event cancelled |
events_capacity_holds | before update of capacity | capacity below places taken |
events_completed_details | before update of starts_at, ends_at, venue, capacity, guest_allowance, reach, reach_unit_id, unit_id | event completed |
events_completed_final | before update of status | event completed |
events_fixed | before update of id, chamber_id, unit_id, created_by, created_at | an event cannot move to another unit |
events_not_deleted | before delete | events are cancelled, not deleted |
events_reach_fits_insert | before insert | reach above the owner |
events_reach_fits_update | before update of reach, reach_unit_id | reach above the owner |
events_version | before update of version | event changed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
event_id | TEXT · required | ||
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
status | TEXT · required | check status IN ('registered', 'waitlisted', 'offered', 'cancelled', 'declined', 'expired', 'released') | |
guests_json | TEXT · required | check json_valid(guests_json) AND json_type(guests_json) = 'array' | The guests' names, as a JSON array of strings. Default '[]'. |
places | INTEGER · required | check places BETWEEN 1 AND 11 | |
locale | TEXT · required | check locale IN ('zh', 'en') | The language the person registered in, for what they are told about it. |
created_at | TEXT · required | When they registered or joined the waitlist: the waitlist's order after tier rank. | |
updated_at | TEXT · required | ||
offered_at | TEXT · may be null | ||
offer_expires_at | TEXT · may be null | ||
ended_at | TEXT · may be null | ||
version | INTEGER · required | check version >= 0 | Default 0. |
walk_in | INTEGER · required | check walk_in IN (0, 1) | Default 0. |
(chamber_id, event_id)→events (chamber_id, id)- unique index
event_registrations_one_live:(event_id, user_id) where status IN ('registered', 'waitlisted', 'offered') - check:
places = 1 + json_array_length(guests_json) - check:
status <> 'offered' OR (offered_at IS NOT NULL AND offer_expires_at IS NOT NULL) - check:
(ended_at IS NULL) = (status IN ('registered', 'waitlisted', 'offered'))
| Trigger | On | Refuses with |
|---|---|---|
event_registrations_capacity_insert | before insert | event full |
event_registrations_capacity_update | before update of status | event full |
event_registrations_fixed | before update of id, event_id, chamber_id, user_id, guests_json, places, locale, created_at | a registration |
event_registrations_moves | before update of status | registration changed |
event_registrations_offer_in_time | before update of status | offer expired |
event_registrations_open | before insert | event not open for registration |
event_registrations_version | before update of version | registration changed |
event_registrations_walk_in_fixed | before update of walk_in | a registration |
event_registrations_walk_in_shape | before insert | a walk-in is registered for one place |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
key | TEXT · required | unique check length(key) BETWEEN 16 AND 64 | Generated on the device that made the check-in; the same key is the same check-in. |
event_id | TEXT · required | ||
chamber_id | TEXT · required | → chambers.id | |
registration_id | TEXT · required | → event_registrations.id | |
user_id | TEXT · required | → users.id | |
method | TEXT · required | check method IN ('scan', 'self', 'manual', 'walk_in') | |
by_user_id | TEXT · required | → users.id | |
checked_in_at | TEXT · required | ||
recorded_at | TEXT · required | ||
undone_at | TEXT · may be null | ||
undone_by | TEXT · may be null | → users.id | |
undo_reason | TEXT · may be null | check undo_reason IS NULL OR length(trim(undo_reason)) BETWEEN 1 AND 500 |
(chamber_id, event_id)→events (chamber_id, id)- unique index
event_check_ins_one_standing:(registration_id) where undone_at IS NULL - check:
(undone_at IS NULL) = (undone_by IS NULL) AND (undone_at IS NULL) = (undo_reason IS NULL) - check:
method <> 'self' OR by_user_id = user_id
| Trigger | On | Refuses with |
|---|---|---|
event_check_ins_fixed | before update of id, key, event_id, chamber_id, registration_id, user_id, method, by_user_id, checked_in_at, recorded_at | a check-in |
event_check_ins_not_completed | before insert | event completed |
event_check_ins_registered | before insert | check-in needs a registration |
event_check_ins_undo_not_completed | before update of undone_at | event completed |
event_check_ins_undo_once | before update of undone_at, undone_by, undo_reason | check-in already undone |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
kind | TEXT · required | check kind IN ('reminder', 'opened') | |
event_id | TEXT · required | ||
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
registration_id | TEXT · may be null | ||
starts_at | TEXT · may be null | ||
batch_id | TEXT · required | ||
sent_at | TEXT · required |
(chamber_id, event_id)→events (chamber_id, id)- unique index
event_notices_sent_opened:(event_id, user_id) where kind = 'opened' - unique index
event_notices_sent_reminder:(registration_id, starts_at) where kind = 'reminder' - check:
(kind = 'reminder') = (registration_id IS NOT NULL) - check:
(kind = 'reminder') = (starts_at IS NOT NULL)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
event_id | TEXT · required | primary key → events.id | |
opened_through | TEXT · required | ||
event_version | INTEGER · required | ||
updated_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
event_id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
completed_at | TEXT · required | ||
completed_by | TEXT · may be null | → users.id | Null when the schedule completed it. |
(chamber_id, event_id)→events (chamber_id, id)
| Trigger | On | Refuses with |
|---|---|---|
event_completions_after_end | before insert | event not ended |
event_completions_fixed | before update | event completed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
event_id | TEXT · required | ||
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
role | TEXT · required | check role IN ('helper', 'speaker', 'volunteer') | |
note | TEXT · may be null | check note IS NULL OR length(trim(note)) BETWEEN 1 AND 300 | |
points | INTEGER · required | check points >= 0 | The points awarded: the chamber's organising value at the time. |
awarded_by | TEXT · required | → users.id | |
awarded_at | TEXT · required | ||
removed_by | TEXT · may be null | → users.id | |
removed_at | TEXT · may be null | ||
remove_reason | TEXT · may be null | check remove_reason IS NULL OR length(trim(remove_reason)) BETWEEN 1 AND 500 |
(chamber_id, event_id)→events (chamber_id, id)- unique index
event_organisers_once:(event_id, user_id) - check:
(removed_at IS NULL) = (removed_by IS NULL) AND (removed_at IS NULL) = (remove_reason IS NULL) - check:
awarded_by <> user_id
| Trigger | On | Refuses with |
|---|---|---|
event_organisers_awarded | before insert | organising needs an event that has started |
event_organisers_fixed | before update of id, event_id, chamber_id, user_id, role, note, points, awarded_by, awarded_at | an organising award is fixed |
event_organisers_removed_once | before update of removed_at, removed_by, remove_reason | organising award already removed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
event_id | TEXT · required | ||
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
named_by | TEXT · required | → users.id | |
named_at | TEXT · required | ||
removed_by | TEXT · may be null | → users.id | |
removed_at | TEXT · may be null |
(chamber_id, event_id)→events (chamber_id, id)- unique index
event_staff_once:(event_id, user_id) where removed_at IS NULL - check:
(removed_at IS NULL) = (removed_by IS NULL) - check:
named_by <> user_id
| Trigger | On | Refuses with |
|---|---|---|
event_staff_fixed | before update of id, event_id, chamber_id, user_id, named_by, named_at | event staff details are fixed |
event_staff_named | before insert | event staff need an open event |
event_staff_removed_once | before update of removed_at, removed_by | event staff already removed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
refusal | TEXT · required |
- check
event_staff_removed:refusal <> 'event_staff_removed'
Profiles, media and the directory
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT · required | primary key → profiles.user_id | |
ordinal | INTEGER · required | primary key check ordinal BETWEEN 0 AND 4 | |
name | TEXT · required | check length(trim(name)) BETWEEN 1 AND 120 | |
industry | TEXT · required | check industry IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U') | |
position | TEXT · required | check length(trim(position)) BETWEEN 1 AND 100 | |
website | TEXT · may be null | check website IS NULL OR website LIKE 'https://%' | |
description | TEXT · may be null | ||
size | TEXT · may be null | check size IS NULL OR size IN ('s1_4', 's5_29', 's30_74', 's75_200', 's201_plus') | |
founded_year | INTEGER · may be null | check founded_year IS NULL OR founded_year >= 1900 |
| Trigger | On | Refuses with |
|---|---|---|
member_search_company_delete | after delete | keeps derived rows in step |
member_search_company_insert | after insert | keeps derived rows in step |
member_search_company_update | after update | keeps derived rows in step |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT · required | primary key → users.id | |
name_zh | TEXT · may be null | check name_zh IS NULL OR length(trim(name_zh)) BETWEEN 1 AND 100 | |
name_en | TEXT · may be null | check name_en IS NULL OR length(trim(name_en)) BETWEEN 1 AND 100 | |
offers | TEXT · may be null | ||
needs | TEXT · may be null | ||
phone | TEXT · may be null | ||
email | TEXT · may be null | ||
whatsapp | TEXT · may be null | ||
wechat | TEXT · may be null | ||
bio | TEXT · may be null | ||
social_json | TEXT · required | check json_valid(social_json) AND json_type(social_json) = 'array' | Default '[]'. |
languages_json | TEXT · required | check json_valid(languages_json) AND json_type(languages_json) = 'array' | Default '[]'. |
visibility_json | TEXT · required | check json_valid(visibility_json) AND json_type(visibility_json) = 'object' | |
version | INTEGER · required | check version >= 1 | |
created_at | TEXT · required | ||
updated_at | TEXT · required | ||
offer_industries_json | TEXT · required | check json_valid(offer_industries_json) AND json_type(offer_industries_json) = 'array' | Default '[]'. |
need_industries_json | TEXT · required | check json_valid(need_industries_json) AND json_type(need_industries_json) = 'array' | Default '[]'. |
- check:
name_zh IS NOT NULL OR name_en IS NOT NULL - check:
phone IS NOT NULL OR email IS NOT NULL OR whatsapp IS NOT NULL OR wechat IS NOT NULL
| Trigger | On | Refuses with |
|---|---|---|
matching_profile_changed | after update of version | keeps derived rows in step |
matching_profile_deleted | after delete | keeps derived rows in step |
matching_profile_saved | after insert | keeps derived rows in step |
member_search_profile_delete | after delete | keeps derived rows in step |
member_search_profile_insert | after insert | keeps derived rows in step |
member_search_profile_update | after update | keeps derived rows in step |
profiles_version | before update of version | profile changed |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
owner_user_id | TEXT · required | → users.id | |
purpose | TEXT · required | check purpose IN ('profile_photo') | |
created_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
key | TEXT · required | primary key check key LIKE 'media/%' | |
queued_at | TEXT · required | ||
attempts | INTEGER · required | Default 0. | |
last_error | TEXT · may be null |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
key | TEXT · required | primary key check key LIKE 'media/%' | |
media_id | TEXT · required | → media.id | |
size | INTEGER · required | check size > 0 | The square's side in pixels. |
content_type | TEXT · required | check content_type IN ('image/webp') | |
byte_size | INTEGER · required | check byte_size > 0 |
- unique
(media_id, size)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
refusal | TEXT · required |
- check
photo_changed:refusal IS NULL
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT · required | primary key → users.id | |
media_id | TEXT · required | unique → media.id | |
version | INTEGER · required | check version >= 1 | |
updated_at | TEXT · required |
| Trigger | On | Refuses with |
|---|---|---|
profile_photos_keep_profile | before update of media_id | photo profile required |
profile_photos_need_profile | before insert | photo profile required |
profile_photos_version | before update | photo_changed: a photo takes the next version |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | ANY · may be null | ||
chamber_id | ANY · may be null | ||
names | ANY · may be null | ||
companies | ANY · may be null | ||
industries | ANY · may be null | ||
stated | ANY · may be null | ||
offers | ANY · may be null | ||
needs | ANY · may be null |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
user_id | TEXT | ||
chamber_id | TEXT | ||
names | |||
companies | |||
industries | |||
stated | |||
offers | |||
needs |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT | ||
user_id | TEXT | ||
source | |||
full_name | TEXT | ||
phone | TEXT | ||
company | TEXT | ||
position | TEXT | ||
industry | TEXT |
Matching, introductions and the board
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
notice_version | TEXT · required | check length(notice_version) BETWEEN 1 AND 40 | |
opted_in_at | TEXT · required | ||
wanted_version | INTEGER · required | check wanted_version >= 0 | The profile version the vectors should reflect: 0 while the person has no profile. |
embedded_version | INTEGER · may be null | check embedded_version IS NULL OR embedded_version >= 1 | |
embed_state | TEXT · required | check embed_state IN ('pending', 'queued', 'embedded', 'skipped') | pending: to be sent to the queue; queued: sent; embedded: vectors match wanted_version; skipped: nothing to embed or no embedding service bound (embed_note says which). |
embed_note | TEXT · may be null | check embed_note IS NULL OR embed_note IN ('no_profile', 'nothing_to_match', 'not_configured') | |
queued_at | TEXT · may be null | ||
embedded_at | TEXT · may be null | ||
model | TEXT · may be null | ||
overwritten | INTEGER · required | check overwritten IN (0, 1) | 1 when a run for an older profile version wrote to the store after the run now in flight may have written: that run then records the opt-in as pending, not embedded, so it is embedded again. Default 0. |
- unique index
matching_optins_one:(chamber_id, user_id)
| Trigger | On | Refuses with |
|---|---|---|
matching_optins_gone | after delete | keeps derived rows in step |
matching_optins_members_only | before insert | matching_members_only |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
vector_id | TEXT · required | primary key | |
chamber_id | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
vector_id | TEXT · required | primary key | |
optin_id | TEXT · required | ||
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
kind | TEXT · required | check kind IN ('offers', 'needs') | |
profile_version | INTEGER · required | check profile_version >= 1 |
- unique
(optin_id, kind)
| Trigger | On | Refuses with |
|---|---|---|
matching_vectors_current | before insert | matching_overtaken |
matching_vectors_current_update | before update of profile_version | matching_overtaken |
matching_vectors_need_optin | before insert | matching_optin_gone |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
chamber_id | TEXT · required | primary key → chambers.id | |
user_id | TEXT · required | primary key → users.id | |
subject_user_id | TEXT · required | primary key → users.id | |
dismissed_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
suggestion_id | TEXT · required | ||
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
subject_user_id | TEXT · required | → users.id | |
model | TEXT · required | ||
system | TEXT · required | ||
prompt | TEXT · required | ||
output | TEXT · may be null | The answer as received (truncated), or null when the model could not be reached. | |
outcome | TEXT · required | check outcome IN ('used', 'unusable', 'unavailable') | used: the reason shown; unusable: the answer failed validation; unavailable: no answer. |
asked_at | TEXT · required |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | |
week | TEXT · required | check week GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' | The Monday the week starts on, in the chamber's time zone (YYYY-MM-DD). |
made_by | TEXT · required | check made_by IN ('schedule', 'member') | |
made_at | TEXT · required | ||
shown | INTEGER · required | check shown >= 0 | What the batch was made with: shown is how many were suggested; none, why there were none. |
note | TEXT · may be null | check note IS NULL OR note IN ('not_shown', 'not_embedded', 'no_match') |
- unique index
matching_suggestion_batches_weekly:(chamber_id, user_id, week) where made_by = 'schedule'
| Trigger | On | Refuses with |
|---|---|---|
matching_suggestion_batches_hourly | before insert | suggestions_made_recently |
matching_suggestion_batches_opted_in | before insert | suggestions_need_matching |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
batch_id | TEXT · required | → matching_suggestion_batches.id | |
chamber_id | TEXT · required | → chambers.id | |
user_id | TEXT · required | → users.id | Who the suggestion is for, and who is suggested. |
subject_user_id | TEXT · required | → users.id | |
ordinal | INTEGER · required | check ordinal >= 0 | |
score | REAL · required | ||
fit | TEXT · required | check fit IN ('they_offer', 'they_need') | Which way the fit runs: they offer what the member needs, or they need what the member offers. |
reason_source | TEXT · required | check reason_source IN ('model', 'template') | |
reason_en | TEXT · may be null | check reason_en IS NULL OR length(reason_en) BETWEEN 1 AND 300 | |
reason_zh | TEXT · may be null | check reason_zh IS NULL OR length(reason_zh) BETWEEN 1 AND 300 | |
template_kind | TEXT · may be null | check template_kind IS NULL OR template_kind IN ('industry', 'they_offer', 'they_need') | |
template_param | TEXT · may be null | check template_param IS NULL OR length(template_param) BETWEEN 1 AND 200 | |
reason_basis | TEXT · may be null | check reason_basis IS NULL OR length(reason_basis) = 64 | For a model reason: a SHA-256 of what the model was shown of the suggested person's card. The reason is shown only while their card as the viewer sees it still gives the same; else the template is, worded from the cards as they are now, so a field hidden since is never shown through the reason. |
dismissed_at | TEXT · may be null |
- unique
(batch_id, subject_user_id) - check:
user_id <> subject_user_id - check:
(reason_source = 'model' AND reason_en IS NOT NULL AND reason_zh IS NOT NULL) OR (reason_source = 'template' AND template_kind IS NOT NULL)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
requester_id | TEXT · required | → users.id | |
recipient_id | TEXT · required | → users.id | |
note | TEXT · required | check length(note) BETWEEN 1 AND 500 | |
source | TEXT · required | check source IN ('suggestion', 'directory', 'board') | Where the request was made: a matching suggestion, the directory card, or a board post. |
source_ref | TEXT · may be null | check source_ref IS NULL OR length(source_ref) BETWEEN 1 AND 100 | The suggestion or board post it was made from, when there is one. |
status | TEXT · required | check status IN ('pending', 'accepted', 'declined', 'expired', 'closed') | pending: waiting for an answer; accepted / declined: answered; expired: not answered in 14 days; closed: one side's data was deleted while it was waiting. |
requester_locale | TEXT · required | check requester_locale IN ('zh', 'en') | The language each person is written to in, for email about the request. |
recipient_locale | TEXT · required | check recipient_locale IN ('zh', 'en') | |
created_at | TEXT · required | ||
expires_at | TEXT · required | ||
decided_at | TEXT · may be null | ||
version | INTEGER · required | check version >= 1 | Default 1. |
requester_branch_id | TEXT · may be null | → units.id |
- unique index
introductions_open_pair:(chamber_id, min(requester_id, recipient_id), max(requester_id, recipient_id)) where status = 'pending' - check:
requester_id <> recipient_id - check:
(status = 'pending') = (decided_at IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
introductions_accept_eligible | before update of status | introduction_not_eligible |
introductions_accept_have_profiles | before update of status | introduction_no_profile |
introductions_answered_once | before update of status | introduction_changed |
introductions_board_post_open | before insert | board_post_unavailable |
introductions_declined_recently | before insert | introduction_declined_recently |
introductions_eligible | before insert | introduction_not_eligible |
introductions_have_profiles | before insert | introduction_no_profile |
introductions_requester_branch | after insert | keeps derived rows in step |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
author_id | TEXT · required | → users.id | |
kind | TEXT · required | check kind IN ('ask', 'offer') | |
title | TEXT · required | check length(title) BETWEEN 1 AND 120 | |
details | TEXT · required | check length(details) BETWEEN 1 AND 2000 | |
search | TEXT · required | The title and details folded for search (NFKC, then lower-cased) by the app, as SQLite's lower() folds only ASCII: the search words are folded the same way. | |
industry | TEXT · may be null | check industry IS NULL OR industry IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U') | An MSIC section from the author's profile, or none. |
status | TEXT · required | check status IN ('open', 'closed', 'removed') | open: on the board until expires_at; closed: by the author; removed: by an admin, with a reason. |
author_locale | TEXT · required | check author_locale IN ('zh', 'en') | The language the author is written to in, for the email telling them of a removal. |
created_at | TEXT · required | ||
expires_at | TEXT · required | ||
closed_at | TEXT · may be null | ||
removed_at | TEXT · may be null | ||
removed_by | TEXT · may be null | → users.id | |
removal_reason | TEXT · may be null | check removal_reason IS NULL OR length(removal_reason) BETWEEN 1 AND 500 | |
version | INTEGER · required | check version >= 1 | Default 1. |
- check:
expires_at > created_at - check:
(status = 'closed') = (closed_at IS NOT NULL) - check:
(status = 'removed') = (removed_at IS NOT NULL AND removed_by IS NOT NULL AND removal_reason IS NOT NULL)
| Trigger | On | Refuses with |
|---|---|---|
board_posts_changed_once | before update of status | board_post_changed |
board_posts_eligible | before insert | board_not_eligible |
board_posts_limit | before insert | board_post_limit |
| Column | Type | Keys and checks | Note |
|---|---|---|---|
introduction_id | TEXT · required | primary key → introductions.id | |
user_id | TEXT · required | primary key → users.id | |
chamber_id | TEXT · required | → chambers.id | |
asked_at | TEXT · required | ||
editable_until | TEXT · required | Asked plus 60 days: an answer may be given or changed before then, never after. | |
ask_batch | TEXT · required | The scheduled run that asked, so only the run that wrote the row tells the person. | |
answer | TEXT · may be null | check answer IS NULL OR answer IN ('none', 'talks', 'deal') | none: nothing came of it; talks: in talks; deal: a deal was done. Empty until answered. |
answered_at | TEXT · may be null | ||
version | INTEGER · required | check version >= 1 | Default 1. |
- check:
(answer IS NULL) = (answered_at IS NULL) - check:
editable_until > asked_at
| Trigger | On | Refuses with |
|---|---|---|
introduction_outcomes_fixed | before update of introduction_id, user_id, chamber_id, asked_at, editable_until, ask_batch | introduction_outcome_fixed |
introduction_outcomes_locked | before update of answer, answered_at | introduction_outcome_locked |
introduction_outcomes_party | before insert | introduction_outcome_party |
introduction_outcomes_versioned | before update | introduction_outcome_changed |
Event photos
| Column | Type | Keys and checks | Note |
|---|---|---|---|
refusal | TEXT · required |
- check
event_photo_changed:refusal <> 'event_photo_changed' - check
event_photo_not_allowed:refusal <> 'event_photo_not_allowed'
| Column | Type | Keys and checks | Note |
|---|---|---|---|
key | TEXT · required | primary key check key LIKE 'media/%' | |
photo_id | TEXT · required | → event_photos.id | |
width | INTEGER · required | check width > 0 | The stored width in pixels; the height follows the photo's shape. |
content_type | TEXT · required | check content_type IN ('image/webp') | |
byte_size | INTEGER · required | check byte_size > 0 |
- unique
(photo_id, width)
| Column | Type | Keys and checks | Note |
|---|---|---|---|
id | TEXT · required | primary key | |
chamber_id | TEXT · required | → chambers.id | |
event_id | TEXT · required | ||
uploader_user_id | TEXT · may be null | → users.id | Null once the uploader's data was erased while the photo was approved. |
uploaded_as | TEXT · required | check uploaded_as IN ('staff', 'attendee') | 'staff' photos are published at once; 'attendee' photos wait for an admin. |
status | TEXT · required | check status IN ('pending', 'approved', 'rejected') | |
showcase | INTEGER · required | check showcase IN (0, 1) | Default 0. |
caption_zh | TEXT · required | check length(caption_zh) <= 300 | Default ''. |
caption_en | TEXT · required | check length(caption_en) <= 300 | Default ''. |
uploaded_at | TEXT · required | ||
moderated_by | TEXT · may be null | → users.id | |
moderated_at | TEXT · may be null | ||
showcased_by | TEXT · may be null | → users.id | |
showcased_at | TEXT · may be null | ||
version | INTEGER · required | check version >= 1 | Default 1. |
(chamber_id, event_id)→events (chamber_id, id)- check:
showcase = 0 OR status = 'approved' - check:
(status = 'pending') = (moderated_at IS NULL) - check:
(moderated_at IS NULL) = (moderated_by IS NULL) - check:
(showcase = 1) = (showcased_at IS NOT NULL) AND (showcased_at IS NULL) = (showcased_by IS NULL)
| Trigger | On | Refuses with |
|---|---|---|
event_photos_event_open | before insert | event_photo_event_closed |
event_photos_fixed | before update of id, chamber_id, event_id, uploaded_as, uploaded_at | an event photo stays with its event |
event_photos_never_pending_again | before update of status | event_photo_changed: a decided photo is not pending again |
event_photos_pending_limit | before insert | event_photo_limit_reached |
event_photos_uploader_unlinked_only | before update of uploader_user_id | an event photo stays with its event |
event_photos_version | before update of version | event_photo_changed: a photo takes the next version |
Migrations
Applied in file order, to the remote database before the code that needs them is deployed. Numbers missing from the sequence have no file in the repository.
| File | What it does |
|---|---|
0001_foundation.sql | Users, platform operators, chambers, and the records every capability leaves behind. |
0002_sign_in.sql | Email sign-in codes, sessions and privacy consent. |
0003_structure.sql | The tree of units inside a chamber, and the admins appointed over it. |
0004_membership.sql | Applications to committees, chamber memberships, committee memberships, and the inbox and outbox that tell people about decisions. |
0005_audit_scope.sql | Each audit entry records the unit a scoped capability acted at, so a chamber's log can be shown to the admins of that unit and the units above it. |
0006_outbox_sending.sql | Adds the 'sending' state, so a delivery can claim an email before sending it. SQLite cannot alter a CHECK constraint, so the table is rebuilt. |
0007_positions.sql | Positions: an office a member holds in one committee for a dated term. The holders of current positions in a committee are its Exco. Whether a position is current is worked out when it is read, from its dates in the chamber's time zone, so nothing needs to run when a term lapses. |
0008_tiers_points.sql | Tiers, points and a member's standing in a chamber. Concurrency (D1 has no interactive transactions, so a command reads before its batch): * memberships.standing_version is bumped by every change to a member's points or tier, and the trigger refuses a bump from a stale reading, so two changes to one member computed from the same reading cannot both commit. * points_settings.ladder_version is bumped when the ladder changes. A points change asserts the ladder it used is still current (ladder_check), and bumps points_version; a ladder edit asserts no points changed since it read them (points_check). So a raise is never computed from a ladder or totals that changed underneath it. Each conflict aborts the batch with 'standing changed', and the command is refused with try_again. |
0009_membership_lifecycle.sql | Leaving, suspension and ending a membership. A membership moves only between active, suspended, resigned and terminated; resigned and terminated are final. Every move is recorded first in membership_transitions, and a committee membership, once ended, stays ended. |
0010_start_tier_open_only.sql | A new membership starts at the lowest tier. Only an open membership gets one: an ended membership is final and cannot be updated (0009), so the starting tier must not be written to it. |
0011_visibility.sql | Who may see what in a chamber: a table of the fixed gateable items against audiences. A chamber with no rows uses the defaults in code (src/visibility.ts). Saving the table replaces every row at once, so a saved chamber holds one row per item and audience. A tier with no row of its own takes the ticks of the tier just below it on the ladder, so a tier added later inherits; a retired tier's rows are ignored. Concurrency: each save bumps visibility_settings.version from the version the admin opened, and the trigger refuses a save from a stale table, so two admins saving at once cannot silently overwrite each other. The first save of a chamber inserts version 1, and a second first save collides on the primary key. |
0012_data_requests.sql | Requests from a person about the data held on them: deleting what one chamber holds, or erasing their whole account. Each chamber is the data controller and answers within 21 days; the platform operator erases accounts. A request is received, then completed or refused, once. Every move is recorded first in data_request_transitions and takes the request's next version, so two admins deciding one request at once cannot both succeed. |
0013_news_sources.sql | News sources and the headlines fetched from them, for the daily digest. Only metadata is kept: a headline, its link, when it was published and where it came from. A feed's own short description is kept only for a source whose terms allow it (the CHECK on news_items refuses one otherwise). Article bodies are never stored. Concurrency (D1 has no interactive transactions): * news_sources.version is bumped by every operator edit, and the trigger refuses a bump from a stale reading, so two edits made from the same reading cannot both commit. * A fetch claims a source by moving next_due_at forward with a guarded UPDATE, so two runs at once cannot both fetch it. * news_items.url_key is unique, so a headline is stored once however often it is fetched. |
0014_member_import.sql | Importing existing members from a spreadsheet. An admin uploads a file; it is checked and kept as a preview for thirty minutes, in chunks of at most a hundred rows. Each chunk is imported by one command, so a D1 batch stays within its limits. Concurrency: every run bumps member_imports.version from the value it read, and the trigger refuses a stale bump, so two runs computed from the same reading cannot both commit. A chunk, once imported, cannot be imported again. |
0015_scheduled_runs.sql | Each run of scheduled work (outbox retries, the daily backup), so the operator's health page can show when it last ran and how it went. |
0016_news_analysis.sql | What the platform worked out about each news item: its topics and relevance score, and the machine-written text shown with it (a two-sentence summary in Chinese and English for a source whose terms allow it; otherwise only the headline machine-translated into the interface languages). Every item gets a row in the same batch that stores it, classified by the keyword rules; the scheduled analysis then asks the model and replaces the classification when the answer is valid. A summary is never kept for a headline-and-link source. The triggers refuse one unless both the item (as fetched) and its source (now) allow summaries, and a source changed to headline and link loses the summaries of its items at once. Concurrency (D1 has no interactive transactions): a run claims an item by bumping `attempts` with a guarded UPDATE, and writes its answer only while `attempts` still holds the value it set, so two runs at once cannot both ask the model about one item or overwrite each other. |
0017_chamber_brands.sql | A chamber's brand, and the addresses (hosts) that show it. A chamber with no brand row is shown with its own name in both languages, the first character of that name as its mark, and the platform's default colour. Concurrency: each save bumps the version from the one the editor opened, and the trigger refuses a save from a stale form. The first save inserts version 1, and a second first save collides on the primary key. |
0018_positions_end_with_place.sql | A position ends when its holder's place in the committee ends: when they leave, are removed, or their membership ends. Until now such a position stayed open, so it came back into the Exco when the holder rejoined. The ending is written by the same batch that ends the place; ended_how says it ended that way rather than by an admin's decision, and end_reason holds the same code (the interface shows it from its catalogue). |
0019_standing_guards.sql | Guards for points, tiers and committee places that a command's reading alone cannot hold, because D1 reads before it writes (see 0008). Each abort names a conflict the command turns into a try_again refusal; on the retry the command reads the new state and refuses or proceeds by it. |
0020_erase_imported_details.sql | Imported details can be erased. 0014 kept an imported member's row exactly as imported, refusing any update. A completed deletion request (one chamber's, or a whole account erasure) must remove the person's details from it, so the only update now allowed is the erasure itself: every personal field set to NULL, and nothing else changed. Deleting a row is still refused: the row also records how the membership came about and the language to write to the member in. |
0023_news_descriptions_withdrawn.sql | A source whose summary terms are withdrawn loses the feed descriptions stored under them. Migration 0016 clears the machine summaries when terms move to headline and link; the publisher's own descriptions stayed stored (and shown) after permission was withdrawn. The items keep their own terms, those in force when they were fetched, so 0026 can still ask for summaries again if the terms come back; the CHECK on news_items allows a summary_allowed item with no description. |
0026_news_terms_restored.sql | A source moved back to allowing summaries (after headline and link) has its items summarised again. Migration 0016 resets a source's items only when its summaries are withdrawn; without this the items it had translated stayed 'done' with no summary, so they were never asked about again and showed "summary pending" for good. Only items fetched under summary terms are reset: an item's own terms are those in force when it was fetched. |
0028_events.sql | Events, their reach, and the waves in which registration opens. An event belongs to one unit (a committee, a branch or the chamber) and is offered to a reach: the organising committee, its whole branch, or the whole chamber. The reach never goes above the owner's level: a branch committee reaches its committee or its branch; a national committee its committee or the chamber; a branch its branch; the chamber the chamber. reach_unit_id is the unit the reach names, checked here. Waves are an ordered list of an audience and the time registration opens to it. Saving waves replaces the event's whole list. Concurrency: every change to an event (its details, status or waves) bumps events.version from the version the admin opened, and the trigger refuses a change from a stale reading, so two admins editing at once cannot overwrite each other. The chamber's default pattern is guarded the same way. |
0029_profiles.sql | A member's profile: one per person, used in every chamber they belong to. The business card (a name, at least one company with its industry and the person's position there, and at least one way to reach them) is required from the first save. Who may see each optional field is the member's choice, kept in visibility_json as field -> 'members' | 'hidden'; the names and each company's name are always shown to members. The read path is profileCard in src/capabilities/profiles.ts. Concurrency: each save bumps profiles.version from the version the member opened, and the trigger refuses a save from a stale profile, so two saves at once cannot silently overwrite each other. The first save inserts version 1; a second first save collides on the primary key. Companies are replaced with the profile in the same batch. Personal data: exported by export.mine, deleted by an account erasure, and by a chamber's deletion when no other chamber still holds the person (capabilities/data-requests.ts). |
0030_news_digest.sql | The chamber's daily news digest: its settings, the admins' editorial changes, and the snapshot assembled each day at the chamber's chosen time. A digest is assembled once per day for the chamber, and also for each branch (and committee) when the chamber says it varies by them. A variant is the chamber's digest with the editorial changes made at its unit and above it; the nearest unit's change to an item wins. Nothing here is about a member: the snapshot holds news headlines and what admins wrote for the chamber, and the editorial tables name the admin who acted by user id only, so an erased account shows as a deleted person wherever its email is joined in. Concurrency (D1 has no interactive transactions): every write to one chamber's digest for one day (an assembly or an editorial change) bumps that day's row in digest_days, and the trigger refuses a bump from a stale reading. So an edit made while the digest is being assembled, or two edits made from the same reading, cannot both commit; the loser is refused (or the assembly tries again) instead of overwriting the other. The unique index on snapshots keeps one per unit and day. |
0031_push.sql | Web push: each device a person allowed push on, their choice of which notices reach them on which channel, and a 'push' channel in the outbox. |
0032_event_registrations.sql | Registrations for events: a place taken, a place on the waitlist, or a place offered from it. A registration holds the member and their guests (names, as typed), and so takes 1 + the number of guests places. Registered and offered rows hold places; together they never exceed the event's capacity, which the triggers below check inside the same write, so two people taking the last place at once cannot both have it, and two offers cannot be made for one place. A person has at most one live registration (registered, waitlisted or offered) per event. Statuses move only forward: registered -> cancelled | released waitlisted -> offered | cancelled | released offered -> registered | declined | expired | released 'released' is what a cancelled event does to every live registration. Concurrency: every change bumps version from the version read, and the trigger refuses a change from a stale reading. An offer is taken only before it expires, checked against the time the change is written. Registrations are personal data: a data export includes them, and both erasures delete them. |
0033_media.sql | Media: images people upload, such as profile photos (and later event photos and chamber logos). The bytes live in the MEDIA R2 bucket, never in the database. Each upload is one `media` row under an opaque id, and one `media_objects` row per stored object (each fixed size is its own object, keyed media/<id>/<size>.webp). See src/media.ts. R2 cannot be written in the same batch as D1, so objects are written before the batch that records them, and removed afterwards through `media_deletions`: a change that drops media queues its object keys in the same batch, and the queue is worked off right after the change commits and again by the scheduled handler, until R2 confirms each delete. Personal data: a profile photo is exported by export.mine and deleted by both erasures (capabilities/data-requests.ts). Backups copy these rows but never the R2 objects. |
0034_member_search.sql | The member directory's search index: one row per saved profile, holding only what a chamber's members may see of it. Search reads this table, never the profile, so a field a member hides can never be matched. member_search_source says what a profile puts in the index: its names and company names (always shown to members), and its companies' industries as tokens msic-a ... msic-u, only while the member shows their industry ('members' is the default, as in DEFAULT_PROFILE_VISIBILITY). Triggers on profiles and profile_companies rewrite the person's row from it on every change, in the same statement as the change, so the index cannot lag behind a save or an erasure. A restore rebuilds the whole index from the view (src/backup.ts), so the index is never copied into a backup. The trigram tokenizer matches any run of three or more characters, which works for Chinese names, which have no spaces between words; shorter words use GLOB on this same table (src/capabilities/directory.ts). Which chamber a person shows in, and whether their membership is active, is decided when the index is queried. Personal data: a copy of part of the profile, removed with it by the triggers. |
0035_matching.sql | Business matching: a member's opt-in per chamber, and the record of the vectors embedded for it. A member opts in to matching in one chamber after reading the plain explanation, recorded with the notice version. While opted in, text built from the member-visible fields of their profile (what they offer and need, each company's industry and description) is embedded and stored in Vectorize, in the chamber's own namespace: one vector for what they offer and one for what they need. The embedding runs off a queue (capabilities/matching.ts, platform.ts). State is kept here; the vectors themselves live in Vectorize. Each opt-in row says which profile version its vectors should reflect (wanted_version) and which they do (embedded_version). A queue message carries the opt-in id and the profile version it was sent for, so a retried or overtaken message is dropped. Vectors are deleted on opt-out, when the membership ends (resigned or terminated), when the profile is deleted, and on either erasure: whatever removes the opt-in row moves its vector ids to matching_vector_deletions in the same batch (trigger below), and the platform deletes them from Vectorize and then the rows. That table holds only random ids and a chamber id. Personal data: matching_optins and matching_vectors are exported by export.mine and deleted by both erasures (capabilities/data-requests.ts). |
0036_digest_email.sql | The news digest by email (#50). A member chooses, per chamber, whether the chamber's digest is emailed to them daily or weekly, and in which language. No row means no email. Both tables hold personal data: they are in the personal data export, and a chamber's deletion of the person's data (or an account erasure) removes them. |
0037_deletion_reaches_everything.sql | What a completed deletion must also reach. |
0038_news_translation_recheck.sql | News items to check once more under the translation rules of ticket #61: each item's language is read from its own text (a source labelled English may publish Malay headlines), and a stored headline translation that changed a number, carries kana or stray Latin words, or is missing for a language the item now needs is cleared and asked for again. The rules are code (news/language.ts, news/translation-check.ts), not SQL, so this migration only queues every stored item; the news analysis run drains the queue a bounded number at a time before it asks the model, and removes each item as it is checked, so each is checked once. Holds no personal data. |
0039_event_check_ins.sql | Checking people in at the door of an event. A check-in records that the person holding a registration arrived: who checked them in (event staff, or the person themselves), how (scanning their member card, the person scanning the venue's code, ticking their name on the list, or as a walk-in) and when. `checked_in_at` is when it happened on the device; `recorded_at` is when the server received it, later for a check-in queued on a device without a connection. Each check-in carries the key its device generated, unique, so a check-in sent twice (a queue replayed after a dropped connection) is recorded once. A registration has at most one check-in that stands; an undone check-in keeps its row, with who undid it, when and why, and the person may be checked in again. A walk-in is a registration made at the door, while the event is on and has space: it holds one place like any other, so the capacity triggers of 0032 still hold, and only the rule that registration closes when the event starts is lifted for it. Check-ins are personal data: a data export includes them, and both erasures delete them (before the registrations they belong to). |
0040_event_photos.sql | Event photos: uploaded by event staff (published at once) or submitted by attendees (waiting for an admin), moderated, and picked by the publisher for the public showcase of past events. The bytes live in the MEDIA bucket through the media module (src/media.ts): each photo is stored at fixed widths (256, 1024, 2048) keeping its shape, as WebP with no metadata, under media/<photo id>/<width>.webp. The photo's id is its media id. Event photos keep their own rows here rather than in `media`: that table's purpose check and its required owner could only change by rebuilding a table other tables reference, and an event photo outlives the link to whoever uploaded it. Dropped objects go through the shared `media_deletions` queue. Statuses: pending (an attendee's photo, waiting), approved, rejected. An admin may approve a rejected photo or reject an approved one; nothing returns to pending. Only an approved photo can be in the showcase, and rejecting it takes it out. Concurrency: every moderation, caption or showcase change bumps version from the version read, and the trigger refuses a stale one. An upload stores its objects before its batch runs, so its insert is conditional on what made the uploader allowed (their admin grant, or their registration) still holding, and a guard then fails the batch if the photo was not written. Personal data: who uploaded a photo is the uploader's. Export lists their photos. Both erasures delete their photos still pending or rejected (with the objects) and unlink them from approved ones, which stay as the chamber's record of the event, shown as uploaded by a deleted person. The people in a photo are not identified anywhere. Backups copy these rows but never the R2 objects. |
0041_event_notices.sql | Event reminders and wave-opening notices (#38). The day before an event, everyone holding a place (registered, or offered one from the waitlist) is reminded; when a wave opens to a member who chose to hear about it, they are told registration is open to them. Both are sent by the scheduled handler. |
0042_suggestions.sql | Weekly matching suggestions, each with a one-line reason, and the people a member dismissed. Each week (from Monday, in the chamber's time zone) the scheduled run gives each opted-in member a batch of a few suggested members, found by nearest neighbours between their needs and others' offers and the reverse (capabilities/suggestions.ts). A member who has none showing may ask for a new batch, at most once an hour. The latest batch is what the member sees, re-checked on every read against who may still be suggested. Each suggestion carries a reason in both interface languages: written by a language model from the two cards as the viewer sees them (reason_source 'model'), or, when the model is unavailable or its answer unusable, a template naming the shared industry or the need (reason_source 'template', worded by the catalogues). Every prompt sent to the model and its answer is kept in matching_reason_prompts for audit. Personal data: all four tables name two people. They are in the viewer's export, and either person's erasure (one chamber's or the whole account) deletes every row naming them (capabilities/data-requests.ts). |
0043_introductions.sql | Introductions: a member asks to be introduced to another member of the same chamber, with a short note. The other person accepts or declines; a request not answered in 14 days expires on the schedule. Once accepted, each sees the other's contact details on their card, even those they hide from members in general (profileCard in capabilities/profiles.ts). Both people must be active members of the chamber and opted in to matching there, when the request is made and when it is accepted (triggers below). Between two people in one chamber only one request is open at a time, in either direction (the unique index). A declined request keeps its requester from asking the same person again for 90 days, counted from the decline. A person whose data is deleted (by the chamber or with their account) leaves the record to the other side as "a deleted person": requests still open are closed, the note they wrote is replaced with a placeholder, and contacts are no longer shared, even if they join again. Personal data: the note. Exported by export.mine and cleared by both erasures (capabilities/data-requests.ts). |
0044_chamber_logos.sql | A chamber's logo is uploaded and kept in the MEDIA R2 bucket through the media module (src/media.ts), no longer fetched from an address the chamber typed. 1. The typed address goes. Every brand row keeps everything else; a chamber that had a logo address shows its mark until an admin uploads the logo. |
0045_event_staff.sql | Event staff, an event's completion, and organising points. 1. Event staff: the publisher (an admin whose reach covers the event's owner) names active members of the chamber to check people in at one event. A person is named at most once at a time; removing them keeps the row, with who removed them and when, and they may be named again. Naming needs a published event that has not completed, and an active member. 2. Completion: an event is completed one day after it ends (the schedule), or when the publisher marks it so after it ends. It happens once, and is final: the door closes for everyone (no check-in is written or undone afterwards, so who attended is fixed), the event can no longer be cancelled, and its time, venue, places, reach and waves are fixed. A completion by the schedule has no person. 3. Organising awards: the event's admins award organising points to a member who helped, spoke or volunteered, once per person per event, ever: removing an award keeps its row, with who removed it, when and why, and the points are taken back as a correction. Staff and organising awards are personal data: a data export includes them, and both erasures delete them. |
0046_board.sql | The asks and offers board: a member of a chamber posts what they are looking for (an ask) or what they can provide (an offer), with a title, details, optionally one of the industries on their profile, and an expiry of at most 90 days. Other members browse and search the chamber's board and answer a post by asking to be introduced to its author (introductions, source 'board', source_ref the post's id). The author closes a post when it is done; a chamber admin or the platform operator removes one, with a reason the author is told. A post is on the board while it is open and its expiry has not passed (expiry is read against the clock, not stored as a status). Closed, removed and expired posts stay in the author's own list with their state. Posting is for active members of the chamber with matching on there (trigger below), and each has at most five posts on the board at a time in a chamber (the limit trigger), so a raced second post cannot slip past the count. An introduction asked from the board names a post that is still on it, by the person asked, as the request commits (trigger below), so a close or removal raced past the request's read still stops it. Personal data: the title, details (and their search form) and removal reason, which are about the author. Exported by export.mine; both erasures (the chamber's and the account's) delete the author's posts (capabilities/data-requests.ts). |
0047_introduction_outcomes.sql | Introduction outcomes: thirty days after an introduction is accepted, each of its two people is asked once, in their inbox (and by push as they chose), whether business came of it: none, in talks, or a deal done. Each answers once per introduction and may change the answer for 60 days from being asked; after that it is locked (a trigger). The schedule asks; the primary key keeps it to once per person and introduction even when two runs overlap. Chamber totals count introductions by the month they were requested, in the chamber's time zone, and by the branch of the requester's membership when they asked, kept here on the introduction (requester_branch_id) so a member who later leaves and joins another branch does not move it. Personal data: each person's answer. Exported by export.mine and deleted by both erasures (capabilities/data-requests.ts); totals are then counted from the answers that remain. |
0048_operations_and_log_reach.sql | Every transition names the operation that made it, as the history of an application, a membership, a committee place, a tier or a data request is meant to. The executor's operation id is written in the same batch as the transition; rows made before this migration, and by scheduled work, have none. |
0049_wave_pattern_audiences.sql | The chamber's default release pattern covers every audience, not only the public (#27): the organising Exco, the tiers (each from highest to lowest), Friends and the public can each be included or left out. Existing patterns keep every audience they had: the new flags start on. The organising Exco wave applies only to a committee's event, the public only to a chamber-wide one, as before. A pattern must open to the tiers or to Friends, so every event drawn from it has a wave whatever its owner and reach. Holds no personal data. |
0050_profile_stand_ins.sql | Profiles, completed: stand-ins for members who have not saved a profile, and industry tags on what a member offers and needs. Industry tags: what a business offers and what it is looking for may each be tagged with MSIC sections (A ... U, as profile_companies.industry), kept as JSON arrays on the profile. They follow the visibility of the offers and the needs field (src/capabilities/profiles.ts). Stand-ins: until a member saves a profile, the details they gave the chamber stand in for it: their latest approved application to that chamber, else the row they were imported with, as member.list reads them. profile_stand_ins holds one row per person and chamber, only while the person has no profile and the details are not erased. Of those details, members see the name, the company, its industry and the position as given (the industry is free text there); the phone stays hidden, as contacts on a profile start hidden. An import may leave out the name: such a member stands in by their company, with no name (the directory lists them after everyone named); a row with neither, as erasure leaves it, does not. The directory's search index (0034) is rebuilt with a chamber column: a profile's row has none and applies in every chamber; a stand-in's row is for its chamber. Triggers on applications and imported_member_details keep stand-in rows in step, and a profile's first save drops them (its insert trigger rewrites every row of the person). The index still holds only what members may see: names, company names, shown industries, the stated industry of a stand-in, and the offer and need tags while those fields are shown. A restore rebuilds it from member_search_source (src/backup.ts). Personal data: the tags are part of the profile and go with it; stand-in rows are copies of application and import details, rewritten when those are erased. |
0051_application_push_choices.sql | Before notice.setPreferences ignored kinds the person is not offered, the settings form sent a choice about new applications to review (applicationReceived) for every member, so saving it as a non-admin stored that kind as turned off, and it stayed off once they were appointed. Only admins are offered that choice, so a stored one belonging to someone with no current admin grant was never theirs to make: it is cleared, and they get the default when appointed. A choice an admin made (stored while they hold a grant) is kept. |
0052_news_title_key.sql | Each stored headline checks that no other source stored the same headline in the last few days (news.ts, fetchOne), so two queue consumers fetching at once cannot both store one story. |