Network CRM — design + build record (A.5)

Status: SHIPPED (both surfaces). The 7 network_* tables are live on the shared Supabase project (network_persons, network_organizations, network_roles, network_relationships, network_interactions, network_deals, network_deal_contacts — verified 2026-07-05), and the relationship-CRM ("who-knows-who" + deal participants) ships on React web (src/components/network/) and Flutter (/network/*, features/network/). kV1HiddenRoutes is empty, so the routes are un-gated. This file is retained as the schema/design record; the sections below describe the applied design, not an unbuilt proposal. Follows cross-platform-design-parity.

Anchor on the identity spine — don't duplicate persons

unified_persons (id, canonical_name, civil_id, email, phone, person_type[]) is the deduplicated identity row that clients.unified_person_id already points at. network_persons references unified_persons — a network profile is an existing person + relationship metadata, so a banker who is also a client is ONE identity, not two. This is what keeps the feature in parity instead of becoming a mobile/web-only island.

Proposed schema (7 tables)

All: id uuid pk default gen_random_uuid(), system text (brand scope — 'aldilaijan'/'khobara'/null=both), created_by uuid default auth.uid(), created_at/updated_at timestamptz. RLS pattern below.

-- 1. Organizations (banks, developers, gov bodies, law firms, vendors…)
create table network_organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null, name_ar text,
  org_type text not null,            -- bank|developer|government|law_firm|vendor|brokerage|other
  website text, notes text,
  system text, created_by uuid default auth.uid(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- 2. Network persons = a unified_persons identity + relationship metadata
create table network_persons (
  id uuid primary key default gen_random_uuid(),
  unified_person_id uuid not null references unified_persons(id) on delete cascade,
  display_name text,                 -- optional override of canonical_name
  title text, seniority text,        -- e.g. "CFO", "owner"
  primary_org_id uuid references network_organizations(id) on delete set null,
  tags text[] not null default '{}',
  importance text not null default 'normal',  -- vip|high|normal
  source text, notes text,
  owner_id uuid default auth.uid(),  -- staff who owns the relationship
  system text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (unified_person_id)         -- one network profile per identity
);

-- 3. Roles: a person's role AT an org, with tenure (person↔org junction)
create table network_roles (
  id uuid primary key default gen_random_uuid(),
  person_id uuid not null references network_persons(id) on delete cascade,
  organization_id uuid not null references network_organizations(id) on delete cascade,
  role_title text not null,
  is_current boolean not null default true,
  started_on date, ended_on date,
  created_at timestamptz not null default now()
);

-- 4. Relationships: directed edges of the people graph
create table network_relationships (
  id uuid primary key default gen_random_uuid(),
  from_person_id uuid not null references network_persons(id) on delete cascade,
  to_person_id uuid not null references network_persons(id) on delete cascade,
  relationship_type text not null,   -- knows|reports_to|family|referred_by|colleague|partner
  strength text not null default 'medium',  -- weak|medium|strong
  notes text, created_by uuid default auth.uid(),
  created_at timestamptz not null default now(),
  check (from_person_id <> to_person_id)
);

-- 5. Interactions: the touchpoint timeline
create table network_interactions (
  id uuid primary key default gen_random_uuid(),
  person_id uuid references network_persons(id) on delete cascade,
  organization_id uuid references network_organizations(id) on delete cascade,
  interaction_type text not null,    -- call|meeting|message|email|event
  occurred_at timestamptz not null default now(),
  summary text, sentiment text,      -- positive|neutral|negative
  follow_up_at timestamptz,
  logged_by uuid default auth.uid(),
  created_at timestamptz not null default now(),
  check (person_id is not null or organization_id is not null)
);

-- 6. Network deals: referral/partnership/introduction opportunities
--    (distinct from brokerage `deals` = property transactions; optional link)
create table network_deals (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  deal_type text not null,           -- referral|partnership|introduction|investment
  stage text not null default 'open',
  value_kwd numeric, expected_close date,
  property_id uuid references properties_base(id) on delete set null,  -- optional
  owner_id uuid default auth.uid(), system text, status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- 7. Deal contacts: which network persons play which role in a network deal
create table network_deal_contacts (
  id uuid primary key default gen_random_uuid(),
  network_deal_id uuid not null references network_deals(id) on delete cascade,
  person_id uuid not null references network_persons(id) on delete cascade,
  role text not null,                -- introducer|principal|advisor|banker|lawyer|agent
  notes text,
  unique (network_deal_id, person_id, role)
);

Indexes: every FK; network_persons(owner_id), (primary_org_id); network_roles(person_id) where is_current; network_relationships(from_person_id), (to_person_id); network_interactions(person_id, occurred_at desc); network_deal_contacts(network_deal_id), (person_id).

RLS (mirrors the house pattern: has_role / app_role, anon-blocked)

A firm-wide relationship CRM is most useful shared among staff (the point is cross-firm visibility of who-knows-who). Proposed per table:

alter table network_persons enable row level security;
-- Staff read all; owner or admin write. (Repeat shape for each table.)
create policy "staff read" on network_persons for select
  using (has_any_role(auth.uid(), array['admin','agent','secretary_aldilaijan',
         'secretary_khobara','accountant']::app_role[])
         and coalesce((auth.jwt()->>'is_anonymous')::boolean,false)=false);
create policy "owner or admin write" on network_persons for all
  using (owner_id = auth.uid() or has_role(auth.uid(),'admin'::app_role))
  with check (owner_id = auth.uid() or has_role(auth.uid(),'admin'::app_role));

(Junction/edge tables — roles, relationships, interactions, deal_contacts — gate writes on the parent's owner or admin; reads on staff.)

Mobile feature architecture (house conventions + parity)

lib/data/models/        network_person.dart, network_organization.dart,
                        network_role.dart, network_relationship.dart,
                        network_interaction.dart, network_deal.dart,
                        network_deal_contact.dart   (hand-written fromJson)
lib/data/repositories/  network_repository.dart  (or split persons/orgs/deals)
lib/features/network/screens/
    network_dashboard_screen.dart      (overview: counts, recent interactions, follow-ups)
    persons_list_screen.dart           (search/filter, importance, tags)
    person_detail_screen.dart          (profile + roles + relationships + interaction timeline)
    organizations_list_screen.dart / organization_detail_screen.dart
    network_deals_screen.dart          (referral/partnership pipeline)
    relationship_graph_screen.dart     (optional: who-knows-who viz)
lib/features/network/widgets/          interaction_form_sheet, relationship_picker, …

• Riverpod FutureProvider.family per list/detail (house pattern).
• Routes under /network/* in router.dart, persona-gated (admin/agent), and added to kV1HiddenRoutes until the screens are ready (build behind the gate).
• Shared i18n keys (ar/en) + design tokens; no surface-specific copy/hex.
• Contract tests: register the new models in test/contract/ so the PostgREST shape stays pinned.
• Golden tests per screen (RTL + brand-switch + dark).

Build sequence

1. Confirm the open decisions below.
2. Apply the schema migration (additive; RLS from day one).
3. Generate contract fixtures + write the 7 models (+ contract tests).
4. Repositories (+ pure-mapping unit tests).
5. Screens behind kV1HiddenRoutes; wire providers.
6. Build the web surface on the same tables (parity); update docs/CROSS_PLATFORM_PARITY.md + a docs/screens/ entry.
7. Un-gate once both surfaces reach parity + QA.

Open product decisions (need your call before applying)

1. Network deals vs brokerage deals — keep separate (proposed: relationship/ referral opportunities, optionally linked to a property) or extend deals?
2. RLS sharing — firm-wide staff read (proposed) or strictly owner-scoped?
3. Identity — require every network_persons row to map to a unified_persons identity (proposed) or allow standalone external contacts?
4. Orgs — net-new network_organizations (proposed) or reuse any existing org/vendor concept? (None found — unified_persons is people-only.)
5. person_type — add a 'network' member to unified_persons.person_type[] when a person joins the network?

Confirm these and I'll apply the migration + build the mobile feature (and the web surface, if you point me at that repo).