DATABASE DESIGN

VietGara — Multi-Tenant SaaS Platform for Auto Garage Management & Maintenance

Version 1.10 — October 12, 2026 · Cloud: Google Cloud Platform (GCP) · Backend: Go

1. Introduction

This document describes the database design for VietGara’s business modules. While the backend is a modular monolith (ADR-001), all modules share one Cloud SQL for PostgreSQL database and the public schema; each module owns its tables and reaches another module’s data only through that module’s code (ADR-008). Per ADR-005, the data isolation unit (tenant) is garage_id — every garage business table includes it and has Row-Level Security applied. Migrations live in vietgara-backend/db/migrations (golang-migrate).

1.1. General Conventions

• Primary key: UUID generated by the application (except for join tables with composite keys).
• Enums for constant values: statuses, types, providers, locales, permissions and features are PostgreSQL enum types (Section 10), never free-text varchar.
• Every garage business table has garage_id, an index on it, and an RLS policy.
• Audit columns (NFR-MT-07, migration 000022): every business table has created_at, created_by, updated_at, updated_by, filled by the trigger set_row_audit() from the session user (app.current_user_id; NULL for system jobs such as the expiry job, the outbox relay and sign-up). created_* never change and updated_at is the time of the change, so no code path can forget them. Append-only tables (stock_transactions, audit_log, outbox_events, notification_log) and link tables whose rows are only added or removed (platform_admins, role_permissions, staff_group_members) have the created pair only, filled by set_created_audit(). processed_events and idempotency_keys are the only tables with neither, listed with the reason in the allowlist of db/audit_columns_test.go; that schema test fails when a new table has neither the columns and trigger nor an allowlist entry.
• Migrations: while the product is in development (no non-test tenant exists) migrations are edited in place: a column added to an existing table goes into the migration that created it, not into a new ALTER migration (db/README.md), and make db-reset rebuilds a database; a new table gets a new migration. From the first real tenant (the migration freeze) migrations are forward-only.
• References between modules. A module owns its tables and no other module queries or writes them (the use-case interface is the only door). Rows therefore refer to another module’s rows by plain uuid, without a foreign key (for example supplier_id on a goods receipt line, item_ref_id on a quote line), and the module that writes the row checks the reference. The one deliberate exception is a foreign key towards reference data that the row cannot mean anything without: Inventory, Quote and Repair Order point at Catalogue parts, units and categories (Section 5.1). Catalogue never points back.
• Soft delete (nullable deleted_at) for tables with historical or legal significance (customers, vehicles, suppliers, files, quotes, repair orders). Settlements and their payments are never deleted.
• Delete-or-deactivate (FR-CAT-07): customers, vehicles, suppliers and files also carry is_active boolean not null default true. A row that other rows still refer to is deactivated (is_active = false); one that nothing refers to is soft-deleted (deleted_at). The reference check is a UsageChecker port of the module (pkg/lifecycle), implemented in cmd/server over the referencing modules, never a join across modules. A deactivated row keeps its unique values (plate, phone, name).
• Phones and e-mail addresses are stored normalized: phones in E.164 (+84901234567, pkg/phone), e-mail trimmed and lower-cased (pkg/email), for users, customers and suppliers, so a customer’s phone can be matched with a user’s verified phone (FR-CUS-06).
• Document snapshots (FR-TEN-06): an issued document keeps the garage’s legal identity as it was in an issuer jsonb column (quotes, repair_orders, settlements, settlement_payments; keys in Section 2.2), so a later change of the profile never rewrites it.
• Time zone: timestamps are stored in UTC (timestamptz); a garage’s local time zone defaults to Asia/Ho_Chi_Minh.
• Money and quantities: bigint VND amounts for prices and totals. Quantities are numeric(14,3) and stock_transactions.unit_cost is numeric(18,4); the repositories read them as trim_scale(…)::text, so the API keeps its decimal strings ("1.5").

1.2. Row-Level Security Pattern

-- NULL-safe helpers: an unset or empty setting matches nothing.
CREATE FUNCTION app_current_garage_id()  RETURNS uuid LANGUAGE sql STABLE
  AS $$ SELECT NULLIF(current_setting('app.current_garage_id', true), '')::uuid $$;
CREATE FUNCTION app_current_user_id() RETURNS uuid LANGUAGE sql STABLE
  AS $$ SELECT NULLIF(current_setting('app.current_user_id', true), '')::uuid $$;

ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers FORCE ROW LEVEL SECURITY;   -- binds the table owner too
CREATE POLICY customers_rls_policy ON customers FOR ALL
  USING (garage_id = app_current_garage_id())
  WITH CHECK (garage_id = app_current_garage_id());

-- The backend sets both settings at the start of every transaction:
-- SELECT set_config('app.current_garage_id', $1, true), set_config('app.current_user_id', $2, true);

The backend connects as a login in the role vietgara_app (NOSUPERUSER NOBYPASSRLS), so the policies always apply; migrations run as the schema owner. Tables that a user must see outside a single garage (its garages, memberships, invitations, roles) have policies that also admit rows of app_current_user_id() — see Section 7.

2. Identity & Tenant/Garage Management

2.1. Table: users (user level, no RLS)

Column	Data Type	Notes
id	uuid PK
email	varchar(255) not null	Unique case-insensitively (lower(email)); required for every user
phone	varchar(20) unique nullable	Optional profile field used to find staff; stored normalized
auth_provider	user_auth_provider	email | google
identity_platform_uid	varchar(128) unique nullable	Google subject id for Google accounts
full_name	varchar(255)
password_hash	varchar(255) nullable	bcrypt; null for Google-only users
status	user_status	active | suspended
email_verified_at	timestamptz nullable	Null until the e-mail OTP is confirmed; set at sign-up for Google accounts
locale	user_locale	vi (default) | en — UI and e-mail language
full_name_source	user_name_source	google | user (default user); a Google sign-in updates full_name only while the source is google (FR-IAM-12)
created_by / updated_by, created_at / updated_at		Audit columns (Section 1.1)

platform_admins (user_id PK → users, created_at) lists platform administrators, synchronized on startup from PLATFORM_ADMIN_EMAILS. Sessions, refresh tokens and OTPs are kept in Redis, not in PostgreSQL.

2.2. Table: garages (each row = one tenant; branch = garage)

Column	Data Type	Notes
id	uuid PK	= garage_id, the tenant id throughout the system
user_id	uuid FK → users.id	The owner (1 user – many garages)
name	varchar(255)
tax_code	varchar(20) nullable
address	text nullable
phone / email	varchar nullable	Garage contact details
legal_name / legal_address / representative	varchar(255) / text / varchar(255), nullable	The garage’s legal identity printed in the header of every document with tax_code and the logo (FR-TEN-06); name stays the trading name. Documents print the legal values and fall back to the trading ones
bank_details	jsonb	{bank_name, account_number, account_holder}: the common BankDetails message, also used by suppliers
logo_key	varchar(500) nullable	Object key of the logo in the file registry (Section 6.7), never a URL, so a CDN or bucket change breaks nothing; the API’s logoUrl is built from it
timezone	varchar(64)	Default Asia/Ho_Chi_Minh
currency	varchar(3)	Default VND
status	garage_status	active | inactive
operating_hours	jsonb	[{day, closed, open, close}] per weekday
quote_number_template / repair_order_number_template / settlement_number_template / payment_voucher_number_template	varchar(50)	Defaults BG-{YYYY}{MM}-{SEQ:4}, LSC-{YYYY}{MM}-{SEQ:4}, QT-{YYYY}{MM}-{SEQ:4} and PT-{YYYY}{MM}-{SEQ:4} (payment voucher, FR-BIL-05). Each template has {YYYY}, {YY}, {MM}, {DD} and exactly one {SEQ:n}. The columns were added to migration 000002 itself (pre-deployment convention)
created_by / updated_by, created_at / updated_at

The issuer snapshot copied onto a document is a JSON object {name, address, phone, tax_code, representative, bank_name, account_number, account_holder, logo_key} (pkg/issuer): the legal name (or the trading name when none is set), address, phone, tax code, representative, bank account and the logo’s object key at that moment. A quote takes it when it is created and again when it is sent; a repair order and a settlement when they are created; a payment voucher when it is issued. A document without a snapshot (older rows) prints the garage’s current profile.

2.3. Table: garage_staff

Column	Data Type	Notes
id	uuid PK
garage_id	uuid FK → garages.id	RLS
user_id	uuid FK → users.id	Staff are existing users; unique per garage
status	staff_status	active | inactive
created_by / updated_by, created_at / updated_at

What a member may do comes from their staff groups (2.5), not from a role column.

2.4. Tables: roles, role_permissions (owner level)

Table	Columns	Notes
roles	id, owner_user_id → users, name, description, default_key default_role nullable, audit columns	A role belongs to the owner and is shared by all their garages; name unique per owner. default_key marks the generated default roles (manager, accountant, service_advisor, technician, inventory_staff)
role_permissions	role_id → roles, permission permission (PK both)	Permission catalogue in Section 10

2.5. Tables: staff_groups, staff_group_members (garage level)

Table	Columns	Notes
staff_groups	id, garage_id, name, description, role_id → roles, default_key, audit columns	One role per group; name unique per garage; RLS
staff_group_members	group_id, staff_id → garage_staff, garage_id, created_at (PK group_id, staff_id)	RLS

2.6. Table: staff_invitations

Column	Data Type	Notes
id	uuid PK
garage_id	uuid FK	RLS (garage side)
user_id	uuid FK → users.id	The invitee, an existing user
group_ids	uuid[]	Groups joined on acceptance
status	invitation_status	pending | accepted | declined | revoked | expired; one pending invitation per garage and user
invited_by	uuid FK → users.id
expires_at / responded_at	timestamptz	7-day validity
created_at / updated_at	timestamptz

3. Customer & Vehicle

3.1. Table: customers

Column	Data Type	Notes
id	uuid PK
garage_id	uuid FK	RLS
full_name / phone / email / address	varchar / text	phone is stored in E.164 and email lower-cased; the phone is unique per garage among live rows (customers are searched by national or international form, phone.SearchKey). A customer needs no VietGara user; the optional link to one is customer_user_links (Section 3.4) and its tax identities are customer_tax_profiles (Section 3.5). customers keeps no user column
is_active	boolean	Default true. A customer that quotes, repair orders or settlements refer to (including through its vehicles) is deactivated instead of deleted: it leaves pickers and staff can reactivate it (FR-CAT-07)
consent_log	jsonb	Append-only list of personal-data consent decisions (Decree 13/2023/ND-CP)
created_by / updated_by, created_at / updated_at / deleted_at		Trigram indexes on name, phone and e-mail

