Schema reference

Luma BI's Postgres schema is split into two groups: source-data tables (loaded from CSV/XLSX exports) and app-state tables (written by the app itself).

Source-data tables

| Table | Grain | Source file | Dedup key | | --- | --- | --- | --- | | acquisitions | one row per user signup | Acquisitions CSV | sha256('acq\|' + id) | | enquiries | one row per supplier contacted | Enquiries CSV | sha256('enq\|' + time + \|enquiryid + \|suppid) | | fcr_enquiries | one row per wedding × supplier contact | FCR data CSV | sha256('fcr\|' + ids + \|suppid + \|enqtime) | | venues | one row per supplier (snapshot) | Venue Summary XLSX | sha256('venue\|' + id) | | deployments | one row per Jira issue × deploy | Deployments CSV | sha256('depl\|' + issue_key + \|date + \|env) |

Join keys

  • acquisitions.id = enquiries.id = fcr_enquiries.weddingid (same user)
  • enquiries.contactedsupplierid = fcr_enquiries.suppid = venues.id (same supplier — case-sensitive)
  • enquiries.enquiryid = fcr_enquiries.enquiryid (same enquiry session)

Known caveats

  • Only ~48% of enquiring users have a matching acquisition row — the acquisition export only covers recent signups. Use LEFT JOIN when stitching.
  • acquisitions.weddingdate / engagementdate are 0% populated — pull these from enquiries.weddingdate.
  • inboxmessagesent is NOT a reliable supplier-reply signal. Use fcr_enquiries.sm1_check = true for real reply data.
  • In enquiries, supplier columns are prefixed contacted: contactedsuppliercategory, contactedsupplierid, contactedsuppliername. There is no suppliercategory column.

App-state tables

| Table | Purpose | | --- | --- | | imported_files | Tracks SHA-256 of each imported file — makes re-imports idempotent | | user_activity | Dashboard login + pageview events for the admin usage report | | ai_insights | Cached Claude reports per (country × dashboard) | | ai_reports | Full AI Analyst report records — one row per question | | ai_report_stars | Per-user favourites on AI Analyst reports | | ai_report_comments | Threaded discussion on AI Analyst reports |

All created via idempotent CREATE TABLE IF NOT EXISTS + ALTER TABLE ADD COLUMN IF NOT EXISTS migrations on boot.

Tier taxonomy (from premium.tier)

| Value | Name | Type | Search distribution | | --- | --- | --- | --- | | -1 | Unverified | — | Not searchable | | 0 | Novice | Free | 5% | | 0.5 | SearchBoost | Paid | 10% | | 1 | Basic | Paid | 15% | | 2 | Lite | Paid | 75% | | 2.5 | Classic | Paid | 75% | | 3 | Expert | Paid | 95% | | 4 | Platinum | Paid | 100% | | NULL | Unregistered | Free | — |

Canonical rule: paid = suppliertier >= 0.5, free = suppliertier IS NULL OR suppliertier < 0.5. Do not use contactedsupplierregistered as a paid/free proxy — Novice (tier 0) is registered but free.

FCR funnel events

A single fcr_enquiries row tracks the full 7-step timeline of a couple-supplier contact:

  • cm1_check — couple sent initial enquiry (almost always true if the row exists)
  • smr_check — supplier READ the enquiry
  • sm1_check — supplier RESPONDED (the canonical reply signal)
  • cmr_check — couple READ the supplier response
  • cm2_check — couple responded back (the First Couple Response metric)
  • smr2_check — supplier read the couple's response
  • sm2_check — supplier responded to the couple's response

All _check fields are BOOLEAN (imported from 0/1 integers in the CSV — use = true, not string matching).

For deployment specifics see Deployment. For how Claude queries these tables see AI Analyst architecture.