Schema atlas

Generated from source

108 tables, walked from 155 migration files. Regenerated by scripts/gen-schema-atlas.mjs from the kvick-bike/migrations/ directory — DO NOT hand-edit this file; edits are overwritten. Last generated: 2026-09-19. M### in the "Since" column names the migration file (e.g. M070 = migrations/070_rental_bookings.sql).

Table index

Table	Cols	Indexes	Triggers	Since
_bak107_zz	2	0	0	M107
access_checks	6	0	0	M159
announcement_images	6	0	0	M144
audit_events	29	11	0	M040
audit_log	8	0	0	M001
audit_mutations	10	3	0	M040
audit_row_changes	7	1	0	M040
brand_groups	4	1	0	M133
bulk_items	7	0	0	M027
categories	12	1	0	M018
channel_dealers	10	1	0	M126
consignment_payouts	17	2	0	M118
customer_account_ledger	11	2	0	M003
customer_bikes	22	4	4	M003
customer_discounts	5	1	0	M059
customer_emails	8	2	0	M038
customer_family_links	5	1	0	M011
customer_family_members	7	1	6	M015
customer_loyalty_ledger	8	2	0	M116
customer_marketing_preferences	9	0	0	M003
customer_notes	8	1	0	M003
customer_phones	8	2	0	M038
customer_pickup_authorisations	9	1	0	M115
customer_tags	4	0	0	M003
customer_wishlist	9	1	0	M115
customers	45	2	9	M002
customers_yoy_summary	6	1	0	M017
device_unlocks	8	1	0	M158
discount_actions	6	1	0	M012
discount_conditions	6	1	0	M012
discounts	18	1	0	M012
duty_templates	13	1	0	M144
feature_registry	10	1	0	M044
gift_cards	9	1	0	M038
held_sales	19	2	0	M027
idempotency_records	9	2	0	M137
inventory	33	3	0	M019
inventory_images	6	0	0	M091
inventory_locations	12	0	0	M019
inventory_movements	14	4	0	M019
inventory_order_queue	14	3	0	M020
inventory_units	30	3	0	M054
kit_components	5	1	0	M061
manufacturers	9	1	0	M018
migration_jobs	17	1	0	M049
migration_presets	9	0	0	M049
note_images	6	0	0	M129
payments	15	2	0	M027
permissions	5	0	0	M008
product_barcodes	8	3	0	M018
product_variants	16	3	0	M018
products	30	4	0	M018
purchase_order_lines	22	5	0	M021
purchase_orders	24	2	0	M025
recovery_codes	7	0	0	M158
rental_addons	8	1	0	M102
rental_booking_bikes	13	4	0	M070
rental_booking_riders	5	1	0	M070
rental_bookings	40	1	0	M079
rental_drafts	3	0	0	M099
rental_hold_policy	3	0	0	M097
rental_id_images	6	0	0	M096
rental_rate_cards	14	1	0	M119
rental_waivers	24	3	0	M093
request_limits	4	2	0	M157
rma	13	2	0	M024
rma_lines	7	1	0	M024
role_permissions	2	0	0	M008
roles	8	0	0	M008
search_synonym_groups	7	1	0	M010
search_synonym_terms	5	2	0	M010
service_bike_locations	6	1	0	M104
service_menu	14	2	0	M030
service_menu_parts	5	2	0	M145
service_signoffs	12	1	0	M112
service_ticket_lines	25	4	0	M030
service_ticket_messages	8	1	0	M030
service_ticket_status_history	8	2	0	M030
service_tickets	50	5	0	M030
service_urgency_levels	6	1	0	M104
session_devices	2	1	0	M160
sessions	4	1	0	M001
shop_announcements	11	1	0	M144
shop_config	64	0	0	M035
shop_note_comments	6	1	0	M148
shop_note_reads	3	1	0	M149
shop_notes	11	3	0	M127
signin_devices	5	0	0	M160
signin_lockouts	8	1	0	M160
staff	18	1	0	M001
staff_duties	17	3	0	M144
staff_feature_state	8	1	0	M043
staff_messages	7	2	0	M144
staff_permission_overrides	8	0	0	M008
staff_screen_permissions	3	0	0	M008
subcategories	12	3	0	M018
supplier_products	12	3	0	M020
suppliers	22	1	0	M020
tags	6	0	0	M003
tax_categories	12	0	0	M018
tax_rates	10	0	0	M018
transaction_discounts	9	2	0	M027
transaction_lines	24	3	0	M027
transaction_taxes	7	2	0	M027
transactions	34	6	0	M004
unlock_accounts	9	0	0	M158
unlock_codes	7	0	0	M158
website_content	7	1	0	M122

Dropped

Tables that appeared in an earlier migration and were later DROPped:

• search_customers_fts

---

_bak107_zz

Introduced: M107. Columns: 2. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
kind	TEXT	yes			M107
id	INTEGER	yes			M107

access_checks