3.2. Table: vehicles

Column	Data Type	Notes
id	uuid PK
garage_id	uuid FK	RLS
customer_id	uuid FK → customers.id	Composite FK (garage_id, customer_id) keeps both in one garage
license_plate	varchar(20)	As entered
license_plate_key	varchar (generated)	Plate reduced to A–Z/0–9; unique per garage among live rows, used for search
make / model / year	varchar / int
vin / engine_number	varchar(50)
is_active	boolean	Default true. A vehicle that quotes or repair orders refer to is deactivated instead of deleted, so its history stays (FR-CAT-07); deleting a customer applies the same rule to each vehicle
audit columns, deleted_at		Whether the identity fields (license_plate, make, model, year, vin, engine_number) are editable is not stored: it is computed from the live quotes and repair orders that refer to the vehicle (a settlement belongs to a repair order; a soft-deleted quote does not count) and returned as editable and lockedFields (FR-CUS-02). A page of vehicles is answered with one query per source, not one per row

3.3. Table: suppliers (Supplier module, migration 000007)

A supplier is a business that sells parts to the garage (FR-SUP-01). It is not a customer, and an insurer is not a supplier: insurers return as their own insurers table in Phase 17 (Section 11.11). Release 5.0 replaces the partners table and its partner_type enum with this table.

Column	Data Type	Notes
id	uuid PK
garage_id	uuid FK	RLS
name	varchar(255)	Unique per garage among live rows, case-insensitively (lower(name))
tax_code	varchar(20) nullable
contact_info	jsonb	{contact_person, phone, email, address}; phone and e-mail normalized
bank_details	jsonb	{bank_name, account_number, account_holder} (BankDetails)
note	text nullable
is_active	boolean	Default true; a supplier that documents refer to is deactivated, not deleted (FR-CAT-07). From Release 7.0 goods receipts, part suppliers and stock issues refer to suppliers (Sections 5.10 to 5.12); a supplier that any of them refers to is deactivated
audit columns, deleted_at

3.4. Table: customer_user_links (Release 5.1, migration 000024)

The link between a garage’s customer profile and a VietGara user, made with the user’s consent (FR-CUS-06).

Column	Data Type	Notes
id	uuid PK	Keys the one-time code of the request
garage_id / customer_id	uuid FK	Composite FK (garage_id, customer_id) → customers, ON DELETE CASCADE
user_id	uuid FK → users.id	ON DELETE CASCADE. Never returned to the garage’s side of the API
status	customer_user_link_status	requested | linked | declined | unlinked. CHECK: a linked row has a channel and a linked_at
channel	customer_user_link_channel, nullable	How it was confirmed: otp (the code staff entered), app (accepted in the Customer App), auto_email (verified e-mail matched at sign-in). Set once linked
requested_at / linked_at / ended_at	timestamptz	ended_at is set when a request is declined or a link is unlinked, by either side
audit columns		Row audit trigger (Section 1.1)

• One current link per customer: partial unique index on (customer_id) where status IN ('requested','linked'). Declined and unlinked rows stay as history and are never re-linked by the e-mail match.
• Row-Level Security (the only table with two doors): garage_id = app_current_garage_id() OR user_id = app_current_user_id(), so a garage’s transaction sees its own customers’ links and a user’s transaction sees the links addressed to them, in any garage.
• Indexes: (garage_id, customer_id, created_at DESC) for the customer’s link state (derived into Customer.linkStatus: the current link’s status, else the latest ended one, else unspecified) and (user_id, status) for the Customer App’s list.
• The one-time code is not stored here: it lives in the OTP store (Redis) like the e-mail OTP, with purpose customer_link and the link id as its key (2-minute validity, 5 attempts, 30-second resend, as FR-IAM-01).

3.5. Table: customer_tax_profiles (Release 5.1, migration 000025)

The legal identities a customer can be billed under (FR-CUS-07).

Column	Data Type	Notes
id	uuid PK
garage_id / customer_id	uuid FK	RLS by garage_id; composite FK (garage_id, customer_id) → customers, ON DELETE CASCADE
kind	customer_tax_profile_kind	personal | company; never changes
legal_name	varchar(255)	The company’s legal name, or the person’s name
tax_id	varchar(13), nullable	CHECK 10 digits, or 13 with the branch suffix
address / invoice_email	varchar(500) / varchar(255), nullable	The e-invoice address (FR-EIN-02)
is_default / is_active	boolean	is_active false once deactivated instead of deleted because a document was billed to it (FR-CAT-07); a deactivated profile loses its default mark
audit columns, deleted_at		Soft-deleted when unused

Partial unique indexes among live rows: one personal profile per customer, and one default per customer.

4. Quote & Repair Order

4.1. Table: quotes (Release 1.2, migration 000015)

Column	Data Type	Notes
id	uuid PK
garage_id / customer_id / vehicle_id	uuid FK	RLS by garage_id; composite FKs (garage_id, customer_id) / (garage_id, vehicle_id) keep both in one garage, the same pattern as vehicles → customers
customer_name / vehicle_license_plate	varchar	Denormalized from the Customer module at creation/update time (via VehicleReader), so this module never queries customers/vehicles directly (HLD Section 2: each module owns its own tables)
number	varchar(50)	Rendered from the garage’s quote_number_template (FR-TEN-05)
status	quote_status enum	draft | sent | approved | rejected | converted
approval_mode	quote_approval_mode enum	link | manual (FR-QUO-02, defined in migration 000002 alongside garages.default_quote_approval_mode)
notes	text
total_amount	bigint	Sum of quote_lines.line_total; VND
issuer	jsonb	The garage’s legal identity snapshot (Section 2.2), taken at creation and again when the quote is sent; printed in the quote’s PDF header (FR-TEN-06)
billing_party	jsonb	Who the quote is billed to (FR-CUS-07), a snapshot of the customer’s tax profile: tax_profile_id, kind, legal_name, tax_id, address, invoice_email. {} is a retail receipt. Re-resolved when the customer changes; a later edit of the profile never rewrites it. Indexed on (garage_id, (billing_party->>'tax_profile_id')) for live quotes, which answers “was a document billed to this profile?”
public_token_hash / public_token_expires_at	char(64) / timestamptz, nullable	SHA-256 of the LINK-mode approval token; the plaintext is never stored, only returned once by SendQuote. Cleared once the quote is decided
sent_at / decided_at / converted_at	timestamptz nullable
created_by / updated_by, created_at / updated_at, deleted_at		Soft-deleted: vehicle history (FR-CUS-03) and repair orders keep referencing a quote

One live quote per (garage_id, number); (garage_id, public_token_hash) unique where set. find_quote_by_public_token(token_hash) (SECURITY DEFINER, same pattern as owner_usage()) resolves a token to (id, garage_id) for the public approval link, which names no garage of its own — see Security Architecture Section 2.

4.2. Table: quote_lines (Release 1.2, migration 000015)

Column	Data Type	Notes
id	uuid PK
garage_id / quote_id	uuid / uuid FK	garage_id denormalized from the parent quote for RLS, the same pattern as every other tenant table
position	integer	Display order
item_type	quote_item_type enum	labor | part
item_ref_id	uuid nullable	A part id (parts.id, Section 5); not a database foreign key — carried over unchanged onto repair_order_lines.item_ref_id at conversion, which is what stock deduction (FR-INV-02) actually reads. The missing foreign key is safe because of the lifecycle rule: a part that a line refers to is deactivated, never hard-deleted (FR-CAT-07); a nightly integrity check in the test suite flags any orphan
description	text
quantity	numeric(14,3)	A real decimal since Release 5.0 (it was a validated decimal string): valuation, profit per repair order and purchases by supplier aggregate quantities in SQL, so every quantity column is numeric(14,3) (this table, repair_order_lines, stock_transactions, stock_balance, parts.reorder_threshold, and from Release 7.0 goods_receipt_lines, stock_issue_lines). The API still sends and accepts decimal strings. With units (Release 6.0) it is always the base-unit amount
unit_id / entered_quantity	uuid nullable / numeric(14,3) nullable	Release 6.0: the unit the user chose and the amount typed in that unit (composite FK (garage_id, unit_id) into units). Null for free-form labor lines and for lines in the part’s base unit; quantity stays the base-unit amount, which keeps the Phase 2 apps unchanged
unit_price / line_total	bigint	VND
decision	quote_line_decision enum	pending | approved | rejected (FR-QUO-03). A hidden consumable is decided with its job, not by the customer
job_id	uuid nullable	Release 7.3 (migration 000031): the catalogue job the line came from (FR-CAT-06). Not a foreign key: the line lives in another module, and a job a line came from is deactivated, never deleted. Indexed (garage_id, job_id)
non_billable	boolean	Release 7.3: a hidden consumable (FR-CAT-08). Always a part line with item_ref_id; unit_price and line_total are 0, so it never reaches total_amount, the public quote or the PDFs
decided_at	timestamptz nullable

4.3. Table: repair_orders (Release 1.3, migration 000016)

