#TradeForce data model

Target persistence for TradeForce under Supabase (app/supabase/migrations), driven by Bubble IR user types, option sets, and privacy roles. Narrative uses idiomatic Postgres names. Bubble ids stay in the naming map and IR evidence.

#Purpose

Define the schema implement must create so auth, onboarding, shift marketplaces, time tracking, payments, admin, chat, and shell notifications share one coherent database. This is the target schema, not a Bubble inventory dump.

#Stack and identity

• Frontend: React + Vite + TSX (app/) via Supabase JS client + user JWT.
• Backend: Supabase Auth + Postgres RLS + edge functions (app/supabase/).
• Identity: Bubble user → public.profiles keyed to auth.users.id. Do not create a SQL table named user / users for domain fields. Email/password live in Supabase Auth; domain flags (role, active, approved, onboarding_complete, rates, Stripe ids) live on profiles.

#Schema overview

#Entities and relationships

#profiles (account and role)

Every signed-in actor is a profile row. role is the option.role enum (contractor, company, admin). Company operators are not a separate companies table: Bubble custom.company only carries a name and dead lists, and live company ownership is shifts.company_id plus profiles.role = company. Company onboarding stores the company display name in first_name (same Bubble field as a contractor's legal first name).

Skill rates for verification and payment math are numeric columns on the profile (company_* and contractor_* per trade). Preferred / do-not-book lists are profile↔profile relations.

#Stripe fields (MON-MARKETPLACE fence)

Only two Stripe-related columns exist in the export: profiles.stripe_account_id and profiles.stripe_name. Implement may add those columns for read/display (contractor payments control copy and dashboard URL when an id is present). Out of scope until MON-MARKETPLACE is answered: Stripe Connect onboarding, KYC, writing stripe fields from Connect plugin responses, GetDataFromAPI live balance, payouts/split. Do not invent additional Stripe tables or columns.

#shifts and membership

A shift belongs to a company profile, carries job title, schedule, rates, skill, type, policies, and seats. Claimed contractors are a many-to-many (shift_contractors) rather than only a uuid array on the parent, matching how claim/cancel workflows mutate membership. Cancelled and no-show lists remain explicit relations.

#time_entries

Live time and payment rows come from Bubble time_tracking1 (display “Time tracking”). NewThing actions create custom.time_tracking1. The older time_tracking type is deleted in the export; send_tracking_approve already takes track as custom.time_tracking1 and only its trigger condition still carries the stale btype_id: custom.time_tracking (/api/bTMBh → properties.condition.properties). That event must be rewritten as a queue/cursor job against time_entries (open risk LOG-RECURSIVE) — the stale reference is not a reason to port a second table.

Money fields (paid_amount, admin_paid_amount, sent_for_payment, related shift totals) require server/RPC authority (MON-CLIENT-WRITES / LOG-CLIENT-WRITES): the SPA must not be the sole writer.

#documents and contractor_skills

Documents are typed rows (docs_type enum) owned by a profile. Contractor skills are rows linking a contractor to a skill_set enum value, optional manual text, and experience enum.

#chats, messages, notifications

Chats hold participants; messages hang off chat_id with soft-delete-per-user via deleted_for_ids. Notifications are fan-out rows with recipient and read-by id lists for the shell bell and chat send path.

#custom_rates

Admin day/shift rate overrides: existed vs new rate linked to a shift. Used from admin company detail Save.

#Dead and folded types

All five are marked deleted: true on the type in source/tradeforce-50323.sanitized.bubble (user_types.<id>.deleted). ir/inventory.json does not carry that flag on user_type rows — it keeps it only on user_type.field — so this section, not inventory meta, is the authority on which types are dead (toolkit defect DEF-141). Bubble removes a type's privacy roles together with the type, which is why these five and only these five have no privacy_role entries: that is deletion, not an unprotected table.

• rates: deleted type; all twelve fields deleted too. Do not create a table — use profile skill rate columns.
• job: deleted type; title/skill live on shifts.
• company: deleted type, folded into profiles as above; record fields only as do-not-migrate.
• current_contractors_info: deleted type, zero fields; preferred companies use preferred_company_ids.
• time_tracking: deleted type; do not migrate.

#Constraints and write authority

• RLS mirrors privacy roles: no anon write; owner/participant/admin/company scopes as in the privacy matrix below.
• Pagination + max limit on every collection query (backend pack rule).
• Money and approval transitions go through transactional RPCs/edge, not open client updates.
• File/image fields become Storage paths; Bubble CDN URLs are cutover inputs only.

#Failure modes

• Migrating deleted fields or the deleted rates / time_tracking / empty current_contractors_info types doubles schema noise without product value.
• Treating Bubble user as the SQL table name collides with Auth and breaks idiomatic Supabase.
• Implementing Stripe Connect because stripe columns exist violates MON-MARKETPLACE.
• Client-authoritative updates to paid_amount / sent_for_payment violate MON-CLIENT-WRITES.

#Naming (idiomatic)

Semantic adaptation mode: idiomatic. Frontend stack react-vite-tsx; backend stack supabase. Drop Bubble _text / _boolean / _number / _date suffixes when the Postgres type is clear. Option-set db_value members become enum/check values; displays stay UI copy.

Summary evidence map (gate + human):

#chats (Bubble chat)

Participant membership arrays or join equivalent.

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#company (not a target table) (Bubble company)

Company operators are profiles.role=company; live ownership via shifts.company_id → profiles. Residual answered_structurally in MOD-COMPANY-SHIFTS.

#current_contractors_info (not a target table) (Bubble current_contractors_info)

Empty type (no fields). Preferred companies use profiles.preferred_company_ids.

No fields in export.

#custom_rates (Bubble custom_rate)

Admin day-specific rate overrides.

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#documents (Bubble documents)

Owner-linked document rows; type enum docs_type.

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#job (not a target table) (Bubble job)

Legacy; live job title/skill live on shifts.

#messages (Bubble message)

Normalized under chats.

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#notifications (Bubble notification)

Header bell + chat send inserts.

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#rates (not a target table) (Bubble rates)

Every field deleted in export; skill rates live on profiles.

#shifts (Bubble shifts)

Core marketplace shift rows.

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#contractor_skills (Bubble skill_set)

Per-contractor skill rows (option + free text + experience).

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#time_tracking (not a target table) (Bubble time_tracking)

Type deleted in export; leftover send_tracking_approve param type. Live rows are time_tracking1 → time_entries (LOG-RECURSIVE rewrite).

#time_entries (Bubble time_tracking1)

Canonical time/payment entry table (MOD-TIME-TRACKING / MOD-PAYMENTS).

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#profiles (Bubble user)

Supabase Auth holds credentials; profiles is domain identity (not auth.users as the product table).

Also create on every migrated table: id uuid pk, created_at timestamptz, updated_at timestamptz, and for owner-scoped rows created_by uuid when Bubble Created By is load-bearing (messages).

#Option sets → enums

Persist db_value for storage and CHECK/enum members. Keep display strings as UI copy only. Do not invent cleaned members for opaque or typo keys that workflows already write/filter (e.g. rate_filter bTKDr db_value _40_600 while attrs encode min 60 / max 80).

#adminnav (AdminNav)

#admintab (AdminTab)

#approve_type (Approve_type)

#break (break)

#comp_contractors_type (Comp.contractors.type)

#docs_type (docs_type)

#expierence (Expierence)

#nav (Nav)

#navigationindex (NavigationIndex)

#navigationonboarding (NavigationOnboarding)

#negatives (Negatives)

#notification (Notification)

#page (Page)

#pay_practices (Rates)

#positives (Positives)

#rate_filter (Rate_filter)

#reason_of_cancellation (reason of cancellation)

#role (Role)

#shift_type (shift_type)

#skill_set (Skill set_opt)

#sort (Sort)

#status_paid (status_paid)

#tabs (Tabs)

#time_tracking (Time tracking)

No values in export — do not create a Postgres enum. Contractor time-tracking modes use tracking instead.

#tracking (Tracking)

#Authorization intent (privacy → RLS)

Bubble privacy roles become Supabase RLS policies. Pattern: everyone denies; named roles grant view_all / search_for to matching actors. One exception, and it is load-bearing: privacy_role:user:everyone carries search_for: true (with view_all: false) — the source lets unauthenticated visitors run searches over User rows. Every other everyone role is all-false. Target default is deny for anon on profiles; keeping any public lookup is a deliberate decision to record, not a rule to copy silently.

#IR evidence

#User types

• chat — /user_types/chat (migrate)
• company — /user_types/company (fold)
• current_contractors_info — /user_types/current_contractors_info (dead)
• custom_rate — /user_types/custom_rate (migrate)
• documents — /user_types/documents (migrate)
• job — /user_types/job (dead)
• message — /user_types/message (migrate)
• notification — /user_types/notification (migrate)
• rates — /user_types/rates (dead)
• shifts — /user_types/shifts (migrate)
• skill_set — /user_types/skill_set (migrate)
• time_tracking — /user_types/time_tracking (dead)
• time_tracking1 — /user_types/time_tracking1 (migrate)
• user — /user_types/user (migrate)

#Option sets

• adminnav — /option_sets/adminnav (8 values)
• admintab — /option_sets/admintab (9 values)
• approve_type — /option_sets/approve_type (3 values)
• break — /option_sets/break (11 values)
• comp_contractors_type — /option_sets/comp_contractors_type (3 values)
• docs_type — /option_sets/docs_type (7 values)
• expierence — /option_sets/expierence (6 values)
• nav — /option_sets/nav (12 values)
• navigationindex — /option_sets/navigationindex (4 values)
• navigationonboarding — /option_sets/navigationonboarding (2 values)
• negatives — /option_sets/negatives (4 values)
• notification — /option_sets/notification (2 values)
• page — /option_sets/page (2 values)
• pay_practices — /option_sets/pay_practices (4 values)
• positives — /option_sets/positives (4 values)
• rate_filter — /option_sets/rate_filter (5 values)
• reason_of_cancellation — /option_sets/reason_of_cancellation (4 values)
• role — /option_sets/role (3 values)
• shift_type — /option_sets/shift_type (3 values)
• skill_set — /option_sets/skill_set (4 values)
• sort — /option_sets/sort (5 values)
• status_paid — /option_sets/status_paid (1 values)
• tabs — /option_sets/tabs (22 values)
• time_tracking — /option_sets/time_tracking (0 values)
• tracking — /option_sets/tracking (2 values)

#Catalog / bundle

• Source preset bubble-ir, content_hash 0730383603a0811b9314f2c6bb54e9b40d7f25e134c9fd1fdbc9d3a68d0a2259
• Inventory ir/inventory.json (user_type ×14, user_type.field ×185, option_set ×25, option_set.value ×130, privacy_role ×25)
• Sanitized export chase: source/tradeforce-50323.sanitized.bubble pointers /user_types/*, /option_sets/*, /api/bTMBh (send_tracking_approve → deleted custom.time_tracking param; creates use time_tracking1)

#Programme fences

• MON-MARKETPLACE — Stripe Connect/KYC/balance/payouts out of scope; only stripe_account_id / stripe_name columns
• MON-CLIENT-WRITES / LOG-CLIENT-WRITES — money and most DB writes need server authority
• LOG-RECURSIVE — rewrite send_tracking_approve against time_entries, not a second Bubble type port

#Needs human clarification

None. After inventory + sanitized export chase, type dispositions and field mappings are encoded in the export or already decided in module residual dispositions (company folded, job/rates/time_tracking/current_contractors_info dead or empty). Programme fences are scope boundaries, not unanswered schema questions.