# Attio CRM — Teardown & Build Spec for Ultron **Purpose:** Reverse-engineer the MIS (Modern Intelligent Solution / "Holool Althakka Al-Hadeeth") Attio workspace so Fable can rebuild the same structure, UX, and functionality inside Ultron — with priority on the **Deal record + Pipeline**. **Source studied:** `app.attio.com/holool-althakka-al-hadeeth` — every page in the workspace, captured live 2026-07-19: Home, Notifications, Tasks, Notes, Emails, Calls, Reports, Sequences, Workflows, the Deals pipeline + a live deal record (Deraah Trading), Companies object + a company record (Google), People object, and a List (Cold Call Queue 1). Plus web research on Attio's REST API and MCP server for the sync option (section 9). --- ## 1. The mental model (why Attio feels clean) Attio is built on **four moving parts**. Everything else is a view on top of them. Ultron today collapses most of this into one "leads + stage" table, which is why it feels flat. The win is separating these concerns: 1. **Objects** — the nouns. Attio ships three: **Companies**, **People**, **Deals**. Each is its own table with its own attributes. A record of one object can *link* to records of another (a Deal links to Companies and People). 2. **Records** — a single row of an object (one deal, one company, one person). Opening a record gives the tabbed detail page. 3. **Lists** — curated, shareable collections that sit *on top of* an object. A list pulls the object's attributes **and adds its own list-only columns** (e.g. a "Call status"). This is the segmentation + shared-workflow layer. 4. **Activity** — every change (stage move, attribute edit, email, meeting) is auto-logged onto the record as a timeline. Nothing is manually maintained. The single most important idea for Ultron: **a Deal is not a lead.** A Deal is an *opportunity* that references a company/people. One company can have many deals over time. Ultron currently stores the funnel stage directly on `lead_clients`, which fuses the entity and the opportunity. Attio keeps them separate, and that separation is what enables the clean pipeline, the deal record, and per-deal activity. --- ## 2. The Deal object + Pipeline (PRIORITY) ### 2.1 Pipeline board (kanban) The Deals page defaults to a **kanban board**, one column per stage, drag-and-drop between columns. Header controls: pipeline/view selector, **View settings**, **Sort** (currently "Created at"), **Filter**, view-type toggle (board / list / other), **Import / Export**, **+ New Deal**. **Stages observed (8), each with a colored dot and a live count:** | # | Stage | Dot color | Count | |---|-------|-----------|-------| | 1 | Qualified | blue | 2 | | 2 | Contacted | pink | 8 | | 3 | Demo Scheduled | red | 1 | | 4 | Demo Completed | yellow | 10 | | 5 | Proposal Sent | purple | 3 | | 6 | Negotiation | green | 2 | | 7 | Closed Won | green | 0 | | 8 | Closed Lost | red | 2 | Each column header has a **`+`** to create a deal directly into that stage, and a **`+ Add calculation`** footer (sum/count/avg of a column, e.g. total deal value in stage). ### 2.2 Deal card anatomy (what shows on each kanban card) Top to bottom, every card renders: - **Deal name** (bold, with the deal object icon) - **Deal value** — currency field ("Set Deal value…" when empty) - **Deal owner** — a person (all here are "Saud Altamimi") with avatar - **A checkbox/attribute field** ("Set a value…") - **Footer row** — three quick-action icons (note, task, comment) + a **time-in-stage badge** ("3d", "105d", "0d") The time badge is a genuinely useful signal — it shows how long a deal has sat in its current stage (stale-deal detection). Ultron should compute this from `lead_stage_history`. ### 2.3 Deal record — detail page (the part you love) Opening a deal splits into a **left rail** (record details) and a **main panel** (tabbed). A top bar shows **"1 of 3 in Deal stage → Proposal Sent"** with prev/next arrows, so you can walk every deal in a stage without leaving the record. A star (favorite) and close (X) sit in the header. **Left rail — "Record Details":** - Header: deal icon + **Deal name**, edit pencil, favorite star - Action buttons: **Compose email**, + icons for note / task / workflow / etc. - **Deal name** - **Deal stage** — inline dropdown with color dot + edit pencil (change stage without leaving) - **Deal owner** — person picker - **Deal value** — currency - **Associated** — linked companies/people (avatars, "+1" overflow) - **View all values** link (expands every attribute) - **Lists** block — "Add to list" + shows which lists this record belongs to **Main panel — tab bar:** `Overview · Activity · Notes (n) · Tasks (n) · Associated People (n) · Emails (n) · Files` Each tab shows a live count badge. **Overview tab** = the dashboard for the deal. Two zones: 1. **Highlights** — a 6-card strip of the most important fields: - *Deal stage* (with a horizontal stage progress bar showing where it is in the pipeline) - *Deal value* - *Deal owner* - *Next due task* - *Associated people › Next interaction* (paywalled "Upgrade" in their plan) - *Associated people › Last interaction* ("2 months ago" + contact avatars) 2. **Activity** feed (mini, with "View all") — e.g. *"Saud changed Deal stage to Proposal Sent — 2 hours ago"*, *"changed 5 attributes — 4 days ago"*, *"created Deal — 4 days ago"*. 3. **Emails** section (mini, with "View all" + compose) — linked email threads with the associated people. **Activity tab** — full chronological feed, **grouped by time bucket** ("This week", "July"), each entry = actor avatar + what changed + timestamp. Has **"+ Add meeting"** and **View settings**. This is auto-generated; the user never writes it. **Notes tab** — free-form notes attached to the deal (count badge). **Tasks tab** — to-dos attached to the deal, feed the "Next due task" highlight. **Associated People tab** — table of linked contacts (name + avatar + attribute columns), multi-select checkboxes, **"+ Add Person"**. **Emails tab** — full email history with the deal's contacts; **Compose email** writes from inside the record and logs back automatically. **Files tab** — attachments on the deal. --- ## 3. Companies object A standard object rendered as a **spreadsheet-style table** — the MIS workspace holds **2,016 companies**. - Columns observed: **Company** (name + auto-fetched logo/favicon), **Main phone**, **Domains**, **Rating** ("Raiting" — their typo), **Description**. Columns are configurable; each has a type icon. - **Auto-enrichment**: Attio pulls the logo, domain, and firmographic data from the company's website/Twitter automatically (Google, stc, Mobily, AlRajhi Bank all show real logos). This is a paid enrichment feature — Ultron would replicate a lighter version from its own scraped Google-Maps data (logo optional). - Sortable (currently "Twitter follower count"), filterable, multi-select checkboxes per row, **+ Add calculation** per column footer, **Import / Export**, **+ New Company**. - A company **record** opens with the same left-rail + tabbed-panel pattern as a deal (its own Overview/Activity/Notes/etc.), and shows the **deals, people, and lists** it's linked to. --- ## 4. People object Same object/table pattern as Companies. Default view "Recently Contacted People", sortable by interaction fields (some are paywalled in their plan). A person record links back to their company and to any deals they're associated with, plus their own activity/email timeline. For Ultron, People is essentially your existing **contact-level** data (the `contacts` table + the person columns already imported into `leads`: full_name, job_title, seniority, linkedin). It just needs to become a first-class browsable object with a record page, not only columns on a lead. --- ## 5. Lists (you specifically want these) A **List** is a curated, shareable collection built on one object. Observed lists in the sidebar: - **Cold Call Queue 1 (4.9+)** — 337 companies - **Linkedin DM campagne 1 – Heal…** - **Test list 2** - **Linkedin DM campagne 2 – Inn…** (×2) **How a list differs from just filtering the table:** - It's a **saved, named, shareable** set of records (membership can be manual or rule-based). - It **inherits the base object's attributes** *and* adds **list-only attributes**. In Cold Call Queue the columns are: `Company`, `Company › Main phone`, `Company › Rating`, `Company › Review Count` (all inherited from the Company object) **plus `Call status`** — a field that only exists inside this list. - That list-only status column turns a list into a **lightweight workflow/mini-pipeline** (e.g. Not called → Called → Callback → Booked), separate from the main Deal pipeline. Perfect for your "team shares a funnel and everyone sees where each contact ended up" requirement. - Lists can be viewed as a table **or** a board (kanban on the list-status field), and each list has its own sort/filter/columns. This is the exact primitive Ultron is missing: your 60k Apollo/scraped leads are one big table today. Lists let a team carve out working sets ("Cold Call Queue 4.9+", "LinkedIn DM campaign — Healthcare") and track a per-list status per record, shared across the team. --- ## 5b. Records are one reusable "shell" (important build note) The **Deal record, Company record, and People record all use the exact same layout**: left rail (Record Details + Lists block) + a tabbed main panel + a "X of N in [view]" prev/next walker at the top. Only the fields and tab set differ: - **Deal** tabs: Overview · Activity · Notes · Tasks · Associated People · Emails · Files - **Company** tabs: Overview · Activity · Emails (e.g. **920** auto-captured) · Calls · Team (linked people) · Notes · Tasks · Files - **Person** tabs: same pattern, linked back to company + deals For Fable this means **build the record shell once**, then configure which attributes and tabs each object shows. That single component is 80% of the "clean" feeling. **Enrichment + relationship intelligence (Attio's paid magic):** hovering a company shows an auto-built card — logo, auto-written description, HQ location (e.g. "Mountain View"), domain, and a **relationship-strength badge** ("Very strong connection with Saud Altamimi") computed from email/interaction volume. Company table columns (Domains, Rating, Description) are auto-filled from the web. Ultron can approximate a lighter version from its own scraped Google-Maps + Apollo data (logo/description optional; relationship strength can be derived from WhatsApp/email volume you already store). --- ## 6. Every other page (full sweep) The left sidebar has three groups: top-level tools, **Records** (the objects), and **Lists**. Here is every page, captured live: **Home** — a personalized command center. "Good morning, Ahmad." + an **"Ask anything…" AI box** (their "Ask Attio" assistant, Auto model) + a **Meetings** strip ("Sync your calendar to get instant meeting context" — Google account connect) + a **Tasks** strip (count + "+ New task" + View all). This is the one-screen daily cockpit — directly relevant to your "one centralized control center" goal. **Notifications** — a dropdown inbox with two tabs, **Notifications** (mentions + important updates) and **Requests** (access requests). Empty here. This is the "someone @mentioned you / a deal you follow changed" feed. You said you'd love this — it's a per-user event inbox driven by the activity log + @mentions. **Tasks (global)** — a workspace-wide task table: columns **Task · Due date · Record (the linked deal/company/person) · Assigned to**, grouped by status (Completed/open), sortable by due date, filterable, "+ New task". Every task can be attached to a record and assigned to a teammate; the record's own Tasks tab and the "Next due task" highlight both read from here. Your `tasks` table (currently 0 rows) is the seed for this. **Notes (global)** — two sub-tabs **Notes** and **Templates**. Notes are grouped **Favorites / Created today / Created this week**, card or list view, sorted by creation date, "+ New note". Each note card shows the **linked record** (company icon), a title, a preview, the **author** (e.g. Saud Altamimi) and date. Real examples seen: *"Quotation blocked — missing info, no response since… (Lavarene Group)"*, *"Demo & quotation completed — office meeting scheduled 2026-07-14 (Eltizam)"*. This is the clean notes view you called out — it's just the notes object rolled up across all records, with templates for repeatable formats. **Emails** — a full **in-CRM email client**: sub-tabs **Drafts / Outbox / Templates**, "Configure mailbox", "Compose email", with mass-send, templates, and attachments. Sends without leaving the CRM and logs every message back onto the record (that's where the Company's "920 emails" came from). You have Smartlead for outbound; the gap is *inbound + threaded email on the record*, not drip. **Calls** — a **paid** call-recording/transcription add-on ("Transform your meetings into actionable insights", works with Google Meet / Zoom / Teams, produces Summary / Next steps / Follow-up questions / Transcript). You said you don't need calls — safe to skip. **Reports** — customizable **analytics dashboards** (Favorites + a list of dashboards, "+ New dashboard"). This is pipeline/funnel reporting: deals per stage, conversion, value, activity by rep. Lower priority but this is where a "team funnel analytics" page would live (already on your Ultron roadmap). **Automations → Sequences** — **paid** automated email sequences (drip): per-recipient status (Automated email in 1 day / Paused / Replied), enrollment insights (Active / Enrolled / Exited %). This overlaps almost exactly with **Smartlead** — no need to rebuild; integrate Smartlead instead. **Automations → Workflows** — a **visual automation builder** (trigger → action, e.g. "when a deal enters Negotiation, create a task / send Slack / update a field"). Currently **empty/unused** in MIS's workspace. Nice-to-have later; not core. **Quick actions / Search (⌘K)** — global command palette at the top of the sidebar for jumping to any record or running an action. --- ## 7. Gap analysis — Attio vs Ultron today Ultron's current schema (confirmed live): `leads` (60,418), `lead_phones` (20,985), `lead_clients` (10, holds the stage), `pipelines` (3), `pipeline_stages` (21), `lead_stage_history` (2), `lead_notes` (10), `tasks` (0, unused), `clients` (3), `contacts` (17), `app_users` (5), `user_clients` (7), `wa_accounts` + WhatsApp tables. | Capability | Attio | Ultron now | Action | |---|---|---|---| | **Deal as its own object** | Yes — deal ≠ company/person | No — stage lives on `lead_clients`, entity and opportunity fused | Add a `deals` table (opportunity referencing a lead/company + contacts). Biggest structural change. | | **Pipeline kanban** | 8 colored stages, drag-drop, counts, per-column calc | `/pipeline` kanban shipped (7-stage template) | Align stages to Attio's 8; add per-column value sum + count. | | **Deal card time-in-stage** | "3d / 105d" badge | not shown | Compute from `lead_stage_history`; surface stale deals. | | **Deal record detail** | Left rail + tabbed panel (Overview/Activity/Notes/Tasks/People/Emails/Files) + prev/next in stage | lead modal only | Build the tabbed record page. This is the "clean" feel you want. | | **Overview Highlights strip** | 6 KPI cards incl. stage progress bar, next task, last interaction | none | Build a highlights header on the record. | | **Auto activity timeline** | Every change auto-logged, grouped by date | `lead_stage_history` + `lead_notes` only | Add a generic `activity` log (stage, attribute, email, note, task, WhatsApp) + grouped feed UI. | | **Companies object** | 2,016, table + enrichment + record page | leads only | Promote company-level data to a browsable object with a record page. | | **People object** | table + record page, linked to company/deals | person columns on `leads` + `contacts` | Promote to first-class object with record page. | | **Lists** | Curated, shareable, list-only status column, board/table views | filtered leads table, no saved lists | **Add a `lists` + `list_entries` model with per-list custom fields (esp. a status). High-value, matches your WhatsApp funnel-sharing goal.** | | **Associated People on a deal** | Add/List linked contacts tab | partial | Link contacts to the deal, show in a tab. | | **Compose email / email thread on record** | Yes, auto-logged | Smartlead separate | Later: log Smartlead/email threads onto the record. | | **Tasks with due dates → "Next due task"** | Yes | `tasks` table exists but empty | Wire the tasks table into the record + a "My Day" strip (already planned). | | **Home cockpit** | Greeting + AI ask-box + meetings + tasks | none | Build a Home dashboard: today's tasks, recent activity, WhatsApp needing reply, an ask-box later. | | **Notifications inbox** | Mentions + record-follow updates | none | Per-user notification feed off the activity log + @mentions. You want this. | | **Global Notes + Templates** | Rolled up across records, authored, dated, templated | `lead_notes` (10) | Notes object with author/date/linked-record + a global Notes page + templates. | | **In-CRM Email (inbound + threads on record)** | Full client, auto-logs to record | Smartlead outbound only | Log email threads onto records; inbound sync is the real gap (drip = keep Smartlead). | | **Reports / dashboards** | Custom analytics | none | Team funnel analytics page (already on roadmap). | | **Sequences (drip)** | Paid, built-in | Smartlead | Integrate Smartlead; don't rebuild. | | **Workflows (automation)** | Visual trigger→action | none (unused in MIS too) | Defer. | **Your existing advantage:** Ultron already has the **WhatsApp layer** Attio does *not* — per-employee Baileys connections, threads/messages on leads, one VPS so any team member can read any WhatsApp. Attio has email/calls but no shared-WhatsApp inbox. So the target isn't "copy Attio" — it's **"Attio's structure + UX, plus Ultron's WhatsApp funnel."** --- ## 8. Recommended build order for Fable Framed to reuse what's already shipped (the `/pipeline` kanban and WhatsApp §15) and add the missing polish. **Phase 1 — Deal record detail page (highest visible impact).** Build the tabbed record layout for the existing lead/deal: left rail (name, stage dropdown with color dot, owner, value, associated companies/people, lists block) + main panel tabs `Overview · Activity · Notes · Tasks · Associated People · WhatsApp · Files`. Overview = Highlights strip (stage progress bar, value, owner, next due task, last interaction) + mini Activity + mini WhatsApp/Email. Add prev/next "X of N in stage" navigation. *This alone closes most of the "it's so clean" gap.* **Phase 2 — Unified activity timeline.** One `activity` log capturing stage changes, attribute edits, notes, tasks, WhatsApp messages, and emails. Render grouped by date bucket ("This week", month) on the Activity tab and mini-feed on Overview. Auto-generated — no manual entry. **Phase 3 — Lists.** `lists` + `list_entries` with per-list custom attributes (minimum: a `status` enum per list). Table **and** board views on the list status. Seed the first real list from your leads (e.g. "Cold Call Queue 4.9+"). This delivers the shared team funnel where anyone can see where a contact ended up. Pairs directly with the shared-WhatsApp layer. **Phase 4 — Promote Companies & People to browsable objects.** Company record page + People record page (reuse the Phase 1 record shell), each showing linked deals, contacts, activity, and WhatsApp. Optional light enrichment (logo/domain) from existing scraped data. **Phase 5 — Deal object separation (structural).** Introduce a real `deals` table so one company can hold multiple opportunities, with stage/owner/value on the deal rather than on `lead_clients`. Migrate current `lead_clients` stages into deals. Do this last — it's the deepest change and everything above can ship against the current model first. **Deliberately deferred (Attio extras, add only if wanted):** Reports/analytics dashboards, email Sequences (you have Smartlead), and Workflows/automations. Note them; don't block the core on them. --- ## 9. Sync option — mirror the MIS Attio funnel into Ultron (your idea) You floated: instead of (or alongside) cloning the UI, **synchronize this client's Attio funnel into Ultron** so a change updates in both places. This is real and worth doing for MIS specifically — but keep it separate from the clone project. Cloning = rebuild the product for *all* your clients; syncing = a data bridge for the *one* client (MIS) who lives in Attio. **What Attio exposes (confirmed):** - **REST API** — full CRUD over records, objects, attributes, **lists + list entries, tasks, notes, threads, comments, workspace members, and webhooks**. Auth via OAuth 2.0 or a Bearer API token. Public OpenAPI spec. - **Webhooks** — fire in near-real-time on record updates, list changes, tasks, and notes. This is what makes true sync possible (no polling). - **Official hosted MCP server** — OAuth one-click, works with Claude; reads auto-approved, writes require confirmation, scoped to the user's permissions. Good for *ad-hoc* "ask/update the CRM in natural language", not for a background sync. **Recommended architecture for MIS sync:** 1. **One-way read first (Attio → Ultron), via webhooks.** MIS grants an API token (or OAuth) on their workspace. Ultron subscribes to `record.updated` / list / task / note webhooks on the Deals object → upserts a mirrored copy into Ultron (a `synced_deals` table tagged `source='attio:MIS'`). Now Ahmad sees the MIS funnel inside Ultron's one dashboard without touching Attio. Low risk — Ultron only reads. 2. **Two-way later (Ultron → Attio), via REST.** When a mirrored deal's stage/owner/value changes in Ultron, push it back with a `PATCH` to Attio. Guard against echo loops (ignore webhooks for changes Ultron just made) and pick a conflict rule (last-write-wins or field-ownership). 3. **Field mapping.** Map Attio's Deal stage ↔ Ultron pipeline stage, owner ↔ app_user, value, associated people ↔ contacts. Store the Attio record ID on the Ultron row as the join key. **Caveats to decide before building:** - **It's the client's workspace, not yours.** Two-way sync means Ultron writes into MIS's CRM — get Saud's explicit sign-off and a scoped token; ideally OAuth so access is revocable. - **Plan gating.** API/webhook access and some features (Sequences, Calls, "Next interaction") sit behind Attio's paid tiers, and this workspace is currently **over its free-plan seat limit**. Confirm MIS is on a plan that includes API + webhooks before relying on them. - **Scope creep.** Sync makes Ultron the *viewer* of MIS's funnel; it does not give the other clients (who don't use Attio) the same UX. The clone work (sections 7–8) is still what makes Ultron good for everyone. Treat sync as a per-client connector, not a substitute for the rebuild. **Bottom line:** yes — do a **one-way webhook read sync for MIS** as a quick win so the MIS funnel shows up in Ultron's central view, and keep the full **clone/rebuild** (sections 7–8) as the main track. Add two-way write only once Saud approves and the plan supports it. --- ## 10. Company-first restructure — creation forms, data model & rollup SQL This is the crux: **Attio is built around Companies; Ultron is built around leads.** In Attio a Company is the anchor record, People are contacts linked to it, and a Deal points at one Company + several People. Ultron has one flat `leads` table. Below is exactly how Attio's create forms are shaped and how to roll your leads up into a company-first model. ### 10.1 What each Attio create form actually asks (captured live) - **Create Company** — `Domains` (the website — this is the enrichment key), `Name`, `Description`, `Team` (the people who work there), `Categories`, `Primary location`. *None strictly required; Domains + Name are the real anchors.* - **Create Person** — `Name`, `Email addresses` (multi), `Description`, **`Company`** (link to a Company), `Job title`, `Phone numbers` (multi), `Primary location`, and social/comms channels: `LinkedIn`, `AngelList`, `Facebook`, `Instagram` (X/Twitter in the standard object too). This workspace also added custom fields `Full Name AR` and `Company industry`. - **Create Deal** — `Deal name` **(required)**, `Deal stage` **(required)**, `Deal owner` **(required)**, then optional `Deal value`, `Associated people` (multi), `Associated company` (one). The relationship graph is: **Company 1—∞ People**, **Deal ∞—1 Company**, **Deal ∞—∞ People**. The "channel of communication" you mentioned (LinkedIn / call / WhatsApp / email) lives on the **Person**, and every interaction over those channels auto-logs to the Person's and the Deal's activity feed — there's no manual "channel" field to maintain. Everything except the three required Deal fields is optional, so your team never has to fill "useless details" — they add a company, attach a person, pick a stage, done. ### 10.2 Your leads already contain the company, the person, AND the channels `leads` (60,418 rows) maps cleanly onto all three Attio objects — no new data collection needed, just restructuring: | Attio object | Fields already in `leads` | |---|---| | **Company** | `company`, `website`, `industry`, `category`, `company_size`, `city`, `district`, `country`, `address`, `lat`, `lng`, `maps_url`, `rating`, `reviews`, `review_tier`, `place_id` | | **Person** | `full_name`, `first_name`, `last_name`, `job_title`, `seniority`, `department`, `linkedin`, `followers` | | **Channels** (on person/company) | `phone`, `whatsapp`, `email`, `alt_emails`, `alt_phones`, `linkedin`, `instagram`, `facebook`, `tiktok`, `snapchat`, `youtube`, `twitter` | **Diagnostics I ran on your live DB:** - 60,418 leads → **40,237 distinct company names** (so ~20k rows are extra contacts/branches that should collapse into shared companies). - **24,894** leads have a real person name (`full_name`) → these become **People**; the rest (~35k) are company-only Google-Maps listings. - **29,949** have a `website` (usable as a domain key); **23,483** have email; **3,307** have WhatsApp. - `place_id` is **unique per row** (60,418/60,418) — it's a per-lead Google Maps key, **not** a company key. So the grouping key is the **normalized company name**, with **domain (from `website`) as a stronger secondary key** where present. ### 10.3 Proposed company-first schema (add alongside `leads`, don't drop it) ``` companies company_id bigserial PK name text -- display name (best/cleanest variant) name_norm text -- normalized key (see 10.4), indexed domain text -- from website, normalized (secondary key) industry, category, company_size text city, district, country, address text lat, lng double precision maps_url text rating numeric reviews integer review_tier text source text created_at timestamptz default now() -- People = the leads that have a person; link them to a company ALTER TABLE leads ADD COLUMN company_id bigint REFERENCES companies(company_id); ALTER TABLE leads ADD COLUMN is_person boolean GENERATED ALWAYS AS (full_name IS NOT NULL AND full_name <> '') STORED; deals deal_id bigserial PK company_id bigint REFERENCES companies(company_id) client_id bigint REFERENCES clients(id) -- which of your clients owns this pipeline name text stage_id bigint REFERENCES pipeline_stages(id) owner_id bigint REFERENCES app_users(id) value numeric created_at timestamptz default now() deal_contacts -- Deal ∞—∞ People (Attio "Associated people") deal_id bigint REFERENCES deals(deal_id) lead_id bigint REFERENCES leads(id) PRIMARY KEY (deal_id, lead_id) ``` This reuses your existing `pipelines` / `pipeline_stages` / `clients` / `app_users` and keeps `lead_clients` as the migration source for stage history. ### 10.4 Rollup SQL — group 60k leads into companies **Step 1 — normalize the company name** (strip legal suffixes, punctuation, Arabic diacritics/alef variants, collapse whitespace). Do it as a reusable expression: ```sql -- normalization: lower, unaccent, drop punctuation, strip common -- legal/business words (EN + AR), squeeze spaces CREATE EXTENSION IF NOT EXISTS unaccent; CREATE OR REPLACE FUNCTION norm_company(t text) RETURNS text AS $$ SELECT trim(regexp_replace( regexp_replace( lower(unaccent(coalesce(t,''))), '\y(llc|ltd|co|company|corp|est|establishment|trading|group|holding|شركة|مؤسسة|مجموعة|القابضة|المحدودة)\y', ' ', 'g'), '[^a-z0-9؀-ۿ]+', ' ', 'g')); $$ LANGUAGE sql IMMUTABLE; ``` **Step 2 — normalize a domain** from `website` (host only, strip `www.`): ```sql -- e.g. https://www.stc.com.sa/x -> stc.com.sa -- (regexp keeps it dependency-free) -- use in step 3 as norm_domain(website) CREATE OR REPLACE FUNCTION norm_domain(u text) RETURNS text AS $$ SELECT nullif(regexp_replace( regexp_replace(lower(coalesce(u,'')), '^https?://(www\.)?', ''), '/.*$', ''), ''); $$ LANGUAGE sql IMMUTABLE; ``` **Step 3 — build `companies` from distinct keys**, picking the most complete/highest-rated row as the canonical record: ```sql -- key = domain when we have one, else normalized name (+ city to avoid -- merging same-named businesses in different cities) WITH keyed AS ( SELECT *, coalesce(nullif(norm_domain(website),''), norm_company(company) || ':' || coalesce(lower(city),'')) AS company_key FROM leads WHERE coalesce(company,'') <> '' ), ranked AS ( SELECT DISTINCT ON (company_key) company_key, company AS name, norm_company(company) AS name_norm, norm_domain(website) AS domain, industry, category, company_size, city, district, country, address, lat, lng, maps_url, rating, reviews, review_tier, source FROM keyed ORDER BY company_key, (website IS NOT NULL) DESC, -- prefer rows with a site rating DESC NULLS LAST, -- then best-rated reviews DESC NULLS LAST -- then most reviews ) INSERT INTO companies (name,name_norm,domain,industry,category,company_size, city,district,country,address,lat,lng,maps_url, rating,reviews,review_tier,source) SELECT name,name_norm,domain,industry,category,company_size,city,district, country,address,lat,lng,maps_url,rating,reviews,review_tier,source FROM ranked; ``` **Step 4 — backfill `leads.company_id`** using the same key: ```sql UPDATE leads l SET company_id = c.company_id FROM companies c WHERE c.name_norm = norm_company(l.company) AND coalesce(c.domain,'') = coalesce(norm_domain(l.website),'') AND coalesce(c.city,'') = coalesce(l.city,''); -- fallback for rows that matched on name but had no domain/city UPDATE leads l SET company_id = c.company_id FROM companies c WHERE l.company_id IS NULL AND c.name_norm = norm_company(l.company); ``` After this: every lead points at a company, leads with a `full_name` are your **People/contacts** for that company, and a company's contacts = `SELECT * FROM leads WHERE company_id = ? AND is_person`. Create a `deal` per company-per-client from `lead_clients`, and populate `deal_contacts` from that company's people. > ⚠️ Run these on a copy first (you already keep `leads_backup_*`), eyeball a sample of merges, and tune the suffix list + whether to include `city` in the key (include it to keep separate branches apart; drop it to merge a chain into one company). This is a starting recipe, not a one-click migration — Fable should review the merge counts before committing. --- ## 11. One-line summary for Fable > Rebuild Ultron around Attio's four primitives — **Objects (Companies / People / Deals), Records (one reusable tabbed detail page), Lists (curated collections with a per-list status), and an auto Activity timeline**. The foundational move is going **company-first**: roll the 60k flat leads up into ~40k Companies (normalized-name key, domain as secondary), attach the 25k named leads as People with their existing channels (phone / WhatsApp / email / LinkedIn / socials), and hang Deals off Companies. Start with the **deal record detail page** + the **company rollup**, then **Lists**, and keep Ultron's unique **shared-WhatsApp funnel** as the layer Attio doesn't have.