4 Star × DataStudios · Artifact

Data Dictionary

The live Supabase schema, table by table

4 Star Marketing — Supabase Data Dictionary

Project: Marketing (qnypvuuumzopzslmuypy). Source of truth = the CRM (MS SQL Server) synced to Supabase. Generated from the live schema on 2026-06-22.

Notes: - Row counts are Postgres estimates (small dimensions may read 0 even when populated — e.g. dim_rfq_sources has 7 rows). - The DB has no column comments set, so field descriptions below are derived from names, types, FK relationships, and the "4 Star Data Setup" ground-truth doc. Items marked ⚠ are inferred and should be confirmed with the client. - All tables have RLS enabled. FK constraint names retain SQL Server lineage (opportunities=deals, reqs/req_items=deal/deal_items).

Table index

Table	~Rows	Purpose
`deals`	1,181,549	One row per RFQ; carries the RFQ→Quote→Order lifecycle, value, source, tracking
`deal_items`	1,768,135	Line items (parts) per deal
`rfq_attribution_touches`	6,439	Click/marketing touches per deal (gclid/msclkid/utm) — the ad-click join
`contacts`	405,526	People; email/phone for Enhanced Conversions + CIO sync + rollups
`companies`	229,710	Accounts
`users`	68	Internal sales reps
`events`	3,621,806	Raw event stream (CRM/marketing activity)
`campaigns`	181	Ad/marketing campaigns by platform/account
`campaign_daily_metrics`	5,391	Daily platform metrics + quotes/orders/rfqs per campaign
`dim_rfq_sources`	7	RFQ source lookup (WEB=1 …)
`dim_status_codes`	—	Deal stage/status lookup
`dim_company_types`	15	Company type lookup
`dim_regions`	—	Region lookup
`dim_when_needed`	—	"When needed" urgency lookup
`sync_watermarks`	9	Incremental-sync cursors (poll-since-last-run)
`cio_daily_send_queue`	—	Customer.io daily send queue
`paid_vendors`	—	Paid source/medium allow-list for attribution

Join map (key relationships)

deals.company_id → companies.id, deals.contact_id → contacts.id, deals.user_id → users.id
deals.rfq_source_id → dim_rfq_sources.id, deals.status_code_id → dim_status_codes.id, deals.company_type_id → dim_company_types.id, deals.region_code_id → dim_regions.id, deals.when_needed_id → dim_when_needed.id
deal_items.deal_id → deals.id, deal_items.when_needed_id → dim_when_needed.id
rfq_attribution_touches.deal_id → deals.id
contacts.company_id → companies.id, contacts.user_id → users.id
events.{deal_id,company_id,contact_id,user_id} → respective tables
campaign_daily_metrics.campaign_uuid → campaigns.id

deals — RFQ lifecycle (1 row per RFQ)

The central table. The full RFQ→Quote→Order lifecycle lives in one row via date columns. The conversion is the RFQ; Quote and Order are the offline stages we upload. Web vs total split = rfq_source_id = 1.

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`history_id`	bigint	unique	Source CRM record id (SQL Server)
`company_id`	uuid	FK→companies	Account
`contact_id`	uuid	FK→contacts	Requesting contact (→ email/phone for ECL)
`status_code_id`	bigint	FK→dim_status_codes	Current stage/status
`when_needed_id`	bigint	FK→dim_when_needed	Urgency
`rfq_source_id`	bigint	FK→dim_rfq_sources	Lead source; 1 = Website (web vs total)
`gp_amount`	numeric		Gross profit amount ⚠
`order_total`	numeric		Order value (Order-stage conversion value)
`rfq_value`	numeric		RFQ/quote value ⚠ (often 0 at quote stage)
`rfq_score`	numeric		Lead score ⚠
`ip_address`	text		Submitter IP ⚠
`rfq_date`	timestamptz		RFQ created (RFQ conversion time)
`assigned_date`	timestamptz		Assigned to rep
`quote_date`	timestamptz		Quote created ⚠
`quote_sent_date`	timestamptz		Quote sent (Quote conversion time)
`booked_date`	timestamptz		Order booked (Order conversion time)
`created_date` / `updated_date`	timestamptz		Row create/update
`notes`	text		Free-text notes
`ga_cid`	text		GA client id (GA4 join)
`user_id`	bigint	FK→users	Owning sales rep
`rfq_id`	numeric		Business RFQ id (used as conversion `transactionId`)
`company_type_id`	bigint	FK→dim_company_types	Company type
`region_code_id`	bigint	FK→dim_regions	Region
`tracking_ids`	jsonb		Raw tracking payload exploded into `rfq_attribution_touches`