Owned by the Repair Order module (HLD module #6): the columns below serve the Kanban board, ad-hoc creation and conversion from a quote (FR-QUO-04).

Column	Data Type	Notes
id	uuid PK
garage_id / vehicle_id	uuid FK	RLS by garage_id
quote_id	uuid FK, nullable, unique	The quote it was converted from; nullable for ad-hoc creation (FR-REP-01). Unique: a quote converts at most once
vehicle_license_plate / customer_name	varchar	Denormalized at creation, the same pattern as quotes (Section 4.1), so this module never queries Customer’s tables directly
number	varchar(50)	Rendered from repair_order_number_template
status	repair_order_status enum	open | in_progress | waiting_on_parts | completed | closed | cancelled — the Kanban board’s columns (UI Design Spec 3.3); derived from the lines’ own status (hidden consumables ignored) and waiting_on_parts, except closed and cancelled, which only their endpoints set (cancelled is Release 7.3, migration 000031)
waiting_on_parts	boolean	Release 7.3: Inventory’s answer that the order’s draft stock issue cannot be covered. While set, an order that is open or in progress is shown as waiting_on_parts; stored so an edit of a line does not lose it. Set from stock_issue.shortage_changed, which re-reads Inventory
total_amount	bigint	Sum of repair_order_lines.line_total
issuer	jsonb	The garage’s legal identity snapshot (Section 2.2) taken when the repair order is created (also by quote conversion)
billing_party	jsonb	The quote’s billing party, copied by quote conversion ({} for an ad-hoc repair order); the settlement starts from it. Not returned by the Repair Order API
closed_at	timestamptz nullable
created_by / updated_by, created_at / updated_at, deleted_at

4.4. Table: repair_order_lines (Release 1.3, migration 000016)

Column	Data Type	Notes
id	uuid PK
garage_id / repair_order_id	uuid / uuid FK	garage_id denormalized, as in quote_lines
position / item_type / item_ref_id / description / quantity / unit_price / line_total	—	Copied unchanged from the quote’s approved lines on conversion, or entered directly on ad-hoc creation
unit_id / entered_quantity	uuid nullable / numeric(14,3) nullable	Release 6.0, as in quote_lines
job_id / non_billable	uuid nullable / boolean	Release 7.3, as in quote_lines; copied by conversion. A hidden consumable needs no progress, so the status and close rules ignore it
status	repair_order_line_status enum	not_started | in_progress | completed (FR-REP-03), tracked separately from the repair order’s own status
assigned_technicians	uuid[]	Technician user ids assigned to this line (FR-REP-02); a line may have more than one. GIN-indexed for ListRepairOrders?technicianId=

repair_order_photos (before/after photos, FR-REP-04) is Phase 11 (files of the registry, Release 5.0).

4.5. Table: document_sequences (platform, migration 000014)

Not owned by any business module — infrastructure like outbox_events. Backs the numbering templates of FR-TEN-05: Quote (Release 1.2), Repair Order (Release 1.3) and Settlement (Release 1.4) each atomically reserve their next sequence number.

Column	Data Type	Notes
garage_id	uuid FK	RLS; part of the composite PK
doc_type	varchar(30)	'quote' | 'repair_order' | 'settlement' | 'payment_voucher'; free text, not an enum, so a later document type needs no migration here
period	varchar(20)	The garage’s numbering template’s date placeholders rendered for “now” (e.g. "BG-202609-{SEQ:4}" for a {YYYY}{MM} template) — the counter resets exactly when that date part changes
next_seq	integer	INSERT … ON CONFLICT (garage_id, doc_type, period) DO UPDATE SET next_seq = next_seq + 1 RETURNING next_seq, one round trip, safe under concurrent transactions

PK (garage_id, doc_type, period).

5. Catalogue & Inventory (Releases 1.3, 6.0, 7.0 and 7.1; migrations 000017, 000028 and 000029)

Two modules share this section. Migration 000017 holds the Catalogue and Inventory tables that existed from Release 6.0 (edited in place until go-live; the Catalogue tables are created first because the Inventory tables refer to them), migration 000028 adds the goods receipt, part supplier and stock issue tables of Release 7.0, and migration 000029 adds the stock count, write-off, supplier return and repair order part return tables of Release 7.1 (Section 5.13), and migration 000030 adds part_barcodes and import_runs of Release 7.2 (Section 5.14). Ownership is by module, not by file:

Module (HLD)	Tables	What it holds
Catalogue (#18)	parts (the part), units, part_units, part_categories, part_images; Phase 7: jobs, job_parts	What a part is: identity (SKU, name), base unit, packaging units with their factors, selling price, category tree, brand, images, active flag; and later the job catalogue with its suggested parts
Inventory (#7)	stock_balance, stock_transactions, goods_receipts, goods_receipt_lines, part_suppliers, stock_issues, stock_issue_lines (Release 7.0); stock_counts, stock_count_lines, write_offs, write_off_lines, supplier_returns, supplier_return_lines, repair_order_part_returns (Release 7.1); Phase 7: part_barcodes	How many there are and what happened to them: every quantity, movement, document, supplier and the average cost of a part

Release 7.0 renames the part’s table from items to parts (and item_units and item_images to part_units and part_images); the API calls the resource a part (/parts), and the SKU code is the “part code” of the screens. Foreign keys are named after parts (part_id) from Release 7.0 on; the item_ref_id columns of quote and repair-order lines keep their name, and hold a part id.

5.1. Dependency rule between the two modules

flowchart LR
  INV[Inventory] --> CAT[Catalogue]
  QT[Quote] --> CAT
  RO[Repair Order] --> CAT

• The dependency is one-way: Inventory, Quote and Repair Order use Catalogue; Catalogue uses none of them. Catalogue never reads stock, quotes, repair orders or any other module’s tables.
• A foreign key towards a Catalogue table is allowed and expected, because a part, unit or category must exist for the row to mean anything: stock_transactions.part_id (composite (garage_id, part_id) into parts), stock_transactions.unit_id, stock_balance.part_id, goods_receipt_lines.part_id, stock_issue_lines.part_id, quote_lines.unit_id, repair_order_lines.unit_id (all composite (garage_id, …) keys, ON DELETE RESTRICT). This is the one exception to “no foreign key across modules” (Section 1.1), and it only goes in this direction. The item_ref_id of a quote or repair-order line stays a plain uuid checked by the use case against Catalogue, because a line may also be free text.
• Everything that describes a part is inside Catalogue, so part_units, part_images, parts.category_id and (Phase 7) job_parts are real foreign keys within one module.
• Questions Catalogue cannot answer from its own tables go through a port wired in cmd/server, the same mechanism as the delete-or-deactivate UsageChecker (Section 1.1): “does this part, unit or category have stock movements, documents or other users?”. Catalogue defines the port; the wiring implements it over the Inventory, Quote and Repair Order tables. Catalogue never imports those modules.
• Catalogue exposes the operations the others call: look up and validate a part, unit or category, convert a quantity to the base unit (Section 5.9), and the shared delete-or-deactivate rule (FR-CAT-07).
• The stock-on-hand list (GET /stock-levels) is an Inventory read: it asks Catalogue for the matching parts (query, category, brand) or starts from its own balances (low stock, sort by quantity) and joins the other half by part id. Catalogue stays free of any quantity.
• Permissions: the Catalogue endpoints, parts included, are guarded by catalogue.read / catalogue.write (Release 6.0, Section 10); Inventory keeps inventory.read / inventory.write for stock and documents, and reads cost through inventory.cost.read (Release 7.0). A role that holds a write permission also holds its read permission. The job catalogue of Phase 7 is guarded by the quote permissions.

5.2. Table: parts (Catalogue — the part)

Column	Data Type	Notes
id	uuid PK
garage_id	uuid FK	RLS by garage_id
sku	varchar(50)
name	varchar(255)
base_unit_id	uuid NOT NULL	The unit stock is stored in; composite FK (garage_id, base_unit_id) into units (Section 5.5). It replaces the free-text unit of Releases 1.3 to 5.x. It cannot change once the part has stock transactions, packaging units or document lines (Catalogue asks the usage port of Section 5.1), because every stored quantity is expressed in it (Section 5.9)
sale_price	bigint NOT NULL DEFAULT 0	What the customer pays per base unit, whole dong (FR-CAT-09). Separate from cost (stock_balance.average_cost, Release 7.0)
category_id	uuid nullable	Composite FK into part_categories; a part has at most one category
brand	varchar(100)	Free text, empty when unknown (FR-CAT-03)
is_active	boolean	A part in use is deactivated, not deleted (FR-CAT-07)
reorder_threshold	numeric(14,3)	A stock policy (the display half of FR-INV-04; the notification is Release 10.1), kept on the part for simplicity and read by Inventory. Always in the base unit
created_by / updated_by, created_at / updated_at, deleted_at		Soft-deleted: past stock transactions keep referencing the part

Unique (garage_id, sku) where live (partial index). Trigram indexes on sku, name and brand back the “contains” search behind ListParts?query=; a btree on (garage_id, category_id) backs the category filter.

5.3. Table: stock_balance (Inventory)

Column	Data Type	Notes
garage_id / part_id	uuid, composite PK	Composite FK into Catalogue’s parts (allowed direction, Section 5.1)
quantity_on_hand	numeric(14,3)	Always in the base unit. Atomically incremented/decremented in place (quantity_on_hand = quantity_on_hand ± $qty) on every transaction. Read as trim_scale(…)::text. Nothing stops it going below zero: an issue never fails for lack of stock (Business Procedures P12)
average_cost	numeric(18,4) NOT NULL DEFAULT 0, ≥ 0	The weighted-average cost per base unit, VND (FR-INV-07, Release 7.0). Changed by each completed goods receipt line that records a cost, never by an issue; a cancelled receipt restores the previous value
created_by / updated_by, created_at / updated_at		Row audit columns (Section 1.1)

Rows are upserted by the Inventory use case in the same database transaction as the movement, so the balance and the ledger cannot disagree.

5.4. Table: stock_transactions (Inventory, append-only)

Column	Data Type	Notes
id	uuid PK
seq	bigint identity	Total order of the movements in the garage; created_at is the start of the transaction that wrote them, equal for movements written together
garage_id / part_id	uuid FK	RLS by garage_id; composite FK into Catalogue’s parts
type	stock_transaction_type enum	in (a completed goods receipt line) | out (a posted stock issue line, or the reversal of a cancelled receipt line)
quantity	numeric(14,3)	Always positive and in the base unit; type gives the direction
unit_cost	numeric(18,4) nullable	VND per base unit, four decimals so rounding does not drift the average: the line’s cost for a receipt, the average-cost snapshot for an issue. NULL when no cost was recorded
unit_id / entered_quantity	uuid nullable / numeric(14,3)	The packaging unit the user entered and the typed amount (Release 6.0); NULL when the base unit was used. quantity is always the base amount
ref_type	stock_transaction_ref_type enum	goods_receipt | stock_issue (Release 7.0); stock_count, write_off, supplier_return and repair_order_return (Release 7.1); purchase and repair_order are the movements recorded before Release 7.0, kept readable
ref_id	uuid	The goods receipt id for goods_receipt, the stock issue id for stock_issue, the stock count, write-off, supplier return or repair order part return id for the Release 7.1 types
supplier_id	uuid nullable	The supplier of the receipt line the movement came from; composite FK into suppliers; NULL for issues
quantity_after / average_cost_after	numeric(14,3) / numeric(18,4)	The part’s quantity on hand and average cost right after this movement, so the valuation report answers “at a date” (FR-INV-07)
created_by / created_at		Row audit columns (Section 1.1)

Indexes: (garage_id, part_id, created_at DESC) and (garage_id, part_id, seq DESC) for the stock card, (garage_id, type, created_at DESC) for the movement report, and (garage_id, supplier_id) where the supplier is set. Idempotency guard: a unique partial index (garage_id, part_id, ref_id) where ref_type = 'stock_issue' lets a stock issue write each part’s movement at most once, on top of the generic processed_events check of every consumer.

5.5. Table: units (Catalogue)

Column	Data Type	Notes
id	uuid PK
garage_id	uuid FK	RLS by garage_id; UNIQUE (garage_id, id) for the composite keys above
name / abbreviation	varchar(50) / varchar(20)	Unique name per garage among live units, ignoring letter case
is_active	boolean	A unit in use is deactivated, never hard-deleted (FR-CAT-07)
created_by / updated_by, created_at / updated_at, deleted_at		Soft-deleted

A unit is per garage, not global: two garages may both have a “case” with different meaning, and the factor lives on the part, not on the unit.

5.6. Table: part_units (Catalogue)

The packaging units of one part. The base unit has no row.

Column	Data Type	Notes
garage_id / part_id / unit_id	uuid, composite PK	Composite FKs (garage_id, part_id) into parts (ON DELETE CASCADE) and (garage_id, unit_id) into units (ON DELETE RESTRICT)
factor	numeric(18,6) > 0	Base units per packaging unit (1 case = 24 cans: 24)
sale_price	bigint nullable, ≥ 0	VND per packaging unit; NULL means parts.sale_price × factor (FR-CAT-09)
created_by / created_at		Creation audit columns

Rules enforced by the use case: the part’s base unit is never one of its own packaging units; a unit appears at most once per part; every unit must be active; the table is replaced as a whole by PUT /parts/{partId}/units.

5.7. Table: part_categories (Catalogue)

Column	Data Type	Notes
id, garage_id	uuid	RLS by garage_id
parent_id	uuid nullable	Composite FK into part_categories; NULL for a top-level category
name	varchar(255)	Unique among live siblings, case-insensitive (partial unique index on (garage_id, coalesce(parent_id), lower(name)))
is_active	boolean	Deactivating a category does not change the parts in it

Depth is at most three levels (Engine oil > Synthetic > 5W-30), enforced by the use case, not by the schema. A category with children or with parts is deactivated or refused on delete.

5.8. Table: part_images (Catalogue)

Column	Data Type	Notes
garage_id / part_id / file_id	uuid, composite PK	Composite FK (garage_id, part_id) into parts; file_id is a files row of kind part_image (Section 6.7) of the same garage; no database foreign key, because the File module is another module
position	integer	Display order; index (garage_id, part_id, position)

At most three images per part; the use case checks that each file exists, belongs to the garage, is of kind part_image and is not attached to another part. Removing an image from a part deactivates its files row; the object is deleted when no part refers to it.

5.9. Quantities and units: the rule every module follows

1. Stock, thresholds, quote lines and repair-order lines are stored in the part’s base unit. A user may type any unit of the part; the database keeps the base amount in quantity and the typed pair in unit_id + entered_quantity, so documents print what was entered and reports can show both.
2. Conversion is done by the backend, on save, with quantity = entered_quantity × factor using exact decimal arithmetic. The result must fit numeric(14,3) (at most three decimals); otherwise the request is refused with VALIDATION_ERROR rather than rounded. Clients may preview but never send a converted amount for a packaging unit.
3. A base-unit amount has no unit_id. Free-form labor lines and part lines in the base unit keep both columns null, which is also what the Phase 2 apps send and read.
4. Example. Beer: base unit can, case = 24, selling price 9,000 per can, case price 200,000 (override; otherwise 216,000). Receiving 10 cases stores quantity = 240, unit_id = case, entered_quantity = 10. Issuing 30 cans leaves 210 cans, shown as 8.75 cases.
5. Changing a part’s base unit after it has stock, packaging units or document lines would silently change what every stored number means, so it is refused (FAILED_PRECONDITION); create a new part instead. Catalogue cannot see stock, so it asks the usage port (Section 5.1).

5.10. Tables: goods_receipts, goods_receipt_lines (Inventory, Release 7.0, migration 000028)

A goods receipt is a numbered, multi-line delivery document (FR-INV-06). Its header supplier is only a default that new lines inherit; every line carries its own supplier. Completing a receipt posts one stock_transactions row per line and updates the average cost; cancelling a completed receipt posts the reversing rows.

Table	Column	Data Type	Notes
goods_receipts	id, garage_id	uuid	RLS by garage_id; UNIQUE (garage_id, id)
	number	varchar(50)	From the garage’s numbering.goodsReceipt template (PN-{YYYY}{MM}-{SEQ:4} by default, Section 4.5); unique per garage
	status	goods_receipt_status enum	draft (default) | completed | cancelled
	default_supplier_id	uuid nullable	Composite FK into suppliers, ON DELETE RESTRICT; the default new lines inherit
	receipt_date	date	The receipt date in the garage’s time zone
	delivery_note_number	varchar(100)	The supplier’s own delivery note number; empty when none
	notes	text
	completed_at / cancelled_at	timestamptz nullable	Set when the receipt is completed or cancelled
	created_by / updated_by, created_at / updated_at		Row audit columns
goods_receipt_lines	id, garage_id	uuid	RLS by garage_id
	goods_receipt_id	uuid	Composite FK into goods_receipts, ON DELETE CASCADE
	position	integer	Order of the lines on the document
	part_id	uuid	Composite FK into parts
	unit_id	uuid nullable	The packaging unit typed; NULL for the base unit (Section 5.9)
	entered_quantity	numeric(14,3) > 0	What was typed, in unit_id or the base unit
	quantity	numeric(14,3) > 0	The base-unit amount, derived from entered_quantity on save
	supplier_id	uuid nullable	Composite FK into suppliers. A draft may leave it empty; completion refuses while any line has none (SUPPLIER_REQUIRED)
	unit_cost	bigint ≥ 0, default 0	VND per entered unit, as typed; 0 means “not recorded”. Stored per base unit on the movement (unit_cost ÷ factor)
	created_by / updated_by, created_at / updated_at		Row audit columns

Indexes: unique (garage_id, number) on goods_receipts; (garage_id, created_at DESC) for the list; (garage_id, goods_receipt_id, position) for the lines; (garage_id, supplier_id) and (garage_id, part_id) on goods_receipt_lines for the supplier filter and the purchases report. Row-Level Security and the audit trigger apply as on every business table.

Rules: a draft changes no stock; only a draft can be changed (GOODS_RECEIPT_NOT_DRAFT); a completed receipt is never edited, only cancelled. The draft → completed → cancelled order is enforced by the use case. Cancelling a completed receipt is refused (INSUFFICIENT_STOCK_TO_REVERSE) when a reversal would take a part below zero, that is, when part of its stock has been issued.

5.11. Tables: stock_issues, stock_issue_lines (Inventory, Release 7.0, migration 000028)

The stock issue (phiếu xuất kho, FR-INV-20) is the numbered stock-out document of a repair order: one per order, created with the order as a draft, posted once. The draft is the reservation of its parts (FR-INV-15); posting writes the stock_transactions rows with their cost snapshot.

Table	Column	Data Type	Notes
stock_issues	id, garage_id	uuid	RLS by garage_id; UNIQUE (garage_id, id)
	number	varchar(50)	From the garage’s numbering.stockIssue template (PX-{YYYY}{MM}-{SEQ:4} by default); unique per garage
	repair_order_id	uuid	The repair order, referred to by id only (another module, no foreign key). Unique per garage: one stock issue per order
	repair_order_number	varchar(50)	The order’s number at creation, for display and printing
	status	stock_issue_status enum	draft (default) | posted | cancelled (a cancelled repair order cancels its draft, Release 7.3)
	is_short	boolean	Release 7.3: the stock on hand cannot cover this draft. Recomputed whenever a draft is created, changed, cancelled or posted and whenever a stock movement of one of its parts is written; a flip publishes stock_issue.shortage_changed. created_at defaults to clock_timestamp(), because reservations are served in the order issues were created and now() ties inside one transaction
	posted_at / posted_by	timestamptz / uuid nullable	Set when posted
	created_by / updated_by, created_at / updated_at		Row audit columns
stock_issue_lines	id, garage_id	uuid	RLS by garage_id
	stock_issue_id	uuid	Composite FK into stock_issues, ON DELETE CASCADE
	repair_order_line_id	uuid	The part line of the order it was derived from; unique per issue
	part_id	uuid	Composite FK into parts
	unit_id / entered_quantity	uuid nullable / numeric(14,3)	The unit and amount of the order’s line (Section 5.9)
	quantity	numeric(14,3) > 0	The base-unit amount. While a draft, it is what the draft reserves
	unit_cost	numeric(18,4) nullable	The average-cost snapshot per base unit, taken when the issue is posted; NULL while a draft or when no cost was known
	created_by / updated_by, created_at / updated_at		Row audit columns

Indexes: (garage_id, stock_issue_id) for the lines; (garage_id, part_id) on stock_issue_lines, which the reserved quantity of a part sums over the lines of draft issues.

Rules: a draft is created with the repair order from its part lines that refer to a part (a free-text part line creates no line); posting is idempotent (the unique (garage_id, part_id, ref_id) guard on stock_transactions, Section 5.4) and makes the issue read-only (STOCK_ISSUE_NOT_DRAFT on a second post). There is no cancel status in use yet: the draft would be cancelled by its repair order, and no repair order cancel action exists in Release 7.0.

5.12. Table: part_suppliers (Inventory, Release 7.0, migration 000028)

The usual suppliers of a part with the supplier’s own code, the last cost paid and one default (FR-INV-19).

Column	Data Type	Notes
garage_id / part_id / supplier_id	uuid, composite PK	Composite FKs into parts (ON DELETE CASCADE) and suppliers (ON DELETE RESTRICT)
supplier_sku	varchar(100)	The supplier’s own code for the part; empty when unknown
last_unit_cost	numeric(18,4) nullable	VND per base unit of the last completed receipt line for this supplier; NULL when never paid. Updated when a receipt completes
is_default	boolean	At most one default per part: partial unique index (garage_id, part_id) where is_default
created_by / updated_by, created_at / updated_at		Row audit columns

A default supplier pre-fills a new receipt line that names no supplier (Section 5.10). The list is replaced as a whole by PUT /parts/{partId}/suppliers.

5.13. Tables: stock counts, write-offs, supplier returns, repair order part returns (Inventory, Release 7.1, migration 000029)

The adjustments of Release 7.1 (FR-INV-09 to FR-INV-12). Each document is numbered from its garage template (numbering.stockCount, numbering.writeOff, numbering.supplierReturn; defaults KK-{YYYY}{MM}-{SEQ:4}, XH-{YYYY}{MM}-{SEQ:4} and TN-{YYYY}{MM}-{SEQ:4}, columns on garages; the sequences themselves are in Section 4.5). Every table is RLS by garage_id and carries the row audit columns; each document header has a composite UNIQUE (garage_id, id), and its lines are deleted with the document (ON DELETE CASCADE).

Table	Column	Data Type	Notes
stock_counts	number	varchar(50)	Unique per garage ((garage_id, number))
	name, notes	varchar(255), text	A label for the people counting; notes
	status	stock_count_status enum	draft (default) | completed | cancelled
	completed_at / completed_by, cancelled_at	timestamptz, uuid, timestamptz nullable	Set when the count is completed (by whom) or cancelled
stock_count_lines	stock_count_id, part_id	uuid	Composite FKs into stock_counts (ON DELETE CASCADE) and parts; UNIQUE (stock_count_id, part_id): a part is on a count once
	unit_id	uuid nullable	The packaging unit the count is typed in; NULL for the base unit (Section 5.9)
	entered_counted_quantity	numeric(14,3) ≥ 0 nullable	As typed; NULL while uncounted; 0 is a real count
	counted_quantity	numeric(14,3) ≥ 0 nullable	The base-unit amount, derived; NULL exactly when entered_counted_quantity is NULL
	system_quantity	numeric(14,3), default 0	The stock on hand, base unit, when the line was added and each time it was counted; completion takes the final snapshot
	average_cost	numeric(18,4) nullable	The average cost the difference is valued at: the live average while a draft, the value used at completion afterwards
	counted_at	timestamptz nullable	When the counted quantity was last typed
write_offs	number, status, reason	varchar(50), adjustment_status (completed | cancelled), write_off_reason	Reason: damaged, lost, expired, internal_use, warranty_replacement; created already completed
	notes, cancelled_at	text, timestamptz nullable
write_off_lines	write_off_id, part_id, unit_id	uuid	Composite FKs (ON DELETE CASCADE from the document)
	entered_quantity, quantity	numeric(14,3) > 0	As typed, and the base-unit amount
	unit_cost	numeric(18,4) nullable	The average cost per base unit at the moment of the write-off
supplier_returns	number, status	varchar(50), adjustment_status	Unique per garage; created completed, cancelled by a reversing movement
	supplier_id	uuid	Composite FK into suppliers, ON DELETE RESTRICT
	goods_receipt_id	uuid nullable	Composite FK into goods_receipts, ON DELETE RESTRICT: the completed receipt the parts came from, when known
	credit_note_number	varchar(100), default ''	The supplier’s own credit note number; fixed when the return is created
	notes, cancelled_at	text, timestamptz nullable
supplier_return_lines	supplier_return_id, part_id, unit_id	uuid	Composite FKs (ON DELETE CASCADE from the document)
	entered_quantity, quantity	numeric(14,3) > 0
	unit_cost	bigint ≥ 0, default 0	The credit per entered unit, as typed or defaulted (linked receipt, else average cost); 0 means “not typed” before defaulting
	average_cost	numeric(18,4) nullable	The part’s average cost at the time of the return
repair_order_part_returns	repair_order_id, repair_order_number, stock_issue_id, stock_issue_line_id, repair_order_line_id	uuid, varchar(50)	The order and its issue line the parts come back from. stock_issue_id is a composite FK into stock_issues and stock_issue_line_id a FK into stock_issue_lines (both ON DELETE RESTRICT)
	part_id, unit_id	uuid, uuid nullable
	entered_quantity, quantity	numeric(14,3) > 0	The cumulative total returned from a line may not exceed the quantity issued on it (RETURN_EXCEEDS_ISSUED)
	reason	text	Required, at most 500 characters; the table has no update path
	unit_cost	numeric(18,4) nullable	The line’s cost snapshot the parts are restored at

Movements: a completed count writes one stock_transactions row per differing line (IN for over, OUT for short, ref_type = 'stock_count'), a reversal writes the opposite. A write-off and a return to supplier write OUT rows (write_off, supplier_return), and a repair order part return writes an IN row (repair_order_return). Adjustments never change stock_balance.average_cost. Indexes: a unique (garage_id, number) on stock_counts, write_offs and supplier_returns, and (garage_id, created_at DESC) on each document table; (garage_id, stock_count_id, position) and (garage_id, part_id) on the count lines, (garage_id, write_off_id, position) and (garage_id, part_id) on the write-off lines, (garage_id, supplier_return_id, position), (garage_id, part_id) and (garage_id, supplier_id) on the supplier return tables, and (garage_id, repair_order_id, created_at), (stock_issue_line_id) and (garage_id, part_id) on the part returns.

5.14. Tables: part_barcodes (Catalogue) and import_runs (Data Import) (Release 7.2, migration 000030)

Barcodes (FR-INV-13) belong to the Catalogue; the import history (FR-INV-14) to the Data Import module, which owns no other table. Both are garage-scoped with the usual row-level security and have the append-only audit trigger (set_created_audit, NFR-MT-07).

Table	Column	Data Type	Notes
part_barcodes	garage_id, part_id	uuid	Composite FK into parts (ON DELETE CASCADE)
	unit_id	uuid nullable	NULL is the base unit; otherwise the composite FK (garage_id, part_id, unit_id) into part_units (ON DELETE RESTRICT), so a code can only name a packaging unit of its own part, and a packaging row with a barcode cannot be deleted
	barcode	varchar(64)	Stored as typed after trimming. UNIQUE (garage_id, lower(barcode)): unique in the garage without regard to case. Soft-deleting a part deletes its rows in the same transaction, which frees the codes
	created_by, created_at	uuid, timestamptz
import_runs	garage_id, id	uuid
	import_type	varchar(32)	parts | goods_receipts | stock_counts
	file_sha256	char(64)	SHA-256 of the confirmed file. UNIQUE (garage_id, import_type, file_sha256) makes a confirmed file idempotent
	file_name, row_count	varchar(255), integer	For the history
	document_id	uuid nullable	The completed opening-stock receipt, the draft receipt or the draft count the import made
	created_by, created_at	uuid, timestamptz

A confirm takes a transaction-scoped advisory lock on (garage, type, hash) first, so two confirmations of one file run one after the other and the second finds the first one’s row. The import writes parts, packaging units and barcodes in bulk statements and its documents through the Inventory use cases, all in the request’s transaction.

5.15. Tables: jobs, job_parts (Catalogue, Release 7.3, migration 000031)

The job catalogue (FR-CAT-05) belongs to the Catalogue module. Both tables are garage-scoped with the usual Row-Level Security policy and row audit trigger.

Table	Column	Data Type	Notes
jobs	id, garage_id	uuid	UNIQUE (garage_id, id)
	name	varchar(255)	UNIQUE (garage_id, lower(name)) WHERE deleted_at IS NULL: unique without regard to case; a deleted job frees its name
	category	varchar(100)	A free label that groups the picker (not the part category tree); indexed (garage_id, lower(category))
	labor_price	bigint ≥ 0	Whole VND
	duration_minutes	integer ≥ 0
	is_active	boolean	Deactivated when deleted while a quote or order line came from it (FR-CAT-07)
	created_by / updated_by, created_at / updated_at, deleted_at
job_parts	job_id	uuid	Composite FK into jobs, ON DELETE CASCADE; the rows of a soft-deleted job are deleted with it
	part_id	uuid	Composite FK into parts, ON DELETE RESTRICT; UNIQUE (job_id, part_id): a part once per job
	unit_id	uuid nullable	NULL is the base unit; otherwise composite FK (garage_id, part_id, unit_id) into part_units, ON DELETE RESTRICT, which is what refuses removing a packaging unit a job uses (PACKAGING_UNIT_USED_BY_JOB; Postgres reports restrict_violation, SQLSTATE 23001)
	quantity	numeric(14,3) > 0	Typed in unit_id (the base unit when NULL)
	non_billable	boolean	A hidden consumable (FR-CAT-08)
	position	integer	Display order

A part that a live job suggests is deactivated, not deleted, and its base unit is locked (the check is a query on job_parts joined to live jobs).

6. Billing & Settlement, License (Release 1.4, migration 000018)

Owned by the Billing & Settlement module (HLD module #8). No separate receivables table: FR-REC-01’s “list of settlements not yet fully collected” is settlements itself filtered by status (ListSettlements?outstandingOnly=true) — a dedicated model arrives with Release 13.0’s reminders and reconciliation (FR-REC-02/03/04), which need their own persisted state the listing alone cannot give them.

6.1. Table: settlements

Column	Data Type	Notes
id	uuid PK
garage_id / repair_order_id	uuid FK	RLS by garage_id; UNIQUE (repair_order_id) — a repair order settles at most once (FR-BIL-01); (garage_id, repair_order_id) FK into repair_orders
customer_name / vehicle_license_plate	varchar	Denormalized from the repair order at creation, the same pattern as repair_orders (Section 4.3)
number	varchar(50)	Rendered from settlement_number_template (FR-TEN-05); UNIQUE (garage_id, number)
total_amount	bigint	Copied from the repair order’s total at creation
paid_amount	bigint	Running total of the payments that are not voided (FR-BIL-03, FR-BIL-06), adjusted atomically in place the same way stock_balance.quantity_on_hand is (Section 5.2): + amount when a payment is recorded, − amount when one is voided
issuer	jsonb	The garage’s legal identity snapshot (Section 2.2) taken at creation; printed in the receipt’s header (FR-TEN-06)
billing_party	jsonb	Who the settlement is billed to (FR-CUS-07): the repair order’s party, an ad-hoc repair order’s customer default, or the profile chosen; changed with UpdateSettlement. Printed on the receipt and defaults the voucher’s payer. Indexed like quotes.billing_party
invoice_requested	boolean	The customer asked for a VAT invoice; true only while billing_party has a legal name, tax ID and address (otherwise INVOICE_DATA_INCOMPLETE)
status	settlement_status enum	pending | partially_paid | paid, derived from paid_amount against total_amount and re-derived after a payment or a void — a zero-total settlement is immediately paid
created_by / updated_by, created_at / updated_at		A settlement has one payer, the customer; Release 5.0 removes the insurer split (customer_amount, insurer_amount, insurer_partner_*, *_paid_amount), which returns with insurance in Phase 17

6.2. Table: settlement_payments (the payment voucher, never deleted)

Every payment is a payment voucher (phiếu thu, FR-BIL-05). A mistaken one is voided, not deleted (FR-BIL-06).

Column	Data Type	Notes
id	uuid PK
garage_id / settlement_id	uuid FK	RLS by garage_id; (garage_id, settlement_id) FK into settlements, ON DELETE CASCADE
amount	bigint
method	payment_method enum	cash | bank_transfer | online — online is recorded manually (staff sees a bank app or gateway dashboard confirmation and records it); automatic confirmation via a payment gateway webhook is Release 16.0 (FR-PAY-02)
note	text nullable
voucher_number	varchar(50)	Rendered from the garage’s payment_voucher_number_template, with the payment_voucher document sequence (Section 4.5); UNIQUE (garage_id, voucher_number)
payer_name / payer_address	varchar(255) / text nullable	Who paid; default to the customer’s name and no address
reason	text	The reason printed on the voucher; defaults to “Thanh toán quyết toán {settlement number}”
collector_name	varchar(255)	The staff member who collected the money, copied from their profile when the voucher is issued; the user id is created_by
issuer	jsonb	The garage’s legal identity snapshot (Section 2.2) taken when the voucher is issued; its PDF prints this, not the current profile
void_reason / voided_at	text / timestamptz, both nullable	Set together when the voucher is voided (CHECK ((voided_at IS NULL) = (void_reason IS NULL))); the voucher keeps its number. The user who voided it is updated_by
created_by / updated_by, created_at / updated_at		Audit columns; updated_* change only when the voucher is voided

Voiding is one transaction: the voucher gets voided_at and void_reason, the settlement’s paid_amount drops by its amount, the status is re-derived and settlement.payment_voided is written to audit_log with the before and after amounts. A voucher is voided once.

6.3. Tables: plans, subscriptions, entitlements (License — user level, no RLS)

Table	Columns	Notes
plans	id, code (unique, immutable), name, description, price bigint, currency, billing_cycle billing_cycle (monthly | yearly), max_garages, max_staff, max_transactions_per_day, features plan_feature[], is_public, is_active, sort_order	Seed: trial 1/5/50 (not selectable), starter 1/5/50, Pro 2/20/100, chain 10/100/1000 (garages/staff/transactions per day)
subscriptions	id, user_id (unique — one per owner), plan_id, status subscription_status, trial_end_at, current_period_start, current_period_end, grace_period_end, auto_renew, reminder_period_end, period_paid_amount bigint (the price of the plan for the current period, credit included, VND; 0 for a trial period; a renewal before the end adds to it), credit_balance bigint (account credit, VND, never negative)	Covers every garage of the owner. A subscription is created once, when the owner’s e-mail is verified, so the trial happens once; the trial plan is not selectable (FR-LIC-02)
entitlements	user_id, feature plan_feature, enabled (PK user_id, feature)	Derived from the subscription; source of the Redis entitlement cache

owner_usage(owner uuid) (SECURITY DEFINER) returns the owner’s active garages and staff (active non-owner members plus live pending invitations, distinct users) across all garages; RLS would otherwise hide the owner’s other garages from a single-garage transaction. Daily transaction counts are Redis counters per owner and day.

6.4. Platform tables (no RLS)

Table	Columns	Notes
outbox_events	id, event_type, garage_id, user_id, payload jsonb, status outbox_status (pending | published | failed), attempts, last_error, created_at, published_at	Written in the business transaction; relayed to Kafka (Event Design)
processed_events	consumer, event_id (PK), processed_at	Idempotent consumers
audit_log	id, actor_user_id, garage_id, action, entity_type, entity_id, before jsonb, after jsonb, ip, created_at	Append-only: vietgara_app has INSERT and SELECT only

6.5. Table: notification_log (Notification, Release 1.5, migration 000019, user level, no RLS)

Owned by the Notification module (HLD module #14). Append-only record of what was actually sent, for visibility/debugging only — not a dedup mechanism: the Outbox Pattern’s processed_events already gives the triggering event exactly-once delivery (Event Design Section 6), so a redelivered event never reaches the mailer twice.

Column	Data Type	Notes
id	uuid PK
user_id	uuid nullable	No FK (same application-level-only choice as settlements.insurer_partner_id); indexed where set
channel	notification_channel enum	email is the only Phase 1 value; push arrives with FR-NOT-03 (Phase 11), SMS/Zalo ZNS with FR-NOT-01/02 (Phase 16)
template	varchar(100)	The rendered template’s name (registration_confirmation, subscription_expiring_soon)
recipient	varchar(255)
status	notification_status enum	sent | failed — only sent is actually written in Release 1.5: a send failure propagates instead of being logged, so the triggering event redelivers and the e-mail is retried, rather than persisting a row that would just roll back with the rest of its transaction
error	text nullable
created_at	timestamptz

Append-only: vietgara_app has INSERT and SELECT only (the same restriction as audit_log, Section 6.4).

6.6. Table: idempotency_keys (platform, migration 000021, RLS)

The answers to requests sent with an Idempotency-Key (API Specification Section 1.3): settlement creation and payment recording. A row is written in the same transaction as the change the request made, so the two commit or roll back together. A failed request therefore leaves no row and can be retried. A concurrent duplicate waits on the primary key until the first request commits, then reads its answer.

Column	Data Type	Notes
garage_id	uuid FK → garages.id	RLS; part of the PK
user_id	uuid FK → users.id	The caller; part of the PK, so two staff members’ keys never collide
idempotency_key	varchar(255)	The client’s key; part of the PK
method	text	The full gRPC method; the same key used on another endpoint is refused
request_hash	bytea	SHA-256 of the request message; the same key with another body is refused
response_type, response	text, bytea nullable	The answer’s message type and protobuf encoding, replayed as is
http_status	integer nullable	The status the endpoint chose (201 for a settlement); null for the default
created_at	timestamptz	Index; keys older than 24 hours are deleted hourly by purge_idempotency_keys()

purge_idempotency_keys(older_than) is SECURITY DEFINER and does nothing but delete keys older than older_than across every garage, which the garage-scoped policy forbids the application role. This is the same pattern owner_usage() uses (Section 4.1).

6.7. Table: files (File module, migration 000023, RLS)

The file registry: every uploaded file is a row here, owned by a garage or by a user, and business rows keep the object key (for example garages.logo_key), never a URL.

Column	Data Type	Notes
id	uuid PK
garage_id / user_id	uuid, nullable	Exactly one is set (CHECK ((garage_id IS NOT NULL) <> (user_id IS NOT NULL))): a garage’s file or a user’s own file
kind	file_kind	logo, payment_evidence, platform_qr and (Release 6.0) part_image; every later kind (repair photo, claim document, export) is private
entity_type / entity_id	varchar(50) / uuid	What the file belongs to, e.g. garage and the garage’s id
key	varchar(500) unique	Object key, also the storage path: garages/{garageId}/{kind}/{entityId}/{uuid}.{ext}, users/{userId}/{kind}/{uuid}.{ext}
content_type / size / sha256	varchar / bigint / char(64)	Hex SHA-256 of the content
is_active	boolean	A file a business row refers to is deactivated, not deleted (FR-CAT-07): a replaced logo is retired this way
audit columns, deleted_at		An unreferenced file is soft-deleted and its object removed

Rules per kind live in code (internal/file/domain): public or private, accepted content types, size limit, the owning entity and the permission it needs. The logo is public (PNG, JPEG or WebP, 1 MB). part_image (Release 6.0, FR-CAT-04) is public too, so that lists and pickers can show thumbnails without a signed URL: PNG, JPEG or WebP, owned by the garage with entity item, needing inventory.write; it is resized on upload and attached to a part through item_images (Section 5.8). payment_evidence is private and owned by the owner user (PNG, JPEG, WebP or PDF, 5 MB); platform_qr is private, owned by the admin who uploaded it (PNG, JPEG or WebP, 1 MB). Private objects are served only through short-lived signed URLs (API Specification Section 6). A file of a user or of the platform has no garage, so the garage policy hides it from a garage-scoped read; file_object_key(file_id) (SECURITY DEFINER, the same pattern as owner_usage()) returns the object key and content type of a live file by its id, and the module that owns the file decides who may ask for it.

6.8. Tables: plan_payment_requests, credit_ledger, platform_payment_settings (License, Release 5.2, migration 000026, user level, no RLS)

A plan change or a renewal is paid by bank transfer and approved by a platform admin (FR-LIC-07, FR-LIC-08); the plan changes in the approval transaction.

Table	Columns	Notes
plan_payment_requests	id, user_id (the owner), subscription_id, kind plan_payment_kind (change | renew), plan_id (the plan to pay for), status plan_payment_status (awaiting_payment | submitted | approved | rejected | cancelled), reference varchar(20) unique (e.g. VG-7K2M9Q), quoted_amount, credit_applied, amount_due, credit_surplus, unused_credit (all VND; unused_credit is the part of the available credit that is the unused value of the period, earned when the plan changes), period_days, unused_days, payer_name, payer_bank, payer_account, transferred_amount, bank_transaction_ref, paid_at, evidence_file_id (a files row of kind payment_evidence), instructions jsonb (the platform payment details shown when the request started), amount_mismatch boolean, review_note, rejection_reason, reviewed_by, reviewed_at, submitted_at, audit columns	At most one open row (awaiting_payment or submitted) per owner (partial unique index); a new request replaces an awaiting_payment one. The payer, transfer and evidence columns are filled when the request is submitted. The quote (quoted_amount, credit_applied, amount_due, credit_surplus, unused_credit, period_days, unused_days) is computed when the request starts and honoured at approval. amount_mismatch is true when transferred_amount differs from amount_due. A row is never deleted
credit_ledger	id, user_id, kind credit_entry_kind (earned | applied | adjustment), amount bigint (signed VND), balance_after, plan_payment_request_id (nullable), note, created_by, created_at	Append-only; subscriptions.credit_balance is the running total. A downgrade surplus is earned; the credit used by a payment is applied; a manual correction by an admin is adjustment with a note
platform_payment_settings	id smallint PK (always 1), bank_name, account_number, account_holder, branch, instructions text, qr_file_id (a files row of kind platform_qr), last_review_reminder_at (when the admins were last reminded of waiting requests, so that one instance sends it once a day), updated_by, updated_at	One row. Every change is written to the audit log with the values before and after

Evidence images and the QR code are private files of the registry (Section 6.7), served through short-lived signed URLs: the evidence to the owner who submitted it and to platform admins, the QR code to every signed-in owner through the settings endpoint.

7. Row-Level Security Policies (Release 1.1)

Table	Read (USING)	Write (WITH CHECK)
garages	current garage, or owned by / member of / invited to the current user	current garage; insert also requires the current user as owner
garage_staff	current garage, or the current user’s memberships	current garage
staff_groups	current garage, the owner’s garages, or groups the user belongs to	current garage
staff_group_members	current garage, or the user’s own memberships	current garage
roles	the user’s roles, the current garage owner’s roles, roles used by visible groups	the user’s own roles
role_permissions	permissions of visible roles	the user’s own roles
staff_invitations	current garage, or invitations to the current user	insert/delete: current garage; update: current garage or invitee
customers, vehicles, suppliers	current garage	current garage
files	current garage, or the current user’s own files	the same
document_sequences, quotes, quote_lines, repair_orders, repair_order_lines	current garage	current garage
parts, stock_transactions, stock_balance, goods_receipts, stock_issues, part_suppliers	current garage	current garage
settlements, settlement_payments	current garage	current garage
idempotency_keys	current garage	current garage

8. Entity-Relationship Overview

Planned entities (bookings, platform roles, claims and others) are described in Section 11 and are not drawn here until they are implemented.

erDiagram
  users ||--o{ garages : owns
  users ||--o| subscriptions : "has (owner)"
  plans ||--o{ subscriptions : "chosen by"
  subscriptions ||--o{ plan_payment_requests : "paid by (5.2)"
  users ||--o{ credit_ledger : "credited (5.2)"
  users ||--o{ entitlements : "entitled to"
  users ||--o{ roles : defines
  roles ||--o{ role_permissions : grants
  garages ||--o{ garage_staff : employs
  users ||--o{ garage_staff : "works as"
  garages ||--o{ staff_groups : has
  roles ||--o{ staff_groups : "assigned to"
  staff_groups ||--o{ staff_group_members : contains
  garage_staff ||--o{ staff_group_members : "belongs to"
  garages ||--o{ staff_invitations : sends
  users ||--o{ staff_invitations : receives
  garages ||--o{ customers : serves
  customers ||--o{ vehicles : owns
  garages ||--o{ suppliers : "buys from"
  garages ||--o{ quotes : "issues (1.2)"
  quotes ||--o{ quote_lines : has
  quotes ||--o| repair_orders : "converted to (1.2)"
  repair_orders ||--o{ repair_order_lines : has
  repair_orders ||--o| settlements : "settled by (1.4)"
  settlements ||--o{ settlement_payments : "paid by (1.4)"
  garages ||--o{ parts : "catalogues (1.3, Catalogue)"
  parts ||--o{ stock_transactions : moves
  parts ||--o| stock_balance : "on hand (1.3)"
  repair_orders ||--o| stock_issues : "reserves and posts (7.0)"
  stock_issues ||--o{ stock_issue_lines : has
  stock_issue_lines }o--|| parts : issues
  garages ||--o{ goods_receipts : "receives (7.0)"
  goods_receipts ||--o{ goods_receipt_lines : has
  goods_receipt_lines }o--|| parts : stocks
  suppliers ||--o{ goods_receipt_lines : "supplies (7.0)"
  parts ||--o{ part_suppliers : "usual suppliers (7.0)"
  suppliers ||--o{ part_suppliers : "is a usual supplier of"
  garages ||--o{ stock_counts : "counts (7.1)"
  stock_counts ||--o{ stock_count_lines : has
  stock_count_lines }o--|| parts : counts
  garages ||--o{ write_offs : "writes off (7.1)"
  write_offs ||--o{ write_off_lines : has
  write_off_lines }o--|| parts : reduces
  suppliers ||--o{ supplier_returns : "receives returns (7.1)"
  supplier_returns ||--o{ supplier_return_lines : has
  supplier_return_lines }o--|| parts : reduces
  stock_issue_lines ||--o{ repair_order_part_returns : "returned by (7.1)"
  garages ||--o{ units : "defines (6.0, Catalogue)"
  units ||--o{ parts : "base unit of (6.0)"
  garages ||--o{ part_categories : "organises (6.0, Catalogue)"
  part_categories ||--o{ parts : "classifies (6.0)"
  part_categories ||--o{ part_categories : "parent of"
  units ||--o{ part_units : "packages (6.0)"

part_units and part_images (Catalogue) refer to parts and units with composite foreign keys, as described in Section 5.

9. Indexing Strategy

• Index (garage_id) on every business table — this filter appears in nearly every query due to RLS.
• Unique (garage_id, license_plate_key) on live vehicles — fast lookup by plate regardless of separators.
• Unique lower(email) on users; unique (garage_id, user_id) on garage_staff; partial unique index on pending invitations per (garage_id, user_id).
• Partial index on pending outbox events (status = ‘pending’) for the relay.
• Unique (garage_id, number) on live quotes and on live repair_orders; unique (garage_id, public_token_hash) where set on quotes.
• Index (garage_id, status, created_at) on quotes/repair_orders — supports status-filtered, time-sorted listings.
• Index (garage_id, customer_id, created_at) / (garage_id, vehicle_id, created_at) on quotes — customer/vehicle filtering and vehicle history (FR-CUS-03).
• Unique index (garage_id, part_id) on stock_balance.
• GIN index on repair_order_lines.assigned_technicians — supports ListRepairOrders?technicianId=.
• Unique partial index (garage_id, sku) where live on parts; unique partial index (garage_id, part_id, ref_id) where ref_type = ‘stock_issue’ on stock_transactions — the idempotency guard for a repeated stock issue (Section 5.4). Release 7.0: the indexes of the goods receipt, stock issue and part supplier tables are listed in Sections 5.10 to 5.12. Catalogue (Release 6.0): unique (garage_id, lower(name)) on live units; unique (garage_id, coalesce(parent_id), lower(name)) on live part_categories; unique (part_id, unit_id) on part_units; (garage_id, part_id, position) on part_images; (garage_id, category_id) and a trigram index on brand on parts.
• Full-text search index (pg_trgm) on customers.full_name and customers.phone, and on parts.sku/parts.name, for fuzzy search.
• Unique (repair_order_id) and (garage_id, number) on settlements; index (garage_id, status, created_at) — supports status-filtered listings and FR-REC-01’s outstandingOnly receivables view. Index (garage_id, settlement_id, created_at) on settlement_payments.
• Partial index (user_id, created_at) where user_id is set, on notification_log.
• Unique partial (user_id) where status is awaiting_payment or submitted on plan_payment_requests (one open request per owner); unique (reference); (status, submitted_at) where status is submitted for the review queue; (user_id, submitted_at DESC). Index (user_id, created_at DESC) on credit_ledger.
• Unique partial (customer_id) where status is requested or linked on customer_user_links; (garage_id, customer_id, created_at DESC) and (user_id, status). Unique partial (customer_id) for the personal profile and for the default profile on customer_tax_profiles. Expression indexes on billing_party->>'tax_profile_id' for quotes and settlements.

10. Enum Types

Enums added by later releases are listed in Section 11 (for example claim_status arrives in Phase 17). Release 5.0 removes payer_type and partner_type.

Enum	Values
user_auth_provider	email, google
user_status	active, suspended
user_locale	vi, en
garage_status	active, inactive
staff_status	active, inactive
invitation_status	pending, accepted, declined, revoked, expired
default_role	manager, accountant, service_advisor, technician, inventory_staff
permission	garage.write, staff.read, staff.write, customer.read, customer.write, supplier.read, supplier.write, subscription.read, quote.read, quote.write, repair_order.read, repair_order.write, inventory.read, inventory.write, inventory.cost.read (Release 7.0), inventory.adjust, inventory.count (Release 7.1), catalogue.read, catalogue.write (Release 6.0), billing.read, billing.write, report.read, and customer.correct (Release 5.1, FR-CUS-09; a role that holds it also holds customer.read)
user_name_source	google, user
customer_user_link_status	requested, linked, declined, unlinked
customer_user_link_channel	otp, app, auto_email
customer_tax_profile_kind	personal, company
file_kind	logo, payment_evidence, platform_qr, part_image (Release 6.0)
billing_cycle	monthly, yearly
plan_feature	customers, quotes, repair_orders, inventory, billing, documents, reports
subscription_status	trial, active, past_due, suspended, cancelled
plan_payment_kind	change, renew
plan_payment_status	awaiting_payment, submitted, approved, rejected, cancelled
credit_entry_kind	earned, applied, adjustment
outbox_status	pending, published, failed
quote_approval_mode	link, manual (FR-QUO-02; also garages.default_quote_approval_mode, FR-TEN-05)
quote_status	draft, sent, approved, rejected, converted
quote_item_type	labor, part (also repair_order_lines.item_type)
quote_line_decision	pending, approved, rejected
repair_order_status	open, in_progress, waiting_on_parts, completed, closed
repair_order_line_status	not_started, in_progress, completed (FR-REP-03)
stock_transaction_type	in, out
stock_transaction_ref_type	purchase, repair_order (both before Release 7.0), goods_receipt, stock_issue (Release 7.0), stock_count, write_off, supplier_return, repair_order_return (Release 7.1)
goods_receipt_status	draft, completed, cancelled (Release 7.0)
stock_issue_status	draft, posted, cancelled (Release 7.0)
stock_issue_mode	on_close, on_start (Release 7.0; garages.stock_issue_mode)
stock_count_status	draft, completed, cancelled (Release 7.1)
adjustment_status	completed, cancelled (Release 7.1; write-offs and supplier returns)
write_off_reason	damaged, lost, expired, internal_use, warranty_replacement (Release 7.1)
settlement_status	pending, partially_paid, paid

| payment_method | cash, bank_transfer, online | | notification_channel | email | | notification_status | sent, failed |

Adding a value is a forward migration (ALTER TYPE … ADD VALUE); the proto enums mirror these values with a <ENUM>_UNSPECIFIED = 0 first entry.

11. Planned Schema by Release (Phases 3–17)

Conventions of Section 1.1 apply: garage_id with RLS on garage data, audit columns, deleted_at for soft delete, and an is_active flag wherever FR-CAT-07 applies. Migrations are edited in place until the migration freeze (Section 1.1).

11.1. Release 3.0: users

Change	Detail
accounts → users	Migration 000001; every account_id / *_account_id column becomes user_id / *_user_id (garages’ owner, subscriptions, platform_admins, notification_log, outbox_events, audit log actor)
Session variable	app.current_account_id → app.current_user_id, with its helper functions, RLS policies, indexes and comments. No column is named user (reserved word)

11.2. Release 5.0: lifecycle, legal profile, vouchers, files

Described where it lives: lifecycle flags (1.1), legal profile (2.2), suppliers (3.3), snapshots (1.1, 4.1, 4.3, 6.1, 6.2), vouchers (4.5, 6.2), row audit columns (1.1), numeric quantities and cost (4.2, 5), files (6.7) and users.full_name_source (2.1). What it leaves for later releases:

Change	Detail
is_active	Added to each later entity that FR-CAT-07 applies to (units in 6.0, tax profiles in 5.1, printers, templates, …)
files.kind	New kinds (repair_photo, claim_document, …) are added to file_kind with their rules; they are private (part_image, added in Release 6.0, is the second public kind after the logo). Business rows reference the file by id; file_id columns arrive with the first such kind
settlement_payments.payer_tax_profile_snapshot	Not needed: the voucher’s payer name and address default from the settlement’s billing party (Section 6.1); no column is added
document_sequences (Section 4.5)	Kinds goods_receipt and stock_issue (Release 7.0, Section 5.10 and 5.11), stock_count, write_off and supplier_return (Release 7.1, Section 5.13). Later kinds: payment_out_voucher (12.0), purchase_order (12.0), claim (16.0)

11.3. Release 5.1 and Release 5.2

Both releases are described where they live. Release 5.1: customer_user_links (3.4), customer_tax_profiles (3.5), the vehicle edit lock (3.2, computed, no column), billing_party on quotes, repair_orders and settlements (4.1, 4.3, 6.1) and settlements.invoice_requested (6.1). Release 5.2: plan_payment_requests, credit_ledger and platform_payment_settings (6.8), subscriptions.period_paid_amount and credit_balance (6.3) and the file kinds payment_evidence and platform_qr (6.7). Nothing further is planned for either release: the voucher payer defaults from the settlement’s billing party, so settlement_payments has no payer tax profile snapshot column, and Phase 16 reuses the payment request, ledger and period snapshot for the gateway charge.

11.4. Release 6.0: units and catalogue

The Catalogue module arrives in this release. Its tables, the ownership split with Inventory and the quantity rules are in Section 5 (migration 000017 is edited in place); this table lists only what changes in existing tables.

Table / column	Detail
items	unit (text) becomes base_unit_id uuid NOT NULL (composite FK into units); adds sale_price bigint, category_id, brand varchar(100), is_active (Section 5.2)
stock_transactions, quote_lines, repair_order_lines	Nullable unit_id (composite FK into units) and entered_quantity numeric(14,3); quantity stays the base-unit amount, which keeps the Phase 2 apps unchanged. Free-form labor lines keep both null
units, item_units, part_categories, item_images	New Catalogue tables (Sections 5.5 to 5.8)
files.kind	Adds part_image (Section 6.7)

11.5. Release 6.1: MFA

user_mfa(user_id, secret_encrypted, enabled_at, last_used_step) and user_mfa_recovery_codes(user_id, code_hash, used_at), both with the row audit columns. A user_mfa row exists from the start of the enrolment and enabled_at is set when the first code is confirmed; last_used_step makes a TOTP code work once. MFA is optional, so there is no policy column. The challenge tokens and the wrong-code counters live in Redis with the sessions.

11.6. Phase 7: inventory operations (Releases 7.1 and 7.2)

Releases 7.0, 7.1 and 7.2 are in place and described in Sections 5.3 to 5.14: the goods receipt, part supplier and stock issue tables (7.0); stock_counts, stock_count_lines, write_offs, write_off_lines, supplier_returns, supplier_return_lines and repair_order_part_returns (7.1); the ref_type values stock_count, write_off, supplier_return and repair_order_return (7.1); and the permissions inventory.adjust and inventory.count (7.1); part_barcodes and import_runs (7.2).

Table / column	Detail
part_barcodes	Release 7.2: part_id, barcode unique per garage (several per part, FR-INV-13)
Excel import	Release 7.2 (FR-INV-14) writes through the same documents as the screens: opening stock is a completed goods receipt or count, so no table is added

11.7. Phase 8: platform staff and support access

Table	Detail
platform_roles, platform_role_permissions	Platform scope, no garage_id, no RLS; permissions from the platform catalogue (plan, subscription, stats, garage_directory, user, platform_staff, support, audit)
platform_staff_groups, platform_staff_group_members	A group has roles; members are users
platform_staff_invitations	Same shape as staff_invitations without a garage
platform_admins	Kept for the super admins bootstrapped from PLATFORM_ADMIN_EMAILS
support_access_grants	id, garage_id, staff_user_id, reason, scope (read_only), requested_at, approved_by, approved_at, expires_at, revoked_at, granted_by_platform_admin boolean. A session under a grant runs in a read-only transaction
support_access_events	Append-only: grant, entity type and id read, time; shown to the owner. Also written to audit_log

11.8. Release 7.3 (done) and 10.0 onward: jobs, reservation, printing

Table / column	Detail
jobs, job_parts	Section 5.15 (Release 7.3): jobs(garage_id, name, category varchar, labor_price, duration_minutes, is_active); job_parts(job_id, part_id, quantity, unit_id, non_billable boolean). The planned category_id became a free category label, and billable became non_billable
quote_lines, repair_order_lines	job_id and non_billable (Sections 4.2 and 4.4)
repair_orders, stock_issues	waiting_on_parts and the cancelled status; is_short (Sections 4.3 and 5.11)
documents_printed	garage_id, document_type, document_id, template_code, template_version, printed_by, printed_at (Release 10.0)
Release 14.0 and 14.1	printers(garage_id, name, protocol, address, paper_size, document_types, is_active); print_agents(garage_id, key_hash, last_seen_at, revoked_at)
Release 14.2	print_templates(garage_id, document_type, paper_size, name, is_default) and print_template_versions(template_id, version, layout jsonb, published_at), immutable once published

11.9. Phase 12: booking

Table	Detail
booking_settings	garage_id PK, working_hours jsonb (weekday to ranges), slot_minutes, capacity_per_slot, min_lead_minutes, cancel_window_minutes, auto_confirm, slug (unique, public page), enabled
blocked_periods	garage_id, starts_at, ends_at, reason (holidays, closures)
bookings	id, garage_id, customer_id, vehicle_id nullable, contact_email, contact_phone, starts_at, ends_at (UTC), status, job_ids uuid[], description, source (app, public, staff), hold_expires_at, bay_id and technician_id (Release 12.3), converted_to_type / converted_to_id, timestamps. Capacity is checked inside the creating transaction with a lock on (garage_id, slot). Photos are files rows of kind booking_photo
bays (Release 12.3)	garage_id, name, type, is_active; jobs may require a bay type

Slots are never stored; they are computed on read.

11.10. Phase 14 and 15: receivables, payables, transfers

receivables (per settlement: due date, reminder state, last reminder), receivable_reminders (each send, channel, outcome), payment_out_vouchers (number, partner, amount, method, reason, void flags), purchase_orders and lines, supplier_payables (per completed goods receipt, allocations by payment-out vouchers), and data_requests (customer, type, status, outcome, processed_by). Release 15.3 adds stock_transfers and lines, with a shipped step at the sender and a received step at the receiver, linked by a transfer id.

11.11. Phase 17: insurance

Table	Detail
insurers	id, garage_id, name, tax_code, hotline, claim_email, portal_url, claim_template, is_active, audit columns. A garage-owned record of the Insurance module; not a customer, not a supplier
insurance_claims	id, garage_id, repair_order_id, vehicle_id, insurer_id, policy_number, incident_at, incident_place, description, status, notified_at, notify_deadline_at, assessed_at, agreed_quote_snapshot jsonb, deductible_amount, insurer_amount, approved_at, paid_at
claim_events	Append-only status and note log: actor, from/to, note, at
claim_documents	claim_id, checklist_item (registration, licence, id card, policy, accident report, assessment, damage report, repair invoice, voucher), file_id, uploaded_by (garage staff or the vehicle owner), uploaded_at
settlements	Regain the insurer split as deductible_amount and insurer_amount tied to a claim, and settlement_payments.payer_type (customer, insurer)
Enums	new claim_status (partner_type is not brought back)

11.12. Release 17.2: supplier portal

supplier_user_links(id, garage_id, supplier_id, user_id, status, requested_by, requested_at, confirmed_at): the same states as customer links but a separate table, consent text and endpoints. A supplier user reads only the purchase orders addressed to the supplier it is linked to. purchase_order_attachments stores delivery notes and invoices as private files of the registry.

11.13. Release 10.2: contact requests

contact_requests(id, name, garage_name, phone, email, message, locale, consent_at, source_ip_hash, user_agent, status, created_at): a platform table with no RLS and no garage or user key, written by the public contact endpoint (FR-WEB-04). status is new or handled; support updates it by hand until a screen exists. source_ip_hash is a salted hash, never the address, so the rate limit can count without storing personal network data. Rows older than the retention set in the Compliance Checklist are purged. vietgara_app has INSERT and SELECT only on it, as on audit_log; the status update uses a separate statement-level grant.