Tribe Issuing App
Database Design and Data Model
Backend: Supabase (PostgreSQL) • Prepared by Wanile Technologies • The single source of truth for the whole build. Read by
the founder, the developer, and any AI coding tool.
How to read this. This is the foundation everything else is built on. Sections 1 to 10 explain the design and the
reasoning. Section 11 (in the .md companion file) holds the complete, runnable SQL. If you read one thing, read the
eleven principles below: they are what make this a foundation a bank can trust.
1. The eleven design principles (with reasoning)
These rules apply to the whole database. Each has a one-line reason so anyone, technical or not, sees why it is there.
1. A tenant_id on every table, from day one. A tenant is one company or bank. The product is white-label and re-sold to
many institutions, so every row records which tenant owns it. This is the single most important decision in the project. It
cannot be added later without a rebuild, and it is the technical heart of the client's resell business.
2. UUID primary keys (random IDs, not 1, 2, 3). Every row gets a random unguessable ID. Counting numbers leak how
much data you have and are guessable in a browser. UUIDs are safe to expose, never collide, and are the Supabase
standard.
3. created_at and updated_at on every table. Every row records when it was made and last changed, automatically.
Banks need history, sorting by date is constant, and these are the first columns you check when something breaks.
4. Soft delete, never hard delete, on business data. Important rows are never truly erased. They are marked with a
deleted_at timestamp and hidden from view. A finance system must never lose a record.
5. Status values locked in the database (enums). Every status field is a fixed list defined inside the database, which
rejects any value not on the list. A bug can never write a nonsense status. The allowed states live in one place.
6. Foreign keys with real integrity. Every link between tables is a true database link. The database refuses to create an
expense for a transaction that does not exist. Data can never silently point at nothing.
7. Indexes on everything we filter by. An index is a shortcut that makes a search fast. We add one on tenant_id always,
on every link between tables, and on every column filtered often (status, dates, user). Without these the app slows to a crawl
as data grows. This is the scalability rule.
8. Money stored exactly, never as a rough decimal. Amounts use numeric(14,2), which is exact, not a floating-point
approximation. Every amount sits next to its currency code. Money bugs are unforgivable in a finance product.
9. Multi-currency and UTC time from the start. The screenshots already show AED and GBP, so every money field
carries a 3-letter currency code. Every time is stored in UTC because users span the UAE, the UK and Pakistan. The app
shows local time; the database stores one true time.
10. Security lives in the database, not in hidden menus. Who can see what is decided by the database itself (Row Level
Security), not by hiding a button. Even if someone bypasses the app, the database blocks them. Doing this properly is what
makes us look like a real engineering team.
11. One clear naming style everywhere. All tables and columns use lower_case_with_underscores; tables are plural; links
are named something_id. This consistency lets the developer and any AI tool always guess the right name, preventing a
whole class of mistakes.
Tribe Issuing App | Database Design and Data Model Page 1
2. The data model at a glance
There are 13 tables in four groups. Organisation: tenants, profiles (users), departments, cost centres, expense categories.
Money: cards, transactions. Workflow (the heart of the app): expenses, receipts, approvals, ai reviews. Supporting:
documents, audit logs.
The main flow in one sentence: a tenant has users and cards; a card produces transactions; each transaction becomes
an expense when an employee submits it; the expense collects a receipt, moves through approvals (its timeline), and gets a
simulated AI review.
TENANTS
every table carries tenant_id
PROFILES DEPARTMENTS COST CENTRES EXPENSE
users + roles CATEGORIES
CARDS TRANSACTIONS
lifecycle + limits merchant, amount, VAT
EXPENSES
the claim / workflow
RECEIPTS APPROVALS AI REVIEWS
image + OCR confidence timeline / history simulated rules
DOCUMENTS AUDIT LOGS
uploads + status append-only trail
cross-cutting, used across the whole system
Arrows show how data flows through the system. Every table also carries tenant_id; users, departments, cost centres and categories link in
by foreign keys (shown in the column detail in section 4).
Two choices worth understanding
Why transactions and expenses are separate tables. A transaction is the raw card spend. An expense is the claim built
on it that an employee submits and that moves through approval. Keeping them separate means the financial record stays
clean and stable while the changing workflow status lives on the expense. This is how real corporate-card systems are built.
Why the receipt match status lives on the expense, not the receipt. The four states are matched, unmatched, missing
and manual review. The tricky one is 'missing', which means no receipt exists at all, so it cannot be stored on a receipt row.
The status sits on the expense, which makes the 'missing receipt worklist' a one-line query.
3. The tables, column by column
Each table lists its columns, type and a short note. The italic line maps it back to the client's mandatory requirements.
3.1 tenants — the white-label companies
Covers requirement 16 (branding). The foundation of multi-tenancy.
Tribe Issuing App | Database Design and Data Model Page 2
Column Type Notes
id uuid (PK) The company / bank. Referenced by every other table.
name text Display name, e.g. Al Habtoor Group.
slug text (unique) URL-safe short name for routing.
logo_url text Brand logo. Drives live re-skinning per client.
primary_color text Brand colour (hex). Drives re-skinning.
secondary_color text Secondary brand colour (hex).
base_currency char(3) Default currency, e.g. AED, GBP.
status tenant_status active / suspended.
created_at, updated_at timestamptz Auto-managed.
3.2 profiles — the application users
Covers requirements 16 and 17 (users, four roles). Linked one-to-one to Supabase login.
Column Type Notes
id uuid (PK) Same ID as the Supabase login record.
tenant_id uuid (FK) Which company the user belongs to.
full_name text
email text Mirrored for display and search.
role user_role employee / manager / admin / tribe_super_admin.
department_id uuid (FK, null) The user's department.
uuid (FK to profiles,
manager_id This user's approver. Powers 'a manager sees their team'.
null)
status profile_status active / inactive.
created_at, updated_at timestamptz Auto-managed.
3.3 departments
Covers requirement 16.
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
name text e.g. Finance, Operations.
created_at, updated_at timestamptz
3.4 cost_centres
Covers requirements 14 and 16.
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
code text Short code, e.g. CC-100.
name text
created_at, updated_at timestamptz
Tribe Issuing App | Database Design and Data Model Page 3
3.5 expense_categories
Covers requirements 7 and 14.
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
name text e.g. Travel, Meals, Software.
mcc_code text (null) Optional link to a card-network category code.
created_at, updated_at timestamptz
3.6 cards
Covers requirements 4, 5 and 6 (lifecycle, admin controls, simulated card data).
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
uuid (FK to profiles,
cardholder_id The assigned employee. Null until assigned.
null)
label text Friendly card name.
masked_number text e.g. dotdotdot 4242. Simulated, no real card.
card_type card_type virtual / physical.
status card_status requested / approved / active / suspended / expired / cancelled.
spend_limit numeric(14,2) (null) Total limit.
daily_limit numeric(14,2) (null) Daily limit.
monthly_limit numeric(14,2) (null) Monthly limit.
currency char(3)
requested_by uuid (FK to profiles)
expires_at timestamptz (null)
created_at, updated_at,
timestamptz Soft delete supported.
deleted_at
3.7 transactions
Covers requirement 7 (all transaction detail).
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
card_id uuid (FK to cards) The card that was spent.
merchant_name text
mcc text (null) Merchant Category Code (raw from the network).
category_id uuid (FK, null) Mapped internal category.
amount numeric(14,2) Exact money.
currency char(3)
Tribe Issuing App | Database Design and Data Model Page 4
Column Type Notes
tax_amount numeric(14,2) (null) VAT / tax portion.
transaction_date timestamptz
status transaction_status pending / completed / refunded / reversed.
uuid (FK to
original_transaction_id For a refund or reversal, points to the original spend.
transactions, null)
created_at, updated_at,
timestamptz Soft delete supported.
deleted_at
3.8 expenses — the heart of the workflow
Covers requirements 8, 9, 11, 12, 14 (claim, matching, approval, queues, fields).
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
uuid (FK to
transaction_id The spend being claimed.
transactions)
submitted_by uuid (FK to profiles) The employee.
category_id uuid (FK, null) Chosen at submission.
cost_centre_id uuid (FK, null) Chosen at submission.
notes text (null)
status expense_status draft / submitted / approved / rejected / returned. The current state.
receipt_match_status receipt_match_status matched / unmatched / missing / manual_review.
submitted_at timestamptz (null)
created_at, updated_at,
timestamptz Soft delete supported. Full history lives in approvals.
deleted_at
3.9 receipts
Covers requirements 8, 9, 10 (capture, OCR confidence, editable fields).
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
expense_id uuid (FK, null) The expense it supports, once matched.
transaction_id uuid (FK, null) Direct link to the spend.
uploaded_by uuid (FK to profiles)
image_url text The stored receipt image (Supabase Storage).
ocr_merchant text (null) Read by OCR, editable by the user.
ocr_amount numeric(14,2) (null) Read by OCR, editable.
ocr_date timestamptz (null) Read by OCR, editable.
ocr_currency char(3) (null) Read by OCR, editable.
ocr_confidence numeric(5,2) (null) 0 to 100. A REAL output of the OCR service.
ocr_raw jsonb (null) Full raw OCR response, kept for audit and debugging.
Tribe Issuing App | Database Design and Data Model Page 5
Column Type Notes
created_at, updated_at,
timestamptz Soft delete supported.
deleted_at
3.10 approvals — the approval timeline
Covers requirement 11. Each row is one event. Append-only: no updated_at, no deleted_at.
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
expense_id uuid (FK to expenses)
actor_id uuid (FK to profiles) Who performed the action.
action approval_action submitted / approved / rejected / returned.
comment text (null) The reviewer's comment.
created_at timestamptz When it happened. Immutable.
3.11 ai_reviews — simulated AI / rules review
Covers requirement 18 (simulated only, no custom AI/ML).
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
expense_id uuid (FK to expenses)
confidence_score numeric(5,2) 0 to 100. Simulated, rules-based.
reason_code text e.g. AMOUNT_OVER_LIMIT, MISSING_RECEIPT.
suggested_action ai_suggested_action approve / review / reject.
rules_triggered jsonb (null) Which rules fired.
created_at timestamptz
3.12 documents — uploads beyond receipts
Covers requirement 3 (document management with full status workflow).
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
uploaded_by uuid (FK to profiles)
uuid (FK to profiles,
owner_id Whose document it is.
null)
title text
document_type text (null) e.g. Trade License, ID.
file_url text The stored file.
status document_status pending / approved / rejected / expired.
expiry_date date (null) Drives the 'expired' status.
Tribe Issuing App | Database Design and Data Model Page 6
Column Type Notes
uuid (FK to profiles,
reviewed_by
null)
created_at, updated_at,
timestamptz Soft delete supported.
deleted_at
3.13 audit_logs — the audit trail
Covers requirement 15. Append-only; only admins and the super admin can read it.
Column Type Notes
id uuid (PK)
tenant_id uuid (FK)
uuid (FK to profiles,
actor_id Null means a system action.
null)
action text login / upload / submit / approve / reject / return / edit / export.
entity_type text (null) What was acted on, e.g. expense, card.
entity_id uuid (null) The specific row.
metadata jsonb (null) Extra context.
created_at timestamptz When it happened. Immutable.
Tribe Issuing App | Database Design and Data Model Page 7
4. The fixed status lists (enums)
These are the only values each status field allows. The database rejects anything else.
Enum type Allowed values
tenant_status active, suspended
profile_status active, inactive
user_role employee, manager, admin, tribe_super_admin
card_type virtual, physical
card_status requested, approved, active, suspended, expired, cancelled
transaction_status pending, completed, refunded, reversed
expense_status draft, submitted, approved, rejected, returned
receipt_match_status matched, unmatched, missing, manual_review
document_status pending, approved, rejected, expired
approval_action submitted, approved, rejected, returned
ai_suggested_action approve, review, reject
5. Indexing and performance
Indexes keep the system fast as data grows. The rules: every tenant_id is indexed (every query filters by tenant, so this is
the most important index of all); every link column is indexed (card_id, transaction_id, expense_id, manager_id, and so
on); and every column we filter or sort by often is indexed (every status, transaction_date, expiry_date, and the timeline
created_at columns). The full index list is in the SQL.
6. Security readiness
The detailed rules are the next document. The schema makes them possible by carrying tenant_id on every table (rule one
is always 'you only see your own tenant'), by defining the four-level role hierarchy on [Link], and by recording each
person's approver on profiles.manager_id so a manager sees exactly their team.
Recommended approach: put tenant_id and role into the login token itself (a Supabase custom access token hook), so the
database checks permissions instantly on every row without an extra lookup. When the database is first created, Row Level
Security is switched on with no policies, which means everything is locked by default. We then open access on purpose,
table by table, in the next document. Starting locked is the safe way.
7. Money, time and the delete policy
Money: numeric(14,2), exact, never a rough decimal, stored next to a 3-letter currency code. Time: always UTC
(timestamptz); the app shows local time, the database stores one true time. Business tables (cards, transactions,
expenses, receipts, documents) use soft delete: a deleted_at timestamp hides the row but keeps the history. History tables
(approvals, ai_reviews, audit_logs) are append-only: written once, never changed, which is what makes them trustworthy as
a record.
8. Kept simple for the MVP, built to grow
Simple on purpose: card controls are columns on the card, not a rules engine; one approval step per expense (a
multi-level chain can be added later without changing the core tables); the AI review is a rules layer, not a trained model,
exactly as requested.
Already built to grow, no rebuild needed: multi-tenant and multi-currency from day one; live re-skinning ready because
branding lives on the tenant; refunds and reversals modelled; and the clean split between transactions, expenses and
approvals means real banking integration later plugs into transactions without touching the workflow.
Tribe Issuing App | Database Design and Data Model Page 8
Next step. The database is locked by default. The next document is the RLS and Security Model: the exact rules for
who can see and do what, across all four roles and all tenants. That turns this locked database into a working, role-aware,
bank-safe system. The complete runnable SQL for this design is in the .md companion file.
Tribe Issuing App | Database Design and Data Model Page 9