rfq_attribution_touches — marketing touches (the ad-click join)

Hourly function explodes deals.tracking_ids into per-touch rows. gclid for Google Ads matching lives here, joined by deal_id.

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`deal_id`	uuid	FK→deals	Owning deal
`history_id`	bigint		Source CRM record id
`rfq_id`	numeric		Business RFQ id
`contact_id`	uuid		Contact
`company_id`	uuid		Company
`touch_index`	int		Order of touch within the deal (0 = first)
`touch_timestamp`	timestamptz		Click/touch time (the ad click for windowing)
`utm_source` / `utm_medium` / `utm_campaign` / `utm_term` / `utm_content`	text		UTM parameters
`gclid`	text		Google Ads click id (deterministic match key)
`msclkid`	text		Microsoft/Bing click id (out of Google Ads scope)
`fbclid`	text		Meta click id
`li_fat_id`	text		LinkedIn click id
`ga_cid`	text		GA client id
`landing_page`	text		Landing URL
`referrer`	text		Referrer URL
`source_type`	text		One of `utm` / `click_id` / `referrer` / `unknown`
`is_valid_touch`	bool		Touch passed validation (use only valid touches)
`is_internal_referrer`	bool		Internal/self-referral flag
`cost_model`	text		Cost attribution model ⚠
`created_date` / `updated_date`	timestamptz		Row create/update

contacts — people (ECL source)

Email/phone here power Enhanced Conversions for Leads for web conversions with no gclid. Filter to valid + consented (email_is_valid, not do_not_contact, not suppressed) before hashing.

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`cio_id`	text		Customer.io id
`full_name` / `first_name` / `last_name`	text		Name (+ `name_parse_confidence`, `name_is_parsed`)
`email`	text	unique	Email (hash for ECL)
`phone` / `phone_2` / `fax`	text		Phone (hash for ECL) / alt phone / fax
`email_validation_status` / `email_is_valid` / `email_validated_at`	text/bool/ts		Email validation
`do_not_contact`	bool		Consent suppression flag
`suppressed_at` / `suppressed_reason`	ts/text		Suppression
`main_contact`	bool		Primary contact for the company
`deleted` / `deleted_date`	bool/ts		Soft delete
`company_id`	uuid	FK→companies	Company
`user_id`	bigint	FK→users	Owning rep
`last_deal_id` / `last_activity_date`	bigint/ts		Latest activity rollups
`last_booked_deal_id` / `last_booked_date`	bigint/ts		Latest booked rollups
`gp_lifetime` / `gp_12mo`	numeric		Gross-profit rollups
`rfq_count_lifetime` / `rfq_count_12mo`	int		RFQ count rollups
`contact_rollup_updated_at`	ts		Rollup refresh time
`exists_in_cio` / `cio_sync_eligible` / `cio_sync_eligible_at` / `cio_last_synced_at` / `cio_last_reconciled_at` / `email_send_date`	bool/ts		Customer.io sync state
`ip_address`	text		Last known IP ⚠
`county`	text		County ⚠
`rep_expires_at` / `rep_assignment_rule`	ts/text		Rep assignment
`created_date` / `updated_date`	timestamptz		Row create/update

companies — accounts

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`name`	text		Company name
`company_type_id`	bigint	FK→dim_company_types	Type
`region_code_id`	bigint	FK→dim_regions	Region
`email` / `phone` / `fax` / `website`	text		Contact details
`qbooks_id`	text		QuickBooks id
`legacy_id`	text	unique	Legacy CRM id
`user_id`	bigint	FK→users	Owning rep
`notes`	text		Notes
`created_date` / `updated_date`	timestamptz		Row create/update

users — sales reps

Field	Type	Key	Description
`id`	bigint	PK (identity)	Rep id
`full_name` / `first_name` / `last_name`	text		Name
`work_phone` / `email` / `fax`	text		Contact
`code`	text		Rep short code
`current_employee`	bool		Active flag
`is_sales`	bool		Sales rep flag
`booked_lookback_months` / `quote_lookback_months` / `target_contact_count`	int		Assignment/targeting params ⚠
`updated_date`	timestamptz		Row update