Introduced: M159. Columns: 6. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
day	TEXT	no		CHECK (day GLOB '[0-9][0-9][0-9][0-9]-[0-9]…	M159
outcome	TEXT	no		CHECK (outcome IN ('ok', 'would_refuse', 'r…	M159
reason	TEXT	no	''		M159
n	INTEGER	no	1	CHECK (n >= 1)	M159
first_at	TEXT	no	(datetime('now')		M159
last_at	TEXT	no	(datetime('now')		M159

Checks (table-level):

• ((outcome = 'ok' AND reason = '') OR (outcome <> 'ok' AND reason IN ( 'no_token', 'malformed', 'wrong_algorithm', 'unknown_key', 'keys_unavailable', 'bad_signature', 'wrong_issuer', 'wrong_audience', 'expired', 'not_yet_valid', 'not_configured'))) (M159)

announcement_images

Introduced: M144. Columns: 6. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M144
mime	TEXT	no			M144
data	TEXT	no			M144
bytes	INTEGER	yes			M144
uploaded_by_staff_id	INTEGER	yes		FK → staff.id	M144
created_at	TEXT	no	CURRENT_TIMESTAMP		M144

audit_events

Introduced: M040. Columns: 29. Indexes: 11. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M040
occurred_at	TEXT	no	(datetime('now')		M040
occurred_at_local	TEXT	yes			M040
category	TEXT	no			M040
event_type	TEXT	no			M040
severity	TEXT	no	'info'		M040
actor_type	TEXT	no	'staff'		M040
actor_staff_id	INTEGER	yes		FK → staff.id	M040
actor_session_id	INTEGER	yes			M040
actor_label	TEXT	no	'unknown'		M040
actor_ip	TEXT	yes			M040
actor_user_agent	TEXT	yes			M040
actor_register_id	TEXT	yes			M040
target_table	TEXT	yes			M040
target_record_id	INTEGER	yes			M040
target_label	TEXT	yes			M040
context_ticket_id	INTEGER	yes			M040
context_transaction_id	INTEGER	yes			M040
context_customer_id	INTEGER	yes			M040
context_screen	TEXT	yes			M040
detail_table	TEXT	yes			M040
detail_id	INTEGER	yes			M040
previous_event_id	INTEGER	yes		FK → audit_events.id	M040
previous_hash	TEXT	yes			M040
current_hash	TEXT	no			M040
duration_ms	INTEGER	yes			M040
outcome	TEXT	no	'success'		M040
summary	TEXT	no			M040
build_version	TEXT	yes			M040
Indexes:

• idx_audit_occurred — (occurred_at) (M040)
• idx_audit_category — (category, occurred_at) (M040)
• idx_audit_event_type — (event_type, occurred_at) (M040)
• idx_audit_actor_staff — (actor_staff_id, occurred_at) (M040)
• idx_audit_target — (target_table, target_record_id) (M040)
• idx_audit_ctx_txn — (context_transaction_id) · WHERE context_transaction_id IS NOT NULL (M040)
• idx_audit_ctx_customer — (context_customer_id) · WHERE context_customer_id IS NOT NULL (M040)
• idx_audit_ctx_ticket — (context_ticket_id) · WHERE context_ticket_id IS NOT NULL (M040)
• idx_audit_severity — (severity) · WHERE severity IN ('warning','error','critical') (M040)
• idx_audit_outcome — (outcome) · WHERE outcome != 'success' (M040)
• idx_audit_chain — (current_hash) (M040)

audit_log

Introduced: M001. Columns: 8. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M001
ts	TEXT	no	(datetime('now')		M001
actor_staff_id	INTEGER	yes		FK → staff.id	M001
actor_name	TEXT	yes			M001
action	TEXT	no			M001
detail	TEXT	yes			M001
prev_hash	TEXT	yes			M001
hash	TEXT	no			M001

audit_mutations

Introduced: M040. Columns: 10. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M040
event_id	INTEGER	no		FK → audit_events.id	M040
operation	TEXT	no			M040
before_json	TEXT	yes			M040
after_json	TEXT	yes			M040
changed_columns_json	TEXT	yes			M040
diff_json	TEXT	yes			M040
trigger_source	TEXT	no	'user_action'		M040
trigger_event_id	INTEGER	yes		FK → audit_events.id	M040
rows_affected	INTEGER	no	1		M040
Indexes:

• idx_mutations_event — (event_id) (M040)
• idx_mutations_operation — (operation) (M040)
• idx_mutations_cascade — (trigger_event_id) · WHERE trigger_event_id IS NOT NULL (M040)

audit_row_changes

Introduced: M040. Columns: 7. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M040
changed_at	TEXT	no	(datetime('now')		M040
table_name	TEXT	no			M040
row_id	INTEGER	yes			M040
op	TEXT	no			M040
before_json	TEXT	yes			M040
after_json	TEXT	yes			M040
Indexes:

• idx_rowchg — (table_name, row_id, id) (M040)

brand_groups

Introduced: M133. Columns: 4. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
group_name	TEXT	no			M133
member	TEXT	no			M133
note	TEXT	yes			M133
created_at	TEXT	no	CURRENT_TIMESTAMP		M133
Indexes:

• idx_brand_groups_member — (member) (M133)

bulk_items

Introduced: M027. Columns: 7. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M027
slug	TEXT	no			M027
name	TEXT	no			M027
price_cents	INTEGER	no		CHECK (price_cents >= 0)	M027
sort_order	INTEGER	no	0		M027
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M027
created_at	TEXT	no	CURRENT_TIMESTAMP		M027

categories

Introduced: M018. Columns: 12. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
code	TEXT	yes			M018
name	TEXT	no			M018
description	TEXT	yes			M018
default_tax_category_id	INTEGER	yes		FK → tax_categories.id	M018
is_discountable	INTEGER	no	1	CHECK (is_discountable IN (0,1))	M018
is_loyalty_eligible	INTEGER	no	1	CHECK (is_loyalty_eligible IN (0,1))	M018
display_order	INTEGER	no	0		M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
aim_id	INTEGER	yes			M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018
updated_at	TEXT	no	CURRENT_TIMESTAMP		M018
Indexes:

• idx_categories_aim_u — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M130)

channel_dealers

Introduced: M126. Columns: 10. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M126
name	TEXT	no			M126
city	TEXT	yes			M126
province	TEXT	yes			M126
is_self	INTEGER	no	0		M126
is_active	INTEGER	no	1		M126
connected_at	TEXT	yes			M126
notes	TEXT	yes			M126
created_at	TEXT	no	CURRENT_TIMESTAMP		M126
updated_at	TEXT	no	CURRENT_TIMESTAMP		M126
Indexes:

• idx_channel_dealers_self — UNIQUE · (is_self) · WHERE is_self = 1 (M126)

consignment_payouts

Introduced: M118. Columns: 17. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M118
consignor_customer_id	INTEGER	no		FK → customers.id	M118
unit_id	INTEGER	yes		FK → inventory_units.id	M118
variant_id	INTEGER	yes		FK → product_variants.id	M118
description	TEXT	no			M118
transaction_id	INTEGER	yes		FK → transactions.id	M118
transaction_line_id	INTEGER	yes		FK → transaction_lines.id	M118
sold_cents	INTEGER	no			M118
split_pct	INTEGER	yes			M118
owed_cents	INTEGER	no	0		M118
paid_cents	INTEGER	no	0		M118
paid_at	TEXT	yes			M118
paid_method	TEXT	yes			M118
paid_by_staff_id	INTEGER	yes		FK → staff.id	M118
notes	TEXT	yes			M118
created_at	TEXT	no	CURRENT_TIMESTAMP		M118
updated_at	TEXT	no	CURRENT_TIMESTAMP		M118
Indexes:

• idx_consign_payout_consignor — (consignor_customer_id) (M118)
• idx_consign_payout_line — UNIQUE · (transaction_line_id) · WHERE transaction_line_id IS NOT NULL (M118)

customer_account_ledger

Introduced: M003. Columns: 11. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M003
customer_id	INTEGER	no		FK → customers.id	M003
occurred_at	TEXT	no	CURRENT_TIMESTAMP		M003
amount_cents	INTEGER	no			M003
entry_type	TEXT	no			M003
reference_kind	TEXT	yes			M003
reference_id	INTEGER	yes			M003
staff_id	INTEGER	yes			M003
notes	TEXT	yes			M003
created_at	TEXT	no	CURRENT_TIMESTAMP		M003
aim_id	INTEGER	yes			M038
Indexes:

• idx_acctledger_customer — (customer_id, occurred_at DESC) (M003)
• idx_acct_ledger_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)

customer_bikes

Introduced: M003. Columns: 22. Indexes: 4. Triggers: 4.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M003
customer_id	INTEGER	no			M003
equipment_type	TEXT	no	'bike'		M003
brand	TEXT	yes			M003
model	TEXT	yes			M003
model_year	INTEGER	yes			M003
serial_number	TEXT	yes			M003
color	TEXT	yes			M003
frame_size	TEXT	yes			M003
notes	TEXT	yes			M003
is_active	INTEGER	no	1		M003
retired_at	TEXT	yes			M003
retired_reason	TEXT	yes			M003
created_at	TEXT	no	CURRENT_TIMESTAMP		M003
updated_at	TEXT	no	CURRENT_TIMESTAMP		M003
source	TEXT	yes			M114
sold_transaction_id	INTEGER	yes		FK → transactions.id	M114
warranty_starts_at	TEXT	yes			M114
service_interval_days	INTEGER	yes			M114
previous_owner_customer_id	INTEGER	yes		FK → customers.id	M114
transferred_at	TEXT	yes			M114
gifted_by_customer_id	INTEGER	yes		FK → customers.id	M114

Foreign keys (table-level):

• customer_id → customers(id) (M003)

Indexes:

• idx_cbikes_customer — (customer_id) · WHERE is_active=1 (M003)
• idx_customer_bikes_serial — (serial_number) (M114)
• idx_customer_bikes_prev_owner — (previous_owner_customer_id) (M114)
• idx_customer_bikes_cust_bike_u — UNIQUE · (customer_id, brand, model, model_year, serial_number) (M138)

Triggers:

• trg_search_cust_fts_bike_ai — AFTER INSERT (M113)
• trg_search_cust_fts_bike_au — AFTER UPDATE (M113)
• trg_search_cust_fts_bike_ad — AFTER DELETE (M113)
• trg_search_cust_fts_bike_au — AFTER UPDATE (M117)

customer_discounts

Introduced: M059. Columns: 5. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M059
customer_id	INTEGER	no			M059
discount_id	INTEGER	no			M059
created_at	TEXT	no	CURRENT_TIMESTAMP		M059
created_by	INTEGER	yes			M059

Unique (table-level):

• (customer_id, discount_id) (M059)

Indexes:

• idx_customer_discounts_customer — (customer_id) (M059)

customer_emails

Introduced: M038. Columns: 8. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M038
customer_id	INTEGER	no		FK → customers.id	M038
email_address	TEXT	no			M038
email_type	TEXT	yes	'primary'		M038
is_primary	INTEGER	no	0		M038
is_subscribed	INTEGER	no	1		M038
aim_id	INTEGER	yes			M038
created_at	TEXT	yes	CURRENT_TIMESTAMP		M038
Indexes:

• idx_customer_emails_cust — (customer_id) (M038)
• idx_customer_emails_cust_addr_u — UNIQUE · (customer_id, email_address) · WHERE email_address IS NOT NULL (M135)

customer_family_links

Introduced: M011. Columns: 5. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M011
customer_a_id	INTEGER	no			M011
customer_b_id	INTEGER	no			M011
created_at	TEXT	no	CURRENT_TIMESTAMP		M011
created_by_staff_id	INTEGER	yes			M011

Foreign keys (table-level):

• customer_a_id → customers(id) (M011)
• customer_b_id → customers(id) (M011)

Unique (table-level):

• (customer_a_id, customer_b_id) (M011)

Checks (table-level):

• (customer_a_id < customer_b_id) (M011)

Indexes:

• idx_cfamily_b — (customer_b_id) (M011)

customer_family_members

Introduced: M015. Columns: 7. Indexes: 1. Triggers: 6.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M015
customer_id	INTEGER	no		FK → customers.id	M015
name	TEXT	no			M015
relationship	TEXT	yes			M015
phone	TEXT	yes			M015
is_minor	INTEGER	no	0	CHECK (is_minor IN (0,1))	M015
created_at	TEXT	no	CURRENT_TIMESTAMP		M015
Indexes:

• idx_customer_family_members_customer — (customer_id) (M015)

Triggers:

• trg_search_cust_fts_family_ai — AFTER INSERT (M031)
• trg_search_cust_fts_family_au — AFTER UPDATE (M031)
• trg_search_cust_fts_family_ad — AFTER DELETE (M031)
• trg_search_cust_fts_family_ai — AFTER INSERT (M113)
• trg_search_cust_fts_family_au — AFTER UPDATE (M113)
• trg_search_cust_fts_family_ad — AFTER DELETE (M113)

customer_loyalty_ledger

Introduced: M116. Columns: 8. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M116
customer_id	INTEGER	no		FK → customers.id	M116
points	INTEGER	no			M116
reason	TEXT	no			M116
transaction_id	INTEGER	yes		FK → transactions.id	M116
note	TEXT	yes			M116
staff_id	INTEGER	yes		FK → staff.id	M116
created_at	TEXT	no	CURRENT_TIMESTAMP		M116
Indexes:

• idx_loyalty_ledger_customer — (customer_id, id) (M116)
• idx_loyalty_ledger_sale — UNIQUE · (transaction_id, reason) · WHERE transaction_id IS NOT NULL (M116)

customer_marketing_preferences

Introduced: M003. Columns: 9. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M003
customer_id	INTEGER	no			M003
channel	TEXT	no			M003
is_subscribed	INTEGER	no	0		M003
consent_source	TEXT	yes			M003
consent_at	TEXT	yes			M003
unsubscribed_at	TEXT	yes			M003
created_at	TEXT	no	CURRENT_TIMESTAMP		M003
updated_at	TEXT	no	CURRENT_TIMESTAMP		M003

Foreign keys (table-level):

• customer_id → customers(id) (M003)

Unique (table-level):

• (customer_id, channel) (M003)

customer_notes

Introduced: M003. Columns: 8. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M003
customer_id	INTEGER	no			M003
author_staff_id	INTEGER	yes			M003
note_text	TEXT	no			M003
is_pinned	INTEGER	no	0		M003
is_deleted	INTEGER	no	0		M003
created_at	TEXT	no	CURRENT_TIMESTAMP		M003
updated_at	TEXT	no	CURRENT_TIMESTAMP		M003

Foreign keys (table-level):

• customer_id → customers(id) (M003)

Indexes:

• idx_cnotes_customer — (customer_id) · WHERE is_deleted=0 (M003)

customer_phones

Introduced: M038. Columns: 8. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M038
customer_id	INTEGER	no		FK → customers.id	M038
phone_number	TEXT	no			M038
phone_type	TEXT	yes	'mobile'		M038
is_primary	INTEGER	no	0		M038
is_sms_capable	INTEGER	no	1		M038
aim_id	INTEGER	yes			M038
created_at	TEXT	yes	CURRENT_TIMESTAMP		M038
Indexes:

• idx_customer_phones_cust — (customer_id) (M038)
• idx_customer_phones_cust_num_u — UNIQUE · (customer_id, phone_number) · WHERE phone_number IS NOT NULL (M138)

customer_pickup_authorisations

Introduced: M115. Columns: 9. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M115
customer_id	INTEGER	no		FK → customers.id	M115
name	TEXT	no			M115
relationship	TEXT	yes			M115
note	TEXT	yes			M115
valid_until	TEXT	yes			M115
created_by_staff_id	INTEGER	yes		FK → staff.id	M115
created_at	TEXT	no	CURRENT_TIMESTAMP		M115
is_revoked	INTEGER	no	0	CHECK (is_revoked IN (0,1))	M115
Indexes:

• idx_pickup_auth_customer — (customer_id, is_revoked) (M115)

customer_tags

Introduced: M003. Columns: 4. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
customer_id	INTEGER	no			M003
tag_id	INTEGER	no			M003
applied_at	TEXT	no	CURRENT_TIMESTAMP		M003
applied_by_staff_id	INTEGER	yes			M003

Foreign keys (table-level):

• customer_id → customers(id) (M003)
• tag_id → tags(id) (M003)

customer_wishlist

Introduced: M115. Columns: 9. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M115
customer_id	INTEGER	no		FK → customers.id	M115
variant_id	INTEGER	yes		FK → product_variants.id	M115
description	TEXT	no			M115
note	TEXT	yes			M115
added_by_staff_id	INTEGER	yes		FK → staff.id	M115
created_at	TEXT	no	CURRENT_TIMESTAMP		M115
purchased_at	TEXT	yes			M115
purchased_transaction_id	INTEGER	yes		FK → transactions.id	M115
Indexes:

• idx_wishlist_customer — (customer_id) (M115)

customers

Introduced: M002. Columns: 45. Indexes: 2. Triggers: 9.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M002
account_number	INTEGER	yes			M002
display_name	TEXT	no			M002
primary_phone	TEXT	yes			M002
primary_email	TEXT	yes			M002
pricing_tier	TEXT	no	'a'		M002
popup_note	TEXT	yes			M002
general_notes	TEXT	yes			M002
credit_limit_cents	INTEGER	no	0		M002
customer_status	TEXT	no	'active'		M002
is_tax_exempt	INTEGER	no	0		M002
customer_since	TEXT	yes	CURRENT_TIMESTAMP		M002
is_deleted	INTEGER	no	0		M002
created_at	TEXT	yes	CURRENT_TIMESTAMP		M002
updated_at	TEXT	yes	CURRENT_TIMESTAMP		M002
version	INTEGER	no	1		M002
legal_name	TEXT	yes			M003
is_business	INTEGER	no	0		M003
primary_phone_type	TEXT	yes	'mobile'		M003
tax_exempt_certificate	TEXT	yes			M003
tax_exempt_category	TEXT	yes			M003
keywords	TEXT	yes			M003
is_consignor	INTEGER	no	0		M003
deleted_at	TEXT	yes			M003
external_aim_id	INTEGER	yes			M003
merged_into_customer_id	INTEGER	yes			M003
address_line1	TEXT	yes			M003
address_line2	TEXT	yes			M003
city	TEXT	yes			M003
province	TEXT	yes			M003
postal_code	TEXT	yes			M003
loyalty_number	INTEGER	yes			M005
loyalty_points	INTEGER	no	0		M005
loyalty_enrolled_at	TEXT	yes			M005
is_minor	INTEGER	no	0		M011
sales_discount_id	INTEGER	yes			M013
service_discount_id	INTEGER	yes			M013
how_heard	TEXT	yes			M115
goes_by	TEXT	yes			M115
preferred_contact	TEXT	yes			M115
date_of_birth	TEXT	yes			M115
deceased_at	TEXT	yes			M115
consignment_split_pct	INTEGER	yes			M115
consignment_payout_method	TEXT	yes			M115
consignment_expires_at	TEXT	yes			M115
Indexes:

• idx_customers_name — (display_name) · WHERE is_deleted=0 (M002)
• idx_customers_account — (account_number) (M002)

Triggers:

• trg_search_cust_fts_ai — AFTER INSERT (M010)
• trg_search_cust_fts_au — AFTER UPDATE (M010)
• trg_search_cust_fts_ad — AFTER DELETE (M010)
• trg_search_cust_fts_ai — AFTER INSERT (M031)
• trg_search_cust_fts_au — AFTER UPDATE (M031)
• trg_search_cust_fts_ad — AFTER DELETE (M031)
• trg_search_cust_fts_ai — AFTER INSERT (M113)
• trg_search_cust_fts_au — AFTER UPDATE (M113)
• trg_search_cust_fts_ad — AFTER DELETE (M113)

customers_yoy_summary

Introduced: M017. Columns: 6. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
customer_id	INTEGER	yes		PK · FK → customers.id	M017
t12m_spend_cents	INTEGER	no	0		M017
p12m_spend_cents	INTEGER	no	0		M017
yoy_delta_cents	INTEGER	no	0		M017
yoy_bucket	TEXT	no	'zero'	CHECK (yoy_bucket IN ('up','down','flat','n…	M017
computed_at	TEXT	no	(datetime('now')		M017
Indexes:

• idx_yoy_bucket — (yoy_bucket) (M017)

device_unlocks

Introduced: M158. Columns: 8. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
token_hash	TEXT	yes		PK	M158
account_id	INTEGER	yes			M158
method	TEXT	no			M158
created_at	TEXT	no	(datetime('now')		M158
expires_at	TEXT	no			M158
revoked_at	TEXT	yes			M158
ip	TEXT	yes			M158
user_agent	TEXT	yes			M158
Indexes:

• idx_device_unlocks_open — (revoked_at, expires_at) (M158)

discount_actions

Introduced: M012. Columns: 6. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M012
discount_id	INTEGER	no		FK → discounts.id	M012
type	TEXT	no		CHECK (type IN ('percent','dollar','set_pri…	M012
target	TEXT	no	'cart'	CHECK (target IN ('cart','category','line'))	M012
value	TEXT	no	'{}'		M012
created_at	TEXT	no	CURRENT_TIMESTAMP		M012
Indexes:

• idx_discount_actions_discount — (discount_id) (M012)

discount_conditions

Introduced: M012. Columns: 6. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M012
discount_id	INTEGER	no		FK → discounts.id	M012
type	TEXT	no		CHECK (type IN ( 'customer_attr','product_a…	M012
operator	TEXT	no	'eq'	CHECK (operator IN ('eq','gte','lte','in'))	M012
value	TEXT	no	'{}'		M012
created_at	TEXT	no	CURRENT_TIMESTAMP		M012
Indexes:

• idx_discount_conditions_discount — (discount_id) (M012)

discounts

Introduced: M012. Columns: 18. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M012
name	TEXT	no			M012
description	TEXT	yes			M012
start_at	TEXT	yes			M012
end_at	TEXT	yes			M012
stackable	INTEGER	no	0	CHECK (stackable IN (0,1))	M012
manager_threshold_cents	INTEGER	yes			M012
max_uses_total	INTEGER	yes			M012
max_uses_per_customer	INTEGER	yes			M012
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M012
created_by_staff_id	INTEGER	yes		FK → staff.id	M012
created_at	TEXT	no	CURRENT_TIMESTAMP		M012
updated_at	TEXT	no	CURRENT_TIMESTAMP		M012
archived_at	TEXT	yes			M012
kind	TEXT	no	'cart'	CHECK (kind IN ('customer','cart'))	M013
department	TEXT	yes		CHECK (department IS NULL OR department IN …	M013
applies_sales	INTEGER	no	0	CHECK (applies_sales IN (0,1))	M014
applies_service	INTEGER	no	0	CHECK (applies_service IN (0,1))	M014
Indexes:

• idx_discounts_active — (is_active, archived_at) (M012)

duty_templates

Introduced: M144. Columns: 13. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M144
title	TEXT	no			M144
body	TEXT	yes			M144
default_assigned_to_staff_id	INTEGER	yes		FK → staff.id	M144
recurrence	TEXT	no	'adhoc'	CHECK (recurrence IN ('daily','weekly','adh…	M144
recurrence_days	TEXT	yes			M144
active	INTEGER	no	1	CHECK (active IN (0,1))	M144
created_by_staff_id	INTEGER	no		FK → staff.id	M144
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M144
deleted_at	TEXT	yes			M144
version	INTEGER	no	1		M144
created_at	TEXT	no	CURRENT_TIMESTAMP		M144
updated_at	TEXT	no	CURRENT_TIMESTAMP		M144

Checks (table-level):

• (length(title) BETWEEN 1 AND 200) (M144)

Indexes:

• idx_duty_templates_active — (active) · WHERE is_deleted = 0 (M144)

feature_registry

Introduced: M044. Columns: 10. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
feature_key	TEXT	yes		PK	M044
module_key	TEXT	no			M044
display_name	TEXT	no			M044
description	TEXT	no			M044
frequency_class	TEXT	no		CHECK (frequency_class IN ('high','medium',…	M044
default_decay_uses	INTEGER	no			M044
default_decay_days	INTEGER	no			M044
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M044
created_at	INTEGER	no	(unixepoch()		M044
updated_at	INTEGER	no	(unixepoch()		M044
Indexes:

• idx_feature_registry_module — (module_key) (M044)

gift_cards

Introduced: M038. Columns: 9. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M038
code	TEXT	yes			M038
customer_id	INTEGER	yes		FK → customers.id	M038
balance_cents	INTEGER	no	0		M038
initial_cents	INTEGER	no	0		M038
issued_at	TEXT	yes			M038
is_active	INTEGER	no	1		M038
aim_id	INTEGER	yes			M038
created_at	TEXT	yes	CURRENT_TIMESTAMP		M038
Indexes:

• idx_gift_cards_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)

held_sales

Introduced: M027. Columns: 19. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M027
held_at	TEXT	no	CURRENT_TIMESTAMP		M027
held_by_staff_id	INTEGER	yes		FK → staff.id	M027
held_by_staff_name	TEXT	yes			M027
customer_id	INTEGER	yes			M027
customer_name	TEXT	yes			M027
customer_account_number	TEXT	yes			M027
customer_primary_phone	TEXT	yes			M027
customer_primary_email	TEXT	yes			M027
ticket_id	INTEGER	yes			M027
ticket_number	TEXT	yes			M027
source	TEXT	yes			M027
subtotal_cents	INTEGER	no	0		M027
total_cents	INTEGER	no	0		M027
line_count	INTEGER	no	0		M027
till_json	TEXT	no	'[]'		M027
notes	TEXT	yes			M027
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M027
updated_at	TEXT	no	CURRENT_TIMESTAMP		M027
Indexes:

• idx_parked_active — (is_deleted, parked_at DESC) (M027)
• idx_held_active — (is_deleted, held_at DESC) (M047)

idempotency_records

Introduced: M137. Columns: 9. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M137
idempotency_key	TEXT	no			M137
endpoint	TEXT	no			M137
request_body_sha256	TEXT	no			M137
response_status	INTEGER	no			M137
response_body	TEXT	no			M137
created_at	TEXT	no	CURRENT_TIMESTAMP		M137
expires_at	TEXT	yes			M137
actor_staff_id	INTEGER	yes		FK → staff.id	M137
Indexes:

• idx_idempotency_endpoint_created — (endpoint, created_at) (M137)
• idx_idempotency_expires — (expires_at) · WHERE expires_at IS NOT NULL (M137)

inventory

Introduced: M019. Columns: 33. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M019
variant_id	INTEGER	no		FK → product_variants.id	M019
location_id	INTEGER	no		FK → inventory_locations.id	M019
quantity_on_hand	INTEGER	no	0		M019
quantity_allocated_sale	INTEGER	no	0		M019
quantity_allocated_service	INTEGER	no	0		M019
quantity_allocated_layaway	INTEGER	no	0		M019
quantity_allocated_loaner	INTEGER	no	0		M019
quantity_allocated_rental	INTEGER	no	0		M019
quantity_allocated_online	INTEGER	no	0		M019
quantity_allocated_special_order	INTEGER	no	0		M019
quantity_available	INTEGER	no	0		M019
cost_cents	INTEGER	yes			M019
freight_cents	INTEGER	yes			M019
weighted_avg_cost_cents	INTEGER	yes			M019
retail_price_cents	INTEGER	yes			M019
selling_price_cents	INTEGER	yes			M019
minimum_price_cents	INTEGER	yes			M019
online_sale_price_cents	INTEGER	yes			M019
reorder_point	INTEGER	yes			M019
reorder_max	INTEGER	yes			M019
hold_back_quantity	INTEGER	yes			M019
tax_category_id	INTEGER	yes		FK → tax_categories.id	M019
is_consignment	INTEGER	no	0	CHECK (is_consignment IN (0,1))	M019
consignor_customer_id	INTEGER	yes		FK → customers.id	M019
status	TEXT	no	'active'	CHECK (status IN ('active','inactive'))	M019
last_physical_count_at	TEXT	yes			M019
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M019
aim_id	INTEGER	yes			M019
version	INTEGER	no	1		M019
created_at	TEXT	no	CURRENT_TIMESTAMP		M019
updated_at	TEXT	no	CURRENT_TIMESTAMP		M019
rental_rate_cents	INTEGER	yes			M065

Unique (table-level):

• (variant_id, location_id) (M019)

Indexes:

• idx_inventory_variant — (variant_id) (M019)
• idx_inventory_reorder — (reorder_point) · WHERE reorder_point IS NOT NULL (M019)
• idx_inventory_one_live_row_per_variant_location — UNIQUE · (variant_id, location_id) · WHERE is_deleted = 0 (M156)

inventory_images

Introduced: M091. Columns: 6. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M091
mime	TEXT	no			M091
data	TEXT	no			M091
bytes	INTEGER	no	0		M091
uploaded_by_staff_id	INTEGER	yes		FK → staff.id	M091
created_at	TEXT	no	(datetime('now')		M091

inventory_locations

Introduced: M019. Columns: 12. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M019
name	TEXT	no			M019
address_line_1	TEXT	yes			M019
city	TEXT	yes			M019
province	TEXT	yes			M019
postal_code	TEXT	yes			M019
is_default	INTEGER	no	0	CHECK (is_default IN (0,1))	M019
is_sales_floor	INTEGER	no	1	CHECK (is_sales_floor IN (0,1))	M019
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M019
aim_id	INTEGER	yes			M019
created_at	TEXT	no	CURRENT_TIMESTAMP		M019
updated_at	TEXT	no	CURRENT_TIMESTAMP		M019

inventory_movements

Introduced: M019. Columns: 14. Indexes: 4. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M019
inventory_id	INTEGER	no		FK → inventory.id	M019
variant_id	INTEGER	no		FK → product_variants.id	M019
location_id	INTEGER	no		FK → inventory_locations.id	M019
movement_type	TEXT	no		CHECK (movement_type IN ('receive','sale','…	M019
quantity_change	INTEGER	no			M019
new_quantity_on_hand	INTEGER	no			M019
reason	TEXT	yes			M019
reference_table	TEXT	yes			M019
reference_id	INTEGER	yes			M019
unit_cost_cents	INTEGER	yes			M019
staff_id	INTEGER	yes		FK → staff.id	M019
occurred_at	TEXT	no	CURRENT_TIMESTAMP		M019
created_at	TEXT	no	CURRENT_TIMESTAMP		M019
Indexes:

• idx_inv_movements_variant — (variant_id, occurred_at DESC) (M019)
• idx_inv_movements_inventory — (inventory_id, occurred_at DESC) (M019)
• idx_inv_movements_type — (movement_type, occurred_at DESC) (M019)
• idx_inv_movements_reference — (reference_table, reference_id) (M019)

inventory_order_queue

Introduced: M020. Columns: 14. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M020
variant_id	INTEGER	yes		FK → product_variants.id	M020
supplier_id	INTEGER	yes		FK → suppliers.id	M020
supplier_name_free	TEXT	yes			M020
quantity	INTEGER	no	1		M020
status	TEXT	no	'pending'	CHECK (status IN ('pending','ordered','rece…	M020
added_by_staff_id	INTEGER	yes		FK → staff.id	M020
customer_id	INTEGER	yes		FK → customers.id	M020
source_po_line_id	INTEGER	yes		FK → purchase_order_lines.id	M020
notes	TEXT	yes			M020
created_at	TEXT	no	CURRENT_TIMESTAMP		M020
updated_at	TEXT	no	CURRENT_TIMESTAMP		M020
unit_cost_cents	INTEGER	yes			M026
special_order_transaction_id	INTEGER	yes		FK → transactions.id	M033
Indexes:

• idx_order_queue_status — (status) (M020)
• idx_order_queue_supplier — (supplier_id, status) (M020)
• idx_order_queue_special_order — (special_order_transaction_id) · WHERE special_order_transaction_id IS NOT NULL (M033)

inventory_units

Introduced: M054. Columns: 30. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M054
inventory_id	INTEGER	no		FK → inventory.id	M054
variant_id	INTEGER	no		FK → product_variants.id	M054
location_id	INTEGER	no		FK → inventory_locations.id	M054
serial_number	TEXT	yes			M054
status	TEXT	no	'in_stock'	CHECK (status IN ('on_order','in_stock','so…	M054
cost_cents	INTEGER	yes			M054
received_at	TEXT	yes			M054
acquired_from_customer_id	INTEGER	yes		FK → customers.id	M054
notes	TEXT	yes			M054
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M054
aim_id	INTEGER	yes			M054
version	INTEGER	no	1		M054
created_at	TEXT	no	CURRENT_TIMESTAMP		M054
updated_at	TEXT	no	CURRENT_TIMESTAMP		M054
is_rental	INTEGER	no	0	CHECK (is_rental IN (0,1))	M067
rental_category	TEXT	yes			M067
rental_rate_cents	INTEGER	yes			M067
is_consignment	INTEGER	no	0	CHECK (is_consignment IN (0,1))	M068
is_trade_in	INTEGER	no	0	CHECK (is_trade_in IN (0,1))	M068
is_used	INTEGER	no	0	CHECK (is_used IN (0,1))	M068
rental_rate_hourly_cents	INTEGER	yes			M074
rental_rate_halfday_cents	INTEGER	yes			M074
rental_rate_weekly_cents	INTEGER	yes			M074
rental_damage_hold_cents	INTEGER	yes			M077
image_id	INTEGER	yes		FK → inventory_images.id	M091
special_price_cents	INTEGER	yes			M092
rental_description	TEXT	yes			M123
rental_fit_min_cm	INTEGER	yes			M123
rental_fit_max_cm	INTEGER	yes			M123
Indexes:

• idx_inventory_units_inventory — (inventory_id, is_deleted) (M054)
• idx_inventory_units_variant — (variant_id, is_deleted) (M054)
• idx_inventory_units_serial — UNIQUE · (serial_number) · WHERE serial_number IS NOT NULL AND is_deleted = 0 (M054)

kit_components

Introduced: M061. Columns: 5. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M061
kit_variant_id	INTEGER	no			M061
component_variant_id	INTEGER	no			M061
quantity	INTEGER	no	1		M061
created_at	TEXT	no	CURRENT_TIMESTAMP		M061

Unique (table-level):

• (kit_variant_id, component_variant_id) (M061)

Indexes:

• idx_kit_components_kit — (kit_variant_id) (M061)

manufacturers

Introduced: M018. Columns: 9. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
name	TEXT	no			M018
display_name	TEXT	yes			M018
website_url	TEXT	yes			M018
b2b_portal_url	TEXT	yes			M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
aim_id	INTEGER	yes			M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018
updated_at	TEXT	no	CURRENT_TIMESTAMP		M018
Indexes:

• idx_manufacturers_aim_u — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M130)

migration_jobs

Introduced: M049. Columns: 17. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M049
status	TEXT	no	'draft'	CHECK (status IN ('draft','gating','confirm…	M049
phase	TEXT	yes			M049
step	INTEGER	no	0		M049
wipe_tables	TEXT	no	'[]'		M049
keep_tables	TEXT	no	'[]'		M049
load_entities	TEXT	no	'[]'		M049
bookmark	TEXT	yes			M049
gate_result	TEXT	yes			M049
parity_result	TEXT	yes			M049
snapshot_meta	TEXT	yes			M049
error	TEXT	yes			M049
started_by_staff_id	INTEGER	yes		FK → staff.id	M049
started_at	TEXT	no	CURRENT_TIMESTAMP		M049
finished_at	TEXT	yes			M049
mode	TEXT	no	'rebuild'		M131
amend_before	TEXT	yes			M131
Indexes:

• idx_migjob_status — (status, id DESC) (M049)

migration_presets

Introduced: M049. Columns: 9. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M049
name	TEXT	no			M049
description	TEXT	yes			M049
wipe_tables	TEXT	no	'[]'		M049
keep_tables	TEXT	no	'[]'		M049
load_entities	TEXT	no	'[]'		M049
is_builtin	INTEGER	no	0	CHECK (is_builtin IN (0,1))	M049
created_at	TEXT	no	CURRENT_TIMESTAMP		M049
updated_at	TEXT	no	CURRENT_TIMESTAMP		M049

note_images

Introduced: M129. Columns: 6. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M129
mime	TEXT	no			M129
data	TEXT	no			M129
bytes	INTEGER	yes			M129
uploaded_by_staff_id	INTEGER	yes		FK → staff.id	M129
created_at	TEXT	no	CURRENT_TIMESTAMP		M129

payments

Introduced: M027. Columns: 15. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M027
transaction_id	INTEGER	no		FK → transactions.id	M027
payment_method	TEXT	no			M027
amount_cents	INTEGER	no			M027
card_last_four	TEXT	yes			M027
card_brand	TEXT	yes			M027
reference_number	TEXT	yes			M027
rounding_cents	INTEGER	no	0		M027
is_refund	INTEGER	no	0	CHECK (is_refund IN (0,1))	M027
refund_of_payment_id	INTEGER	yes		FK → payments.id	M027
status	TEXT	no	'completed'		M027
occurred_at	TEXT	no	CURRENT_TIMESTAMP		M027
created_at	TEXT	no	CURRENT_TIMESTAMP		M027
staff_id	INTEGER	yes		FK → staff.id	M036
aim_id	INTEGER	yes			M038
Indexes:

• idx_payments_txn — (transaction_id) (M027)
• idx_payments_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)

permissions

Introduced: M008. Columns: 5. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M008
name	TEXT	no			M008
display_name	TEXT	no			M008
description	TEXT	yes			M008
category	TEXT	no			M008

product_barcodes

Introduced: M018. Columns: 8. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
variant_id	INTEGER	no		FK → product_variants.id	M018
barcode	TEXT	no			M018
barcode_type	TEXT	no	'unknown'	CHECK (barcode_type IN ('upc','ean','code39…	M018
is_primary	INTEGER	no	0	CHECK (is_primary IN (0,1))	M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
aim_id	INTEGER	yes			M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018
Indexes:

• idx_barcodes_variant — (variant_id) (M018)
• idx_barcodes_barcode — (barcode) (M018)
• idx_product_barcodes_variant_code_u — UNIQUE · (variant_id, barcode) · WHERE barcode IS NOT NULL (M135)

product_variants

Introduced: M018. Columns: 16. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
product_id	INTEGER	no		FK → products.id	M018
sku	TEXT	no			M018
manufacturer_part_number	TEXT	yes			M018
display_label	TEXT	yes			M018
is_discontinued	INTEGER	no	0	CHECK (is_discontinued IN (0,1))	M018
discontinued_at	TEXT	yes			M018
replacement_variant_id	INTEGER	yes		FK → product_variants.id	M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M018
aim_id	INTEGER	yes			M018
version	INTEGER	no	1		M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018
updated_at	TEXT	no	CURRENT_TIMESTAMP		M018
is_bulk	INTEGER	no	0	CHECK (is_bulk IN (0,1))	M039
image_id	INTEGER	yes		FK → inventory_images.id	M091
Indexes:

• idx_variants_product — (product_id) (M018)
• idx_variants_discontinued — (is_discontinued, is_active) (M018)
• idx_variants_bulk — (is_bulk) · WHERE is_bulk = 1 (M039)

products

Introduced: M018. Columns: 30. Indexes: 4. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
category_id	INTEGER	yes		FK → categories.id	M018
subcategory_id	INTEGER	yes		FK → subcategories.id	M018
manufacturer_id	INTEGER	yes		FK → manufacturers.id	M018
display_name	TEXT	no			M018
model_number	TEXT	yes			M018
product_type	TEXT	no	'goods'	CHECK (product_type IN ('goods','service','…	M018
unit_label	TEXT	no	'each'		M018
weight_grams	INTEGER	yes			M018
description	TEXT	yes			M018
short_description	TEXT	yes			M018
internal_notes	TEXT	yes			M018
warranty_months	INTEGER	yes			M018
default_tax_category_id	INTEGER	yes		FK → tax_categories.id	M018
stock_behavior	TEXT	no	'tracked'	CHECK (stock_behavior IN ('tracked','untrac…	M018
is_serialized	INTEGER	no	0	CHECK (is_serialized IN (0,1))	M018
is_consignment	INTEGER	no	0	CHECK (is_consignment IN (0,1))	M018
is_loyalty_eligible	INTEGER	no	1	CHECK (is_loyalty_eligible IN (0,1))	M018
is_online_visible	INTEGER	no	0	CHECK (is_online_visible IN (0,1))	M018
online_description	TEXT	yes			M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M018
aim_id	INTEGER	yes			M018
version	INTEGER	no	1		M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018
updated_at	TEXT	no	CURRENT_TIMESTAMP		M018
is_trade_in	INTEGER	no	0	CHECK (is_trade_in IN (0,1))	M063
is_rental	INTEGER	no	0	CHECK (is_rental IN (0,1))	M063
is_used	INTEGER	no	0	CHECK (is_used IN (0,1))	M063
rental_category	TEXT	yes			M066
Indexes:

• idx_products_category — (category_id) (M018)
• idx_products_manufacturer — (manufacturer_id) (M018)
• idx_products_active — (is_active, is_deleted) (M018)
• idx_products_aim_u — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M130)

purchase_order_lines

Introduced: M021. Columns: 22. Indexes: 5. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M021
purchase_order_id	INTEGER	no		FK → purchase_orders.id	M021
line_number	INTEGER	no	1		M021
variant_id	INTEGER	yes		FK → product_variants.id	M021
supplier_sku	TEXT	yes			M021
description	TEXT	yes			M021
quantity_ordered	INTEGER	no			M021
quantity_received	INTEGER	no	0		M021
quantity_invoiced	INTEGER	no	0		M021
unit_cost_cents	INTEGER	no	0		M021
line_discount_cents	INTEGER	no	0		M021
line_total_cents	INTEGER	no	0		M021
expected_at	TEXT	yes			M021
notes	TEXT	yes			M021
is_special_order	INTEGER	no	0	CHECK (is_special_order IN (0,1))	M021
aim_id	INTEGER	yes			M021
version	INTEGER	no	1		M021
created_at	TEXT	no	CURRENT_TIMESTAMP		M021
updated_at	TEXT	no	CURRENT_TIMESTAMP		M021
special_order_transaction_id	INTEGER	yes		FK → transactions.id	M033
customer_id	INTEGER	yes		FK → customers.id	M037
earmarked_qty	INTEGER	no	0		M037
Indexes:

• idx_po_lines_po — (purchase_order_id) (M021)
• idx_po_lines_variant — (variant_id) (M021)
• idx_po_lines_special_order — (special_order_transaction_id) · WHERE special_order_transaction_id IS NOT NULL (M033)
• idx_po_lines_customer — (customer_id) · WHERE customer_id IS NOT NULL (M037)
• idx_po_lines_aim_u — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M130)

purchase_orders

Introduced: M025. Columns: 24. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M025
po_number	TEXT	no			M025
supplier_id	INTEGER	no		FK → suppliers.id	M025
status	TEXT	no	'pending_order'	CHECK (status IN ('pending_order','ordered'…	M025
ordered_by_staff_id	INTEGER	yes		FK → staff.id	M025
ordered_at	TEXT	yes			M025
expected_at	TEXT	yes			M025
shipped_at	TEXT	yes			M025
received_at	TEXT	yes			M025
ship_method	TEXT	yes			M025
tracking_number	TEXT	yes			M025
shipping_cost_cents	INTEGER	yes			M025
tax_cents	INTEGER	yes			M025
subtotal_cents	INTEGER	yes			M025
total_cents	INTEGER	yes			M025
customer_id	INTEGER	yes		FK → customers.id	M025
notes	TEXT	yes			M025
is_on_hold	INTEGER	no	0	CHECK (is_on_hold IN (0,1))	M025
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M025
aim_id	INTEGER	yes			M025
external_reference	TEXT	yes			M025
version	INTEGER	no	1		M025
created_at	TEXT	no	CURRENT_TIMESTAMP		M025
updated_at	TEXT	no	CURRENT_TIMESTAMP		M025
Indexes:

• idx_po_supplier — (supplier_id) (M025)
• idx_po_status — (status, ordered_at DESC) (M025)

recovery_codes

Introduced: M158. Columns: 7. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M158
code_hash	TEXT	no			M158
batch	TEXT	no			M158
created_at	TEXT	no	(datetime('now')		M158
created_by_staff_id	INTEGER	yes			M158
used_at	TEXT	yes			M158
used_ip	TEXT	yes			M158

rental_addons

Introduced: M102. Columns: 8. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M102
name	TEXT	no			M102
sort_order	INTEGER	no	0		M102
default_given	INTEGER	no	0	CHECK (default_given IN (0,1))	M102
requires_refusal_initials	INTEGER	no	0	CHECK (requires_refusal_initials IN (0,1))	M102
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M102
created_at	TEXT	no	(datetime('now')		M102
updated_at	TEXT	no	(datetime('now')		M102
Indexes:

• idx_rental_addons_live — (is_deleted, sort_order) (M102)

rental_booking_bikes

Introduced: M070. Columns: 13. Indexes: 4. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M070
booking_id	INTEGER	no			M070
unit_id	INTEGER	no			M070
rate_cents	INTEGER	no	0		M070
out_at	TEXT	yes			M071
returned_at	TEXT	yes			M071
line_status	TEXT	no	'active'		M082
added_at	TEXT	yes			M082
removed_at	TEXT	yes			M082
replaces_bike_id	INTEGER	yes		FK → rental_booking_bikes.id	M082
damage_captured_cents	INTEGER	no	0		M082
billed_units	REAL	no	1		M082
line_cents	INTEGER	no	0		M082

Foreign keys (table-level):

• booking_id → rental_bookings(id) (M070)
• unit_id → inventory_units(id) (M070)

Indexes:

• idx_rental_booking_bikes_unit — (unit_id) (M070)
• idx_rental_booking_bikes_booking — (booking_id) (M070)
• ix_rbb_active_unit — UNIQUE · (booking_id, unit_id) · WHERE line_status <> 'swapped_out' (M087)
• ix_rbb_active_unit — UNIQUE · (booking_id, unit_id) · WHERE line_status NOT IN ('swapped_out','sold') (M088)

rental_booking_riders

Introduced: M070. Columns: 5. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M070
booking_id	INTEGER	no			M070
name	TEXT	no			M070
customer_id	INTEGER	yes		FK → customers.id	M082
is_minor	INTEGER	no	0		M082

Foreign keys (table-level):

• booking_id → rental_bookings(id) (M070)

Indexes:

• idx_rental_booking_riders_booking — (booking_id) (M070)

rental_bookings

Introduced: M079. Columns: 40. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M079
location_id	INTEGER	no	1		M079
customer_id	INTEGER	no			M079
from_date	TEXT	no			M079
to_date	TEXT	no			M079
status	TEXT	no	'booked'	CHECK (status IN ('booked','out','returned'…	M079
subtotal_cents	INTEGER	no	0		M079
tax_cents	INTEGER	no	0		M079
total_cents	INTEGER	no	0		M079
notes	TEXT	yes			M079
is_deleted	INTEGER	no	0		M079
version	INTEGER	no	1		M079
created_at	TEXT	no	CURRENT_TIMESTAMP		M079
updated_at	TEXT	no	CURRENT_TIMESTAMP		M079
out_at	TEXT	yes			M079
returned_at	TEXT	yes			M079
deposit_transaction_id	INTEGER	yes		FK → transactions.id	M079
charge_transaction_id	INTEGER	yes		FK → transactions.id	M079
rate_basis	TEXT	no	'daily'		M079
start_time	TEXT	yes			M079
end_time	TEXT	yes			M079
halfday_window	TEXT	yes			M079
damage_hold_cents	INTEGER	yes			M079
damage_hold_status	TEXT	yes			M079
damage_captured_cents	INTEGER	no	0		M079
no_show_at	TEXT	yes			M079
no_show_reason	TEXT	yes			M079
cancelled_at	TEXT	yes			M079
cancelled_reason	TEXT	yes			M079
lost_at	TEXT	yes			M079
lost_reason	TEXT	yes			M079
extended_at	TEXT	yes			M079
pickup_at	TEXT	yes			M079
pickup_state	TEXT	yes			M079
reminder_sent_at	TEXT	yes			M079
source	TEXT	no	'counter'		M079
damage_hold_auth_ref	TEXT	yes			M079
converted_sale_transaction_id	INTEGER	yes		FK → transactions.id	M079
damage_capture_transaction_id	INTEGER	yes		FK → transactions.id	M079
damage_hold_placed_at	TEXT	yes			M098

Foreign keys (table-level):

• customer_id → customers(id) (M079)

Indexes:

• idx_rental_bookings_dates — (from_date, to_date) (M079)

rental_drafts

Introduced: M099. Columns: 3. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
staff_id	INTEGER	yes		PK	M099
payload	TEXT	no			M099
updated_at	TEXT	no	(datetime('now')		M099

rental_hold_policy

Introduced: M097. Columns: 3. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
rental_category	TEXT	yes		PK	M097
hold_cents	INTEGER	no	0		M097
updated_at	TEXT	no	(datetime('now')		M097

rental_id_images

Introduced: M096. Columns: 6. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M096
mime	TEXT	no			M096
data	TEXT	no			M096
bytes	INTEGER	no	0		M096
uploaded_by_staff_id	INTEGER	yes		FK → staff.id	M096
created_at	TEXT	no	(datetime('now')		M096

rental_rate_cards

Introduced: M119. Columns: 14. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M119
kind	TEXT	no		CHECK (kind IN ('rental','demo'))	M119
category_key	TEXT	no			M119
label	TEXT	no			M119
session_minutes	INTEGER	yes			M119
hourly_cents	INTEGER	yes			M119
halfday_cents	INTEGER	yes			M119
daily_cents	INTEGER	yes			M119
weekly_cents	INTEGER	yes			M119
damage_hold_cents	INTEGER	yes			M119
sort_order	INTEGER	no	0		M119
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M119
created_at	TEXT	no	CURRENT_TIMESTAMP		M119
updated_at	TEXT	no	CURRENT_TIMESTAMP		M119
Indexes:

• idx_rate_card_kind_cat — UNIQUE · (kind, category_key) (M119)

rental_waivers

Introduced: M093. Columns: 24. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M093
booking_id	INTEGER	no		FK → rental_bookings.id	M093
customer_id	INTEGER	yes		FK → customers.id	M093
signer_name	TEXT	no			M093
is_main_contact	INTEGER	no	0	CHECK (is_main_contact IN (0,1))	M093
is_minor	INTEGER	no	0	CHECK (is_minor IN (0,1))	M093
guardian_name	TEXT	yes			M093
signature_png	TEXT	no			M093
waiver_text	TEXT	no			M093
signed_at	TEXT	no	(datetime('now')		M093
witnessed_by_staff_id	INTEGER	yes		FK → staff.id	M093
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M093
public_token	TEXT	yes			M094
id_type	TEXT	yes			M095
id_number	TEXT	yes			M095
id_dob	TEXT	yes			M095
id_expiry	TEXT	yes			M095
id_scanned_at	TEXT	yes			M095
id_image_id	INTEGER	yes		FK → rental_id_images.id	M096
item_helmet	INTEGER	no	1	CHECK (item_helmet IN (0,1))	M101
item_water_bottle	INTEGER	no	0	CHECK (item_water_bottle IN (0,1))	M101
item_panniers	INTEGER	no	0	CHECK (item_panniers IN (0,1))	M101
helmet_refused_initials	TEXT	yes			M101
items_json	TEXT	yes			M102
Indexes:

• idx_rental_waivers_booking — (booking_id) (M093)
• idx_rental_waivers_customer — (customer_id) (M093)
• idx_rental_waivers_token — UNIQUE · (public_token) (M094)

request_limits

Introduced: M157. Columns: 4. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M157
action	TEXT	no			M157
client_key	TEXT	no			M157
occurred_at	TEXT	no	(datetime('now')		M157
Indexes:

• idx_request_limits_key — (action, client_key, occurred_at) (M157)
• idx_request_limits_time — (action, occurred_at) (M157)

rma

Introduced: M024. Columns: 13. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M024
rma_number	TEXT	no			M024
supplier_id	INTEGER	no		FK → suppliers.id	M024
status	TEXT	no	'open'		M024
reason	TEXT	yes			M024
resolution	TEXT	yes			M024
resolution_notes	TEXT	yes			M024
created_by_staff_id	INTEGER	yes			M024
shipped_at	TEXT	yes			M024
resolved_at	TEXT	yes			M024
is_deleted	INTEGER	no	0		M024
created_at	TEXT	no	(datetime('now')		M024
updated_at	TEXT	no	(datetime('now')		M024
Indexes:

• idx_rma_supplier — (supplier_id, status, is_deleted) (M024)
• idx_rma_status — (status, is_deleted) (M024)

rma_lines

Introduced: M024. Columns: 7. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M024
rma_id	INTEGER	no		FK → rma.id	M024
variant_id	INTEGER	no		FK → product_variants.id	M024
quantity	INTEGER	no			M024
reason	TEXT	yes			M024
unit_cost_cents	INTEGER	yes			M024
created_at	TEXT	no	(datetime('now')		M024
Indexes:

• idx_rma_lines — (rma_id) (M024)

role_permissions

Introduced: M008. Columns: 2. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
role_id	INTEGER	no			M008
permission_id	INTEGER	no			M008

Foreign keys (table-level):

• role_id → roles(id) (M008)
• permission_id → permissions(id) (M008)

roles

Introduced: M008. Columns: 8. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M008
name	TEXT	no			M008
display_name	TEXT	no			M008
description	TEXT	yes			M008
is_system	INTEGER	no	0		M008
created_at	TEXT	no	CURRENT_TIMESTAMP		M008
updated_at	TEXT	no	CURRENT_TIMESTAMP		M008
idle_lockout_seconds	INTEGER	yes		CHECK (idle_lockout_seconds IS NULL OR idle…	M008

search_synonym_groups

Introduced: M010. Columns: 7. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M010
domain	TEXT	no			M010
description	TEXT	yes			M010
is_active	INTEGER	no	1		M010
created_at	TEXT	no	CURRENT_TIMESTAMP		M010
updated_at	TEXT	no	CURRENT_TIMESTAMP		M010
created_by_staff_id	INTEGER	yes			M010

Foreign keys (table-level):

• created_by_staff_id → staff(id) (M010)

Indexes:

• idx_search_synonym_groups_domain — (domain) · WHERE is_active = 1 (M010)

search_synonym_terms

Introduced: M010. Columns: 5. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M010
group_id	INTEGER	no			M010
term	TEXT	no			M010
is_active	INTEGER	no	1		M010
created_at	TEXT	no	CURRENT_TIMESTAMP		M010

Foreign keys (table-level):

• group_id → search_synonym_groups(id) (M010)

Unique (table-level):

• (group_id, term) (M010)

Indexes:

• idx_search_synonym_terms_group — (group_id) · WHERE is_active = 1 (M010)
• idx_search_synonym_terms_term — (term) · WHERE is_active = 1 (M010)

service_bike_locations

Introduced: M104. Columns: 6. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M104
name	TEXT	no			M104
sort_order	INTEGER	no	0		M104
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M104
created_at	TEXT	no	(datetime('now')		M104
updated_at	TEXT	no	(datetime('now')		M104
Indexes:

• idx_service_bike_locations_live — (is_deleted, sort_order) (M104)

service_menu

Introduced: M030. Columns: 14. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M030
code	TEXT	yes			M030
name	TEXT	no			M030
description	TEXT	yes			M030
category	TEXT	yes			M030
price_cents	INTEGER	no	0	CHECK (price_cents >= 0)	M030
est_minutes	INTEGER	yes			M030
is_ebike	INTEGER	no	0	CHECK (is_ebike IN (0,1))	M030
tax_category_id	INTEGER	yes		FK → tax_categories.id	M030
sort_order	INTEGER	no	0		M030
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M030
aim_id	INTEGER	yes			M030
created_at	TEXT	no	CURRENT_TIMESTAMP		M030
updated_at	TEXT	no	CURRENT_TIMESTAMP		M030
Indexes:

• idx_service_menu_active — (is_active, sort_order) (M030)
• idx_service_menu_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M141)

service_menu_parts

Introduced: M145. Columns: 5. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M145
service_menu_id	INTEGER	no		FK → service_menu.id	M145
variant_id	INTEGER	no		FK → product_variants.id	M145
quantity	INTEGER	no	1	CHECK (quantity > 0)	M145
created_at	TEXT	no	CURRENT_TIMESTAMP		M145
Indexes:

• uq_service_menu_parts — UNIQUE · (service_menu_id, variant_id) (M145)
• idx_service_menu_parts_service — (service_menu_id) (M145)

service_signoffs

Introduced: M112. Columns: 12. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M112
ticket_id	INTEGER	no		FK → service_tickets.id	M112
kind	TEXT	no		CHECK (kind IN ('authorisation','pickup'))	M112
customer_id	INTEGER	yes		FK → customers.id	M112
signer_name	TEXT	no			M112
signature_png	TEXT	no			M112
terms_text	TEXT	no			M112
budget_cents_at_signing	INTEGER	yes			M112
total_cents_at_signing	INTEGER	yes			M112
signed_at	TEXT	no	(datetime('now')		M112
witnessed_by_staff_id	INTEGER	yes		FK → staff.id	M112
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M112
Indexes:

• idx_service_signoffs_ticket — (ticket_id, kind, is_deleted) (M112)

service_ticket_lines

Introduced: M030. Columns: 25. Indexes: 4. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M030
ticket_id	INTEGER	no		FK → service_tickets.id	M030
line_number	INTEGER	no	1		M030
line_type	TEXT	no	'labour'	CHECK (line_type IN ('labour','part'))	M030
service_menu_id	INTEGER	yes		FK → service_menu.id	M030
variant_id	INTEGER	yes		FK → product_variants.id	M030
description	TEXT	no			M030
sku	TEXT	yes			M030
quantity	INTEGER	no	1		M030
unit_price_cents	INTEGER	no	0		M030
original_unit_price_cents	INTEGER	yes			M030
labor_minutes	INTEGER	yes			M030
discount_cents	INTEGER	no	0		M030
line_total_cents	INTEGER	no	0		M030
tax_category_id	INTEGER	yes		FK → tax_categories.id	M030
is_completed	INTEGER	no	0	CHECK (is_completed IN (0,1))	M030
is_recommended	INTEGER	no	0	CHECK (is_recommended IN (0,1))	M030
assigned_mechanic_id	INTEGER	yes		FK → staff.id	M030
applied_discount_id	INTEGER	yes		FK → discounts.id	M030
created_at	TEXT	no	CURRENT_TIMESTAMP		M030
aim_id	INTEGER	yes			M038
parent_line_id	INTEGER	yes		FK → service_ticket_lines.id	M110
discount_reason	TEXT	yes			M151
special_order	INTEGER	no	0		M152
so_touched	INTEGER	no	0		M152
Indexes:

• idx_stl_ticket — (ticket_id) (M030)
• idx_stl_variant — (variant_id) (M030)
• idx_svc_lines_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)
• idx_stl_parent — (parent_line_id) (M110)

service_ticket_messages

Introduced: M030. Columns: 8. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M030
ticket_id	INTEGER	no		FK → service_tickets.id	M030
direction	TEXT	no	'outbound'	CHECK (direction IN ('outbound','inbound'))	M030
channel	TEXT	no	'sms'	CHECK (channel IN ('sms','email','phone_cal…	M030
message_text	TEXT	no			M030
sent_by_staff_id	INTEGER	yes		FK → staff.id	M030
delivery_status	TEXT	no	'recorded'		M030
created_at	TEXT	no	CURRENT_TIMESTAMP		M030
Indexes:

• idx_stm_ticket — (ticket_id, created_at) (M030)

service_ticket_status_history

Introduced: M030. Columns: 8. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M030
ticket_id	INTEGER	no		FK → service_tickets.id	M030
from_status	TEXT	yes			M030
to_status	TEXT	no			M030
staff_id	INTEGER	yes		FK → staff.id	M030
reason	TEXT	yes			M030
created_at	TEXT	no	CURRENT_TIMESTAMP		M030
aim_id	INTEGER	yes			M038
Indexes:

• idx_sth_ticket — (ticket_id, created_at) (M030)
• idx_svc_stathist_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)

service_tickets

Introduced: M030. Columns: 50. Indexes: 5. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M030
ticket_number	TEXT	no			M030
customer_id	INTEGER	yes		FK → customers.id	M030
bill_to_customer_id	INTEGER	yes		FK → customers.id	M030
customer_bike_id	INTEGER	yes		FK → customer_bikes.id	M030
bike_description	TEXT	yes			M030
bike_serial	TEXT	yes			M030
bike_location	TEXT	yes			M030
status	TEXT	no	'dropped_off'		M030
urgency	TEXT	no	'normal'		M030
urgency_notes	TEXT	yes			M030
primary_mechanic_id	INTEGER	yes		FK → staff.id	M030
reported_issues	TEXT	yes			M030
mechanic_findings	TEXT	yes			M030
staff_notes	TEXT	yes			M030
dropped_off_at	TEXT	no	CURRENT_TIMESTAMP		M030
promised_at	TEXT	yes			M030
estimated_at	TEXT	yes			M030
estimate_amount_cents	INTEGER	yes			M030
estimate_approved_at	TEXT	yes			M030
work_started_at	TEXT	yes			M030
ready_at	TEXT	yes			M030
notified_at	TEXT	yes			M030
picked_up_at	TEXT	yes			M030
paid_in_full_at	TEXT	yes			M030
warranty_until	TEXT	yes			M030
cancelled_at	TEXT	yes			M030
cancelled_reason	TEXT	yes			M030
hold_reason	TEXT	yes			M030
total_labor_minutes	INTEGER	no	0		M030
labour_cents	INTEGER	no	0		M030
parts_cents	INTEGER	no	0		M030
subtotal_cents	INTEGER	no	0		M030
discount_cents	INTEGER	no	0		M030
gst_cents	INTEGER	no	0		M030
pst_cents	INTEGER	no	0		M030
total_cents	INTEGER	no	0		M030
deposit_cents	INTEGER	no	0		M030
service_discount_id	INTEGER	yes		FK → discounts.id	M030
invoice_transaction_id	INTEGER	yes		FK → transactions.id	M030
created_by_staff_id	INTEGER	yes		FK → staff.id	M030
created_at	TEXT	no	CURRENT_TIMESTAMP		M030
updated_at	TEXT	no	CURRENT_TIMESTAMP		M030
aim_id	INTEGER	yes			M038
inventory_unit_id	INTEGER	yes		FK → inventory_units.id	M085
rental_booking_id	INTEGER	yes		FK → rental_bookings.id	M085
is_internal	INTEGER	no	0		M085
parts_vendor	TEXT	yes			M109
parts_eta	TEXT	yes			M109
max_budget_cents	INTEGER	yes			M142
Indexes:

• idx_tickets_customer — (customer_id) (M030)
• idx_tickets_status — (status, dropped_off_at DESC) (M030)
• idx_tickets_bike — (customer_bike_id) (M030)
• idx_tickets_mechanic — (primary_mechanic_id) (M030)
• idx_svc_tickets_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)

service_urgency_levels

Introduced: M104. Columns: 6. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M104
name	TEXT	no			M104
sort_order	INTEGER	no	0		M104
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M104
created_at	TEXT	no	(datetime('now')		M104
updated_at	TEXT	no	(datetime('now')		M104
Indexes:

• idx_service_urgency_levels_live — (is_deleted, sort_order) (M104)

session_devices

Introduced: M160. Columns: 2. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
token	TEXT	yes		PK	M160
device_key	TEXT	no			M160
Indexes:

• idx_session_devices_key — (device_key) (M160)

sessions

Introduced: M001. Columns: 4. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
token	TEXT	yes		PK	M001
staff_id	INTEGER	no		FK → staff.id	M001
created_at	TEXT	no	(datetime('now')		M001
expires_at	TEXT	no			M001
Indexes:

• idx_sessions_staff — (staff_id) (M001)

shop_announcements

Introduced: M144. Columns: 11. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M144
posted_by_staff_id	INTEGER	no		FK → staff.id	M144
body	TEXT	no			M144
pinned	INTEGER	no	0	CHECK (pinned IN (0,1))	M144
posted_at	TEXT	no	CURRENT_TIMESTAMP		M144
expires_at	TEXT	yes			M144
edited_at	TEXT	yes			M144
retired_at	TEXT	yes			M144
retired_by_staff_id	INTEGER	yes		FK → staff.id	M144
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M144
version	INTEGER	no	1		M144

Checks (table-level):

• (length(body) BETWEEN 1 AND 100000) (M144)

Indexes:

• idx_shop_announcements_feed — (pinned DESC, posted_at DESC) · WHERE is_active = 1 (M144)

shop_config

Introduced: M035. Columns: 64. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK · CHECK (id = 1)	M035
business_name	TEXT	no			M035
legal_name	TEXT	yes			M035
address_line_1	TEXT	no			M035
address_line_2	TEXT	yes			M035
city	TEXT	no			M035
province	TEXT	no	'BC'		M035
postal_code	TEXT	no			M035
country	TEXT	no	'CA'		M035
phone_primary	TEXT	no			M035
email_primary	TEXT	no			M035
website_url	TEXT	yes			M035
gst_number	TEXT	yes			M035
pst_number	TEXT	yes			M035
fiscal_year_end_mmdd	TEXT	no	'12-31'		M035
hours_json	TEXT	no	'{}'		M035
receipt_footer_text	TEXT	yes			M035
receipt_logo_url	TEXT	yes			M035
version	INTEGER	no	1		M035
created_at	TEXT	no	(datetime('now')		M035
updated_at	TEXT	no	(datetime('now')		M035
layaway_term_days	INTEGER	no	90		M036
tab_accents_json	TEXT	yes			M042
gift_cards_enabled	INTEGER	no	1	CHECK (gift_cards_enabled IN (0, 1))	M045
account_credit_enabled	INTEGER	no	1	CHECK (account_credit_enabled IN (0, 1))	M045
pos_tender_mc_enabled	INTEGER	no	1	CHECK (pos_tender_mc_enabled IN (0,1))	M056
pos_tender_visa_enabled	INTEGER	no	1	CHECK (pos_tender_visa_enabled IN (0,1))	M056
pos_tender_debit_enabled	INTEGER	no	1	CHECK (pos_tender_debit_enabled IN (0,1))	M056
pos_tender_cash_enabled	INTEGER	no	1	CHECK (pos_tender_cash_enabled IN (0,1))	M056
pos_tender_card_manual_enabled	INTEGER	no	1	CHECK (pos_tender_card_manual_enabled IN (0…	M056
pos_tender_store_credit_enabled	INTEGER	no	1	CHECK (pos_tender_store_credit_enabled IN (…	M056
pos_tender_check_enabled	INTEGER	no	1	CHECK (pos_tender_check_enabled IN (0,1))	M056
pos_tender_etransfer_enabled	INTEGER	no	1	CHECK (pos_tender_etransfer_enabled IN (0,1))	M056
pos_tender_promo_enabled	INTEGER	no	1	CHECK (pos_tender_promo_enabled IN (0,1))	M056
pos_layaway_enabled	INTEGER	no	1	CHECK (pos_layaway_enabled IN (0,1))	M057
rental_deposit_pct	INTEGER	no	50	CHECK (rental_deposit_pct BETWEEN 0 AND 100)	M073
rental_late_fee_enabled	INTEGER	no	1	CHECK (rental_late_fee_enabled IN (0,1))	M086
rental_late_grace_minutes	INTEGER	no	30	CHECK (rental_late_grace_minutes BETWEEN 0 …	M086
rental_daycount_basis	TEXT	no	'inclusive'	CHECK (rental_daycount_basis IN ('inclusive…	M089
rental_min_days	INTEGER	no	1	CHECK (rental_min_days BETWEEN 1 AND 60)	M089
rental_min_charge_cents	INTEGER	no	0	CHECK (rental_min_charge_cents >= 0)	M089
rental_early_return_policy	TEXT	no	'no_refund'	CHECK (rental_early_return_policy IN ('no_r…	M089
rental_early_return_fee_cents	INTEGER	no	0	CHECK (rental_early_return_fee_cents >= 0)	M089
rental_early_return_cutoff	TEXT	yes			M089
rental_cancel_policy	TEXT	no	'full_refund'	CHECK (rental_cancel_policy IN ('full_refun…	M089
rental_cancel_fee_cents	INTEGER	no	0	CHECK (rental_cancel_fee_cents >= 0)	M089
rental_cancel_window_hours	INTEGER	no	0	CHECK (rental_cancel_window_hours BETWEEN 0…	M089
rental_waiver_text	TEXT	yes			M097
rental_hold_default_cents	INTEGER	no	0		M097
rental_deposit_pct_dayof	INTEGER	yes			M100
rental_deposit_pct_future	INTEGER	yes			M100
service_bench_mechanics	INTEGER	no	1		M108
service_bench_minutes_per_day	INTEGER	no	360		M108
service_parts_hold_keeps_slot	INTEGER	no	0		M108
service_authorisation_text	TEXT	yes			M112
service_pickup_text	TEXT	yes			M112
service_require_signoff	INTEGER	no	0		M112
service_default_interval_days	INTEGER	no	365		M114
loyalty_enabled	INTEGER	no	0		M116
loyalty_points_per_dollar	INTEGER	no	1		M116
loyalty_cents_per_point	INTEGER	no	1		M116
service_max_budget_enabled	INTEGER	no	1		M150
demo_mode	INTEGER	no	0		M155
require_morning_unlock	INTEGER	no	0		M158

shop_note_comments

Introduced: M148. Columns: 6. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M148
note_id	INTEGER	no			M148
author_staff_id	INTEGER	no			M148
body_html	TEXT	no	''		M148
created_at	TEXT	no	CURRENT_TIMESTAMP		M148
updated_at	TEXT	no	CURRENT_TIMESTAMP		M148
Indexes:

• idx_note_comments_note — (note_id, created_at ASC) (M148)

shop_note_reads

Introduced: M149. Columns: 3. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
note_id	INTEGER	no			M149
staff_id	INTEGER	no			M149
read_at	TEXT	no	CURRENT_TIMESTAMP		M149
Indexes:

• idx_note_reads_staff — (staff_id, note_id) (M149)

shop_notes

Introduced: M127. Columns: 11. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M127
author_staff_id	INTEGER	no			M127
body_html	TEXT	no	''		M127
created_at	TEXT	no	CURRENT_TIMESTAMP		M127
updated_at	TEXT	no	CURRENT_TIMESTAMP		M127
tab	TEXT	no	'general'		M128
status	TEXT	no	'open'		M147
resolution_html	TEXT	no	''		M147
resolved_at	TEXT	yes			M147
resolved_by_staff_id	INTEGER	yes			M147
fixed_in_version	TEXT	no	''		M147
Indexes:

• idx_shop_notes_created — (created_at DESC) (M127)
• idx_shop_notes_tab — (tab, created_at DESC) (M128)
• idx_shop_notes_tab_status — (tab, status, created_at DESC) (M147)

signin_devices

Introduced: M160. Columns: 5. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M160
token_hash	TEXT	no		CHECK (length(token_hash) = 64)	M160
code	TEXT	no		CHECK (length(code) = 4)	M160
first_ip	TEXT	yes			M160
created_at	TEXT	no	(datetime('now')		M160

signin_lockouts

Introduced: M160. Columns: 8. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M160
client_key	TEXT	no			M160
code	TEXT	yes			M160
wrong_pins	INTEGER	no		CHECK (wrong_pins >= 1)	M160
last_ip	TEXT	yes			M160
locked_at	TEXT	no	(datetime('now')		M160
cleared_at	TEXT	yes			M160
cleared_by	TEXT	yes			M160

Checks (table-level):

• ((cleared_at IS NULL) = (cleared_by IS NULL)) (M160)

Indexes:

• idx_signin_lockouts_open — UNIQUE · (client_key) · WHERE cleared_at IS NULL (M160)

staff

Introduced: M001. Columns: 18. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M001
name	TEXT	no			M001
role	TEXT	no	'staff'		M001
pin_hash	TEXT	yes			M001
pin_salt	TEXT	yes			M001
active	INTEGER	no	1		M001
created_at	TEXT	no	(datetime('now')		M001
role_id	INTEGER	yes		FK → roles.id	M008
initials	TEXT	yes			M009
email	TEXT	yes			M009
phone_mobile	TEXT	yes			M009
idle_lockout_seconds	INTEGER	yes		CHECK (idle_lockout_seconds IS NULL OR idle…	M009
override_role_id	INTEGER	yes		FK → roles.id	M016
external_aim_id	INTEGER	yes			M034
start_date	TEXT	yes			M034
username	TEXT	yes			M034
tooltip_mode	TEXT	no	'heuristic'	CHECK (tooltip_mode IN ('on', 'off', 'heuri…	M043
can_access_notes	INTEGER	no	0		M127
Indexes:

• idx_staff_external_aim — (external_aim_id) (M034)

staff_duties

Introduced: M144. Columns: 17. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M144
assigned_to_staff_id	INTEGER	yes		FK → staff.id	M144
created_by_staff_id	INTEGER	no		FK → staff.id	M144
title	TEXT	no			M144
body	TEXT	yes			M144
shift_date	TEXT	yes			M144
status	TEXT	no	'pending'	CHECK (status IN ('pending','in_progress','…	M144
completed_at	TEXT	yes			M144
completed_by_staff_id	INTEGER	yes		FK → staff.id	M144
template_id	INTEGER	yes		FK → duty_templates.id	M144
source	TEXT	no	'adhoc'	CHECK (source IN ('adhoc','template'))	M144
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M144
deleted_at	TEXT	yes			M144
deleted_by_staff_id	INTEGER	yes		FK → staff.id	M144
version	INTEGER	no	1		M144
created_at	TEXT	no	CURRENT_TIMESTAMP		M144
updated_at	TEXT	no	CURRENT_TIMESTAMP		M144

Checks (table-level):

• (length(title) BETWEEN 1 AND 200) (M144)
• (body IS NULL OR length(body) <= 4000) (M144)

Indexes:

• idx_staff_duties_date — (shift_date) · WHERE is_deleted = 0 (M144)
• idx_staff_duties_assignee — (assigned_to_staff_id, shift_date) · WHERE is_deleted = 0 (M144)
• uq_staff_duties_template_day — UNIQUE · (template_id, shift_date) · WHERE template_id IS NOT NULL (M144)

staff_feature_state

Introduced: M043. Columns: 8. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
staff_id	INTEGER	no		FK → staff.id	M043
feature_key	TEXT	no			M043
use_count	INTEGER	no	0	CHECK (use_count >= 0)	M043
first_used_at	INTEGER	yes			M043
last_used_at	INTEGER	yes			M043
tooltip_force_show	INTEGER	no	0	CHECK (tooltip_force_show IN (0, 1))	M043
created_at	INTEGER	no	(unixepoch()		M043
updated_at	INTEGER	no	(unixepoch()		M043
Indexes:

• idx_sfs_staff — (staff_id) (M043)

staff_messages

Introduced: M144. Columns: 7. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M144
from_staff_id	INTEGER	no		FK → staff.id	M144
to_staff_id	INTEGER	no		FK → staff.id	M144
body	TEXT	no			M144
created_at	TEXT	no	CURRENT_TIMESTAMP		M144
read_at	TEXT	yes			M144
is_deleted	INTEGER	no	0	CHECK (is_deleted IN (0,1))	M144

Checks (table-level):

• (from_staff_id != to_staff_id) (M144)
• (length(body) BETWEEN 1 AND 4000) (M144)

Indexes:

• idx_staff_messages_to — (to_staff_id, created_at DESC) · WHERE is_deleted = 0 (M144)
• idx_staff_messages_from — (from_staff_id, created_at DESC) · WHERE is_deleted = 0 (M144)

staff_permission_overrides

Introduced: M008. Columns: 8. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M008
staff_id	INTEGER	no			M008
permission_id	INTEGER	no			M008
allow	INTEGER	no			M008
reason	TEXT	yes			M008
granted_by_staff_id	INTEGER	yes			M008
granted_at	TEXT	no	CURRENT_TIMESTAMP		M008
expires_at	TEXT	yes			M008

Foreign keys (table-level):

• staff_id → staff(id) (M008)
• permission_id → permissions(id) (M008)
• granted_by_staff_id → staff(id) (M008)

Unique (table-level):

• (staff_id, permission_id) (M008)

staff_screen_permissions

Introduced: M008. Columns: 3. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
staff_id	INTEGER	no			M008
permission_id	INTEGER	no			M008
granted_at	TEXT	no	CURRENT_TIMESTAMP		M008

Foreign keys (table-level):

• staff_id → staff(id) (M008)
• permission_id → permissions(id) (M008)

subcategories

Introduced: M018. Columns: 12. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
category_id	INTEGER	no		FK → categories.id	M018
code	TEXT	yes			M018
name	TEXT	no			M018
description	TEXT	yes			M018
display_order	INTEGER	no	0		M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
aim_id	INTEGER	yes			M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018
updated_at	TEXT	no	CURRENT_TIMESTAMP		M018
default_tax_category_id	INTEGER	yes		FK → tax_categories.id	M055
parent_subcategory_id	INTEGER	yes		FK → subcategories.id	M153
Indexes:

• idx_subcategories_category — (category_id) (M018)
• idx_subcategories_cat_code_u — UNIQUE · (category_id, code) · WHERE code IS NOT NULL (M130)
• idx_subcategories_parent — (parent_subcategory_id) (M153)

supplier_products

Introduced: M020. Columns: 12. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M020
supplier_id	INTEGER	no		FK → suppliers.id	M020
variant_id	INTEGER	no		FK → product_variants.id	M020
supplier_sku	TEXT	yes			M020
cost_cents	INTEGER	yes			M020
next_cost_cents	INTEGER	yes			M020
is_primary_supplier	INTEGER	no	0	CHECK (is_primary_supplier IN (0,1))	M020
last_received_at	TEXT	yes			M020
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M020
aim_id	INTEGER	yes			M020
created_at	TEXT	no	CURRENT_TIMESTAMP		M020
updated_at	TEXT	no	CURRENT_TIMESTAMP		M020

Unique (table-level):

• (supplier_id, variant_id) (M020)

Indexes:

• idx_supplier_products_variant — (variant_id) (M020)
• idx_supplier_products_supplier — (supplier_id) (M020)
• idx_supplier_products_primary — (variant_id, is_primary_supplier) (M020)

suppliers

Introduced: M020. Columns: 22. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M020
name	TEXT	no			M020
display_name	TEXT	yes			M020
account_number	TEXT	yes			M020
contact_name	TEXT	yes			M020
contact_phone	TEXT	yes			M020
contact_email	TEXT	yes			M020
contact_address	TEXT	yes			M020
website_url	TEXT	yes			M020
b2b_portal_url	TEXT	yes			M020
payment_terms	TEXT	yes			M020
default_lead_time_days	INTEGER	yes			M020
is_drop_ship_capable	INTEGER	no	0	CHECK (is_drop_ship_capable IN (0,1))	M020
display_color_hex	TEXT	yes			M020
logo_url	TEXT	yes			M020
notes	TEXT	yes			M020
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M020
aim_id	INTEGER	yes			M020
version	INTEGER	no	1		M020
created_at	TEXT	no	CURRENT_TIMESTAMP		M020
updated_at	TEXT	no	CURRENT_TIMESTAMP		M020
name_key	TEXT	yes			M062
Indexes:

• idx_suppliers_name_key_active — UNIQUE · (name_key) · WHERE is_active = 1 (M062)

tags

Introduced: M003. Columns: 6. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M003
name	TEXT	no			M003
color_hex	TEXT	yes			M003
description	TEXT	yes			M003
is_system	INTEGER	no	0		M003
created_at	TEXT	no	CURRENT_TIMESTAMP		M003

tax_categories

Introduced: M018. Columns: 12. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
name	TEXT	no			M018
display_name	TEXT	no			M018
description	TEXT	yes			M018
applies_gst	INTEGER	no	1	CHECK (applies_gst IN (0,1))	M018
applies_pst	INTEGER	no	1	CHECK (applies_pst IN (0,1))	M018
is_context_dependent	INTEGER	no	0	CHECK (is_context_dependent IN (0,1))	M018
context_rule	TEXT	yes			M018
legal_reference	TEXT	yes			M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018
updated_at	TEXT	no	CURRENT_TIMESTAMP		M018

tax_rates

Introduced: M018. Columns: 10. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M018
name	TEXT	no			M018
display_name	TEXT	no			M018
rate_basis_points	INTEGER	no			M018
jurisdiction	TEXT	yes			M018
applies_to	TEXT	no		CHECK (applies_to IN ('gst','pst'))	M018
effective_from	TEXT	yes			M018
effective_until	TEXT	yes			M018
is_active	INTEGER	no	1	CHECK (is_active IN (0,1))	M018
created_at	TEXT	no	CURRENT_TIMESTAMP		M018

transaction_discounts

Introduced: M027. Columns: 9. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M027
transaction_id	INTEGER	no		FK → transactions.id	M027
discount_type	TEXT	no			M027
description	TEXT	yes			M027
amount_cents	INTEGER	no			M027
applied_by_staff_id	INTEGER	yes		FK → staff.id	M027
created_at	TEXT	no	CURRENT_TIMESTAMP		M027
transaction_line_id	INTEGER	yes			M028
discount_id	INTEGER	yes			M028
Indexes:

• idx_txn_disc_txn — (transaction_id) (M027)
• idx_txn_discounts_line — (transaction_line_id) (M028)

transaction_lines

Introduced: M027. Columns: 24. Indexes: 3. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M027
transaction_id	INTEGER	no		FK → transactions.id	M027
line_number	INTEGER	no	1		M027
variant_id	INTEGER	yes		FK → product_variants.id	M027
description	TEXT	no			M027
sku	TEXT	yes			M027
manufacturer_name	TEXT	yes			M027
model	TEXT	yes			M027
quantity	INTEGER	no	1		M027
unit_price_cents	INTEGER	no	0		M027
original_unit_price_cents	INTEGER	yes			M027
cost_at_sale_cents	INTEGER	yes			M027
discount_cents	INTEGER	no	0		M027
line_total_cents	INTEGER	no	0		M027
tax_category_id	INTEGER	yes		FK → tax_categories.id	M027
serial_number	TEXT	yes			M027
is_voided	INTEGER	no	0	CHECK (is_voided IN (0,1))	M027
refunded_quantity	INTEGER	no	0		M027
created_at	TEXT	no	CURRENT_TIMESTAMP		M027
return_condition	TEXT	yes			M029
aim_id	INTEGER	yes			M038
account_credit_customer_id	INTEGER	yes			M046
fitted_to_bike	INTEGER	no	0		M050
rental_unit_id	INTEGER	yes		FK → inventory_units.id	M072
Indexes:

• idx_txn_lines_txn — (transaction_id) (M027)
• idx_txn_lines_variant — (variant_id) (M027)
• idx_tx_lines_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)

transaction_taxes

Introduced: M027. Columns: 7. Indexes: 2. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M027
transaction_id	INTEGER	no		FK → transactions.id	M027
tax_name	TEXT	no			M027
rate_basis_points	INTEGER	no			M027
taxable_amount_cents	INTEGER	no			M027
tax_amount_cents	INTEGER	no			M027
created_at	TEXT	no	CURRENT_TIMESTAMP		M027
Indexes:

• idx_txn_taxes_txn — (transaction_id) (M027)
• idx_txn_taxes_txn_name_u — UNIQUE · (transaction_id, tax_name) (M135)

transactions

Introduced: M004. Columns: 34. Indexes: 6. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M004
reference_number	TEXT	yes			M004
transaction_type	TEXT	no	'sale'		M004
customer_id	INTEGER	yes			M004
occurred_at	TEXT	no	CURRENT_TIMESTAMP		M004
subtotal_cents	INTEGER	no	0		M004
total_cents	INTEGER	no	0		M004
amount_received_cents	INTEGER	no	0		M004
status	TEXT	no	'posted'		M004
staff_id	INTEGER	yes			M004
staff_initials	TEXT	yes			M004
related_ticket_id	INTEGER	yes			M004
created_at	TEXT	no	CURRENT_TIMESTAMP		M004
total_tax_cents	INTEGER	no	0		M027
total_discount_cents	INTEGER	no	0		M027
amount_outstanding_cents	INTEGER	no	0		M027
posted_at	TEXT	yes			M027
voided_at	TEXT	yes			M027
voided_by_staff_id	INTEGER	yes			M027
void_reason	TEXT	yes			M027
related_transaction_id	INTEGER	yes			M027
tax_exempt_status_card_number	TEXT	yes			M027
tax_override_reason	TEXT	yes			M027
notes	TEXT	yes			M027
updated_at	TEXT	yes			M027
restocking_fee_cents	INTEGER	no	0		M029
return_reason	TEXT	yes			M029
deposit_kind	TEXT	yes			M036
deposit_status	TEXT	yes			M036
settled_transaction_id	INTEGER	yes		FK → transactions.id	M036
is_price_locked	INTEGER	no	1		M036
quoted_at	TEXT	yes			M036
aim_id	INTEGER	yes			M038
rental_booking_id	INTEGER	yes		FK → rental_bookings.id	M072
Indexes:

• idx_tx_customer — (customer_id, occurred_at DESC) (M004)
• idx_tx_occurred — (occurred_at DESC) (M004)
• idx_txn_customer_occurred — (customer_id, occurred_at, id) (M006)
• idx_txn_occurred — (occurred_at, id) (M006)
• idx_tx_deposit_status — (deposit_status) · WHERE deposit_status IS NOT NULL (M036)
• idx_transactions_aim — UNIQUE · (aim_id) · WHERE aim_id IS NOT NULL (M038)

unlock_accounts

Introduced: M158. Columns: 9. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M158
google_email	TEXT	no			M158
google_sub	TEXT	yes			M158
staff_id	INTEGER	yes			M158
phone	TEXT	no			M158
active	INTEGER	no	1		M158
created_at	TEXT	no	(datetime('now')		M158
created_by_staff_id	INTEGER	yes			M158
last_unlock_at	TEXT	yes			M158

unlock_codes

Introduced: M158. Columns: 7. Indexes: 0. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	TEXT	yes		PK	M158
account_id	INTEGER	no			M158
code_hash	TEXT	no			M158
attempts	INTEGER	no	0		M158
created_at	TEXT	no	(datetime('now')		M158
expires_at	TEXT	no			M158
used_at	TEXT	yes			M158

website_content

Introduced: M122. Columns: 7. Indexes: 1. Triggers: 0.

Column	Type	Null	Default	Constraints	Since
id	INTEGER	yes		PK	M122
page	TEXT	no			M122
block	TEXT	no			M122
html	TEXT	no			M122
original	TEXT	yes			M122
updated_by	INTEGER	yes		FK → staff.id	M122
updated_at	TEXT	no	CURRENT_TIMESTAMP		M122

Unique (table-level):

• (page, block) (M122)

Indexes:

• idx_website_content_page — (page) (M122)