events — raw event stream

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`source_name` / `source_text` / `source_id`	text/text/numeric		Originating system + ref
`event_type`	text		Event type ⚠
`occurred_at`	timestamptz		Event time
`deal_id` / `company_id` / `contact_id` / `user_id`	uuid/bigint	FK	Related entities
`cio_eligible` / `cio_sync_date` / `cio_id`	bool/ts/text		Customer.io sync state

deal_items — line items per deal

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`history_detail_id`	bigint	unique	Source CRM line id
`deal_id`	uuid	FK→deals	Owning deal
`history_id`	bigint		Source CRM deal id
`part` / `part_clean`	text		Requested part number (raw / normalized)
`manufacturer`	text		Manufacturer
`quantity` / `quantity_requested`	numeric/text		Quantity (quoted / requested)
`unit_cost` / `unit_price` / `target_price` / `gp_amount`	numeric		Pricing + GP
`lead_time` / `condition` / `date_code`	text		Sourcing detail
`vendor`	text		Vendor
`stock_flag` / `hot_flag`	bool		In-stock / hot flags
`when_needed_id`	bigint	FK→dim_when_needed	Urgency
`notes` / `rfq_notes`	text		Notes
`source_updated_at` / `created_date` / `updated_date`	timestamptz		Timestamps

campaigns — ad/marketing campaigns

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`platform`	text		Ad platform (e.g. google, microsoft)
`account_id` / `account_name`	text		Ad account
`campaign_id` / `campaign_name`	text		Platform campaign id/name
`channel` / `campaign_type`	text		Channel / type
`status`	text		Campaign status
`created_date` / `updated_date`	timestamptz		Timestamps

campaign_daily_metrics — daily platform metrics (reconciliation spine)

Field	Type	Key	Description
`id`	uuid	PK	Surrogate key
`date`	date		Metric day
`platform` / `account_id` / `campaign_id`	text		Platform + campaign
`campaign_uuid`	uuid	FK→campaigns	Campaign link
`impressions` / `clicks` / `cost` / `cost_negative`	numeric		Ad delivery + spend
`sends` / `delivered` / `opens` / `bounces` / `unsubscribes` / `spams`	numeric		Email-channel metrics
`rfqs` / `quotes` / `orders`	numeric		Conversions attributed per campaign/day
`ctr` / `cpc` / `cpa`	numeric		Derived rates
`metadata`	jsonb		Extra platform fields
`created_date`	timestamptz		Row create

sync_watermarks — incremental cursors

Field	Type	Key	Description
`source`	text	PK	Sync source name
`last_id`	bigint		Last processed source id
`last_sync_date`	timestamptz		Last sync time
`updated_at`	timestamptz		Row update

Dimension & queue tables

dim_rfq_sources (id PK, code, description, medium, channel, source): RFQ source lookup. Known values — 1=WEB, 2=EM, 3=EMD (email), 4=PHN, 5=PHND, 6=FX (offline), 7=CP (customer portal).
dim_status_codes (id PK, code, description): deal stage/status lookup.
dim_company_types (id PK, code, description): company type lookup (15 rows).
dim_regions (id PK, code, description): region lookup.
dim_when_needed (id PK, code, description): urgency lookup.
cio_daily_send_queue (contact_id PK, claimed_at, synced_at, synced_events_at, status): Customer.io daily send work queue.
paid_vendors (id PK, source, medium, enabled): allow-list of paid source/medium pairs for attribution classification.

Implications for the offline-conversions build

Quote conversion = deals.quote_sent_date; Order conversion = deals.booked_date. Conversion value: order_total for orders; confirm the field for quotes (rfq_value is frequently 0). Idempotency/transactionId = rfq_id.
gclid match = join deals → rfq_attribution_touches (valid touches), last gclid click before the conversion, within 90 days.
ECL fallback now data-available via deals.contact_id → contacts.{email,phone}, filtered to email_is_valid / not do_not_contact / not suppressed.
Reconciliation (Obj 2) can lean on campaign_daily_metrics (platform-reported rfqs/quotes/orders/cost by day) vs. deals (source of truth), website-only.
Incremental runs key off sync_watermarks.