Mapping platform v2 into the import boundary schemas

// v2 source: Person (the lawyer on case 20345, "Franchise Agreement")
{
  "id": "psn_itt7xgkh7qh8tjuz",
  "firstName": "Arthur",
  "lastName": "Pemberton",
  "emailAddress": "arthur.pemberton@example-firm.test",
  "phoneNumber": null,
  "status": "REGISTERED",          // dropped: login state does not migrate
  "hasConfirmedDetails": "f",      // dropped: v2 UI state
  "hasUploadedProfilePhoto": "t",  // dropped, along with photoUrl
  "createdAt": "2025-06-24 13:54:17.089",
  "updatedAt": "2025-06-26 14:23:23.45"
}

// canonical: identity
// LAWYER because his firm seat carries the solicitor role (see next section)
{
  "external_ref": "psn_itt7xgkh7qh8tjuz",
  "identity_type": "LAWYER",
  "first_name": "Arthur",
  "last_name": "Pemberton",
  "contact_email": "arthur.pemberton@example-firm.test",
  "contact_phone": null,
  "created_at": "2025-06-24T13:54:17.089Z"
}

-- models/canonical/identity.sql
-- One row per person that some other canonical row references.
with seats as (
    select personId, roles, solicitorId
    from {{ source('v2', 'FirmEmployee') }}
    where firmId = '{{ var("firm_id") }}' and not deleted
),

referenced_people as (
    select person_external_ref as person_id from {{ ref('matter_participant') }}
    union distinct
    select author_person_external_ref from {{ ref('matter_note') }}
    union distinct
    select author_person_external_ref from {{ ref('chat_message') }}
    union distinct
    select uploader_person_external_ref from {{ ref('matter_file') }}
    union distinct
    select person_external_ref from {{ ref('call') }}
    union distinct
    select identity_external_ref from {{ ref('firm_member') }}
    union distinct
    select identity_external_ref from {{ ref('team_member') }}
)

select
    p.id as external_ref,
    case
        when s.personId is not null
         and (s.solicitorId is not null or 'solicitor' in unnest(s.roles))
            then 'LAWYER'
        when s.personId is not null then 'STAFF'
        when sol.personId is not null then 'LAWYER'
        else 'CLIENT'
    end as identity_type,
    coalesce(nullif(trim(p.firstName), ''), 'Unknown') as first_name,
    coalesce(nullif(trim(p.lastName), ''), 'Unknown') as last_name,
    p.emailAddress as contact_email,
    p.phoneNumber as contact_phone,
    p.createdAt as created_at
from {{ source('v2', 'Person') }} p
join referenced_people r on r.person_id = p.id
left join seats s on s.personId = p.id
left join {{ source('v2', 'Solicitor') }} sol on sol.personId = p.id

// v2 source: FirmEmployee (Arthur's seat at the firm)
{
  "id": "frmemp_6u2zf1bd039oes15",
  "firmId": "frm_szntlk8kzj2bsbml",         // dropped: the run targets one firm
  "personId": "psn_itt7xgkh7qh8tjuz",
  "solicitorId": "sol_gd64lh1h4xmtcb7a",
  "roles": "{solicitor,colp,cofa}",          // solicitor wins: role = LAWYER; colp/cofa collapse away
  "clerkRole": "basic_member",               // dropped: auth
  "clerkOrganisationMembershipId": "orgmem_2yxPTQuYWOu2euKlVpqeTHkYOSu",  // dropped: auth
  "deleted": "f",
  "isActive": "t",
  "instructionCredits": "0",                 // dropped: marketplace
  "caseLimit": null,                         // dropped: marketplace
  "profileId": "legprf_vg8xz611clfgoao0",    // dropped: lawyer profile (a known gap)
  "legalEntityId": "legent_a3uly6wrhhy7h4yf",            // dropped: money
  "firmEmployeeLedgerId": "frmempldg_46wgq0b2xlfg1xcv",  // dropped: money
  "firmEmployeeDisbursementLedgerId": "frmempdisldg_3qe9p420w7wirufs",  // dropped: money
  "createdAt": "2025-06-24 13:54:18.73",
  "updatedAt": "2025-11-05 13:53:21.982"
}

// canonical: firm_member
{
  "external_ref": "frmemp_6u2zf1bd039oes15",
  "identity_external_ref": "psn_itt7xgkh7qh8tjuz",
  "role": "LAWYER",
  "job_title": null,
  "created_at": "2025-06-24T13:54:18.73Z"
}

-- models/canonical/firm_member.sql
select
    fe.id as external_ref,
    fe.personId as identity_external_ref,
    case
        when fe.clerkRole = 'admin' then 'ADMIN'
        when 'solicitor' in unnest(fe.roles) then 'LAWYER'
        else 'SUPPORT'
    end as role,
    cast(null as string) as job_title,
    fe.createdAt as created_at
from {{ source('v2', 'FirmEmployee') }} fe
where fe.firmId = '{{ var("firm_id") }}'
  and not fe.deleted

// v2 source: Team (Arthur's team; no name, owner is just a pointer)
{
  "id": "team_o7jpd9wuhtkovqqr",
  "ownerId": "frmemp_6u2zf1bd039oes15",
  "firmId": "frm_szntlk8kzj2bsbml",
  "createdAt": "2025-11-05 16:54:56.3",
  "updatedAt": "2025-11-05 16:54:56.3"
}

// v2 source: TeamMember (Nina, a colleague on Arthur's team)
{
  "id": "tm_7zgrspnfbjw9iu24",
  "teamId": "team_o7jpd9wuhtkovqqr",
  "firmEmployeeId": "frmemp_89z695sgbusqqvdf",
  "roleName": "Lawyer",        // dropped: free text, no boundary column
  "isDeleted": "f",
  "partyOnAllCases": "f",      // dropped: behaviour, not history
  "createdAt": "2025-11-05 16:54:56.331",
  "updatedAt": "2025-11-05 16:54:56.331"
}

// canonical: team (name synthesised from the owner)
{
  "external_ref": "team_o7jpd9wuhtkovqqr",
  "name": "Arthur Pemberton's team",
  "created_at": "2025-11-05T16:54:56.3Z"
}

// canonical: team_member, two rows.
// The first is invented: v2 never stores the owner as a member.
[
  {
    "external_ref": "team:team_o7jpd9wuhtkovqqr:owner",
    "team_external_ref": "team_o7jpd9wuhtkovqqr",
    "identity_external_ref": "psn_itt7xgkh7qh8tjuz",
    "role": "OWNER",
    "created_at": "2025-11-05T16:54:56.3Z"
  },
  {
    "external_ref": "tm_7zgrspnfbjw9iu24",
    "team_external_ref": "team_o7jpd9wuhtkovqqr",
    "identity_external_ref": "psn_595cke9mauudrujt",
    "role": "MEMBER",
    "created_at": "2025-11-05T16:54:56.331Z"
  }
]

-- models/canonical/team.sql
select
    t.id as external_ref,
    coalesce(
        concat(trim(p.firstName), ' ', trim(p.lastName), "'s team"),
        concat('Team ', substr(t.id, -6))
    ) as name,
    t.createdAt as created_at
from {{ source('v2', 'Team') }} t
left join {{ source('v2', 'FirmEmployee') }} fe on fe.id = t.ownerId
left join {{ source('v2', 'Person') }} p on p.id = fe.personId
where t.firmId = '{{ var("firm_id") }}'

-- models/canonical/team_member.sql
with real_members as (
    select
        tm.id as external_ref,
        tm.teamId as team_external_ref,
        fe.personId as identity_external_ref,
        'MEMBER' as role,
        tm.createdAt as created_at
    from {{ source('v2', 'TeamMember') }} tm
    join {{ ref('team') }} t on t.external_ref = tm.teamId
    join {{ source('v2', 'FirmEmployee') }} fe on fe.id = tm.firmEmployeeId
    where not tm.isDeleted and not fe.deleted
),

-- v2 stores the owner as a pointer, never as a member row
synthesised_owners as (
    select
        concat('team:', t.id, ':owner') as external_ref,
        t.id as team_external_ref,
        fe.personId as identity_external_ref,
        'OWNER' as role,
        t.createdAt as created_at
    from {{ source('v2', 'Team') }} t
    join {{ source('v2', 'FirmEmployee') }} fe
        on fe.id = t.ownerId and not fe.deleted
    where t.firmId = '{{ var("firm_id") }}'
)

select * from real_members
union all
select * from synthesised_owners

// v2 source: Case 20345, "Franchise Agreement" (a completed matter)
{
  "id": "cas_zlkoq0q3ih8lb4o4",
  "humanReadableId": "20345",
  "status": "COMPLETED",
  "title": "Franchise Agreement",
  "source": "lawhive",
  "ownerId": "frmemp_6u2zf1bd039oes15",       // drives the firm-side lead check
  "leadCustomerId": "cus_jwrgv3x9u631t26c",   // not needed here: a real client party exists
  "createdAt": "2024-11-28 11:51:34.652",
  "lastActivity": "2026-06-16 09:07:41.479",
  "completedAt": "2026-06-16 09:08:19.172",
  "enabledModules": "{lawrenceBasic,caseAssessments,informationRequest}",  // dropped: deprecated
  "caseLedgerId": "csldg_596jnd6hvhnf2904",             // dropped: money
  "nonBillableLedgerId": "nbldg_cuwndkev9ju78krl",      // dropped: money
  "officeAccountLedgerId": "coaldg_fml9ci1xcg26j0h6",   // dropped: money
  "baseFeePlanId": "fp_odf4vqmy00npfaml",               // dropped: money
  "autoBillingEnabled": "t",                            // dropped: money
  "hasRequestedReview": "f"                             // dropped: marketplace
}

// canonical: matter
// COMPLETED passes through unchanged and keeps its completedAt
{
  "external_ref": "cas_zlkoq0q3ih8lb4o4",
  "human_readable_id": "20345",
  "title": "Franchise Agreement",
  "matter_status": "COMPLETED",
  "external_source": "lawhive",
  "market": "GB",
  "primary_jurisdiction_id": "gb-england-wales",
  "created_at": "2024-11-28T11:51:34.652Z",
  "last_activity_at": "2026-06-16T09:07:41.479Z",
  "completed_at": "2026-06-16T09:08:19.172Z"
}

// v2 source: the three CaseParty rows on this case (all ASSIGNED)
[
  {
    "id": "caspar_9juw0fk84sfb9a9k",
    "type": "SOLICITOR",
    "personId": "psn_itt7xgkh7qh8tjuz",
    "isLead": "t",
    "createdAt": "2024-11-28 11:51:34.652"
  },
  {
    "id": "caspar_husqm4gx4hjjtc9s",
    "type": "CUSTOMER",
    "personId": "psn_makckimjucnnw4wf",
    "isLead": "t",
    "createdAt": "2024-11-28 11:51:34.652"
  },
  {
    "id": "caspar_zjbvgkbp2ejhbt8c",
    "type": "TEAM_MEMBER",
    "personId": "psn_595cke9mauudrujt",
    "teamMemberId": "tm_7zgrspnfbjw9iu24",
    "isLead": "f",
    "createdAt": "2025-12-09 12:15:30.946"
  }
]

// canonical: matter_participant
// A well-behaved case: one lead per side already, so no fix-ups fired
[
  {
    "external_ref": "caspar_9juw0fk84sfb9a9k",
    "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
    "person_external_ref": "psn_itt7xgkh7qh8tjuz",
    "side": "FIRM",
    "participant_type": "LAWYER",
    "is_lead": true,
    "created_at": "2024-11-28T11:51:34.652Z"
  },
  {
    "external_ref": "caspar_husqm4gx4hjjtc9s",
    "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
    "person_external_ref": "psn_makckimjucnnw4wf",
    "side": "CLIENT",
    "participant_type": "CLIENT",
    "is_lead": true,
    "created_at": "2024-11-28T11:51:34.652Z"
  },
  {
    "external_ref": "caspar_zjbvgkbp2ejhbt8c",
    "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
    "person_external_ref": "psn_595cke9mauudrujt",
    "side": "FIRM",
    "participant_type": "TEAM_MEMBER",
    "is_lead": false,
    "created_at": "2025-12-09T12:15:30.946Z"
  }
]

-- models/canonical/matter.sql
select
    c.id as external_ref,
    cast(c.humanReadableId as string) as human_readable_id,
    c.title,
    case c.status
        when 'PENDING' then 'OPENING'
        when 'REASSIGNING' then 'ACTIVE'
        when 'SUBMITTED_FOR_CLOSING' then 'ACTIVE'
        else c.status  -- OPENING, ACTIVE, COMPLETED, CANCELLED pass through
    end as matter_status,
    c.source as external_source,
    'GB' as market,
    'gb-england-wales' as primary_jurisdiction_id,
    c.createdAt as created_at,
    coalesce(c.lastActivity, c.updatedAt) as last_activity_at,
    -- completed_at present exactly when the collapsed status is COMPLETED
    case when c.status = 'COMPLETED'
         then coalesce(c.completedAt, c.lastActivity, c.updatedAt)
    end as completed_at
from {{ source('v2', 'Case') }} c
join {{ source('v2', 'FirmEmployee') }} owner on owner.id = c.ownerId
where owner.firmId = '{{ var("firm_id") }}'

-- models/canonical/matter_participant.sql (sketch)
with assigned as (
    select
        cp.id as external_ref,
        cp.caseId as matter_external_ref,
        cp.personId as person_external_ref,
        if(cp.type = 'CUSTOMER', 'CLIENT', 'FIRM') as side,
        case cp.type
            when 'CUSTOMER' then 'CLIENT'
            when 'SOLICITOR' then 'LAWYER'
            else 'TEAM_MEMBER'
        end as participant_type,
        cp.isLead as source_is_lead,
        cp.createdAt as created_at
    from {{ source('v2', 'CaseParty') }} cp
    join {{ ref('matter') }} m on m.external_ref = cp.caseId
    where cp.status = 'ASSIGNED'  -- INVITED parties are never staged
    -- one row per person per matter: keep the earliest
    qualify row_number() over (
        partition by cp.caseId, cp.personId order by cp.createdAt
    ) = 1
),

-- cases with no customer party but a recorded lead customer
synthesised_client_leads as (
    select
        concat('case:', c.id, ':lead-client') as external_ref,
        c.id as matter_external_ref,
        cus.personId as person_external_ref,
        'CLIENT' as side,
        'CLIENT' as participant_type,
        true as source_is_lead,
        c.createdAt as created_at
    from {{ source('v2', 'Case') }} c
    join {{ source('v2', 'Customer') }} cus on cus.id = c.leadCustomerId
    where not exists (
        select 1 from assigned a
        where a.matter_external_ref = c.id and a.side = 'CLIENT'
    )
),

unioned as (
    select *, false as is_owner from assigned
    union all
    select *, false as is_owner from synthesised_client_leads
)

-- exactly one lead per side: the case owner wins the firm side,
-- the earliest flagged lead wins otherwise
select
    * except (source_is_lead, is_owner),
    row_number() over (
        partition by matter_external_ref, side
        order by is_owner desc, source_is_lead desc, created_at
    ) = 1 as is_lead
from unioned
-- (owner detection and the case:<id>:owner synthesis are elided here)

// v2 source: two live CaseExternalParty rows on case 20345.
// Four soft-deleted siblings (isDeleted = t) were excluded, including
// earlier duplicates of these two names.
[
  {
    "id": "casct_9dgxx26dzjsby1p0",
    "caseId": "cas_zlkoq0q3ih8lb4o4",
    "type": "person",
    "name": "Nina Ashford",            // one string; split on the final space
    "isDeleted": "f",
    "createdAt": "2026-03-20 14:42:38.673"
  },
  {
    "id": "casct_e6fd0t8p5svhttdq",
    "caseId": "cas_zlkoq0q3ih8lb4o4",
    "type": "company",
    "name": "Ashford Co",                 // companies keep the whole name
    "isDeleted": "f",
    "createdAt": "2026-03-20 14:42:31.346"
  }
]

// canonical: contact
[
  {
    "external_ref": "casct_9dgxx26dzjsby1p0",
    "contact_type": "INDIVIDUAL",
    "first_name": "Nina",
    "last_name": "Ashford",
    "title": null,
    "legal_name": null,
    "trading_name": null,
    "registration_number": null,
    "person_external_ref": null,
    "notes": null,
    "created_at": "2026-03-20T14:42:38.673Z"
  },
  {
    "external_ref": "casct_e6fd0t8p5svhttdq",
    "contact_type": "COMPANY",
    "first_name": null,
    "last_name": null,
    "title": null,
    "legal_name": "Ashford Co",
    "trading_name": null,
    "registration_number": null,
    "person_external_ref": null,
    "notes": null,
    "created_at": "2026-03-20T14:42:31.346Z"
  }
]

// canonical: matter_contact (the link back to the matter carries the role)
{
  "external_ref": "cas_zlkoq0q3ih8lb4o4:casct_9dgxx26dzjsby1p0",
  "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
  "contact_external_ref": "casct_9dgxx26dzjsby1p0",
  "role": "OTHER",
  "role_label": "External party",
  "notes": null,
  "created_at": "2026-03-20T14:42:38.673Z"
}

-- models/canonical/contact.sql
-- Three partial v2 sources become one address book.
with external_parties as (
    select
        cep.id as external_ref,
        if(cep.type = 'person', 'INDIVIDUAL', 'COMPANY') as contact_type,
        if(cep.type = 'person',
           regexp_extract(trim(cep.name), r'^(.*)\s+\S+$'), null) as first_name,
        if(cep.type = 'person',
           regexp_extract(trim(cep.name), r'(\S+)$'), null) as last_name,
        if(cep.type = 'company', trim(cep.name), null) as legal_name,
        cast(null as string) as registration_number,
        cast(null as string) as notes,
        cep.createdAt as created_at
    from {{ source('v2', 'CaseExternalParty') }} cep
    join {{ ref('matter') }} m on m.external_ref = cep.caseId
    where not cep.isDeleted
),

mail_contacts as (
    select
        cmc.id as external_ref,
        'INDIVIDUAL' as contact_type,
        regexp_extract(trim(cmc.name), r'^(.*)\s+\S+$') as first_name,
        regexp_extract(trim(cmc.name), r'(\S+)$') as last_name,
        cast(null as string) as legal_name,
        cast(null as string) as registration_number,
        cast(null as string) as notes,
        cmc.createdAt as created_at
    from {{ source('v2', 'CaseMailContact') }} cmc
    join {{ ref('matter') }} m on m.external_ref = cmc.caseId
    where not cmc.isDeleted
),

client_companies as (
    select distinct
        co.id as external_ref,
        'COMPANY' as contact_type,
        cast(null as string) as first_name,
        cast(null as string) as last_name,
        co.name as legal_name,
        co.companyNumber as registration_number,
        -- one unstructured string; rides in notes, not contact_address
        concat('Registered address: ', co.address) as notes,
        co.createdAt as created_at
    from {{ source('v2', 'CaseParty') }} cp
    join {{ source('v2', 'Company') }} co on co.id = cp.companyId
    join {{ ref('matter') }} m on m.external_ref = cp.caseId
    where cp.status = 'ASSIGNED'
)

select * from external_parties
union all select * from mail_contacts
union all select * from client_companies

contact_address (from the mail contacts' address columns) and matter_contact (one link per contact per case, with its role label) are sibling models built from the same three sources.

// v2 source: CaseFileFolder on case 20345
{
  "id": "caseFilFol_0r5z9mlry5m86qfl",
  "title": "Email Correspondence",
  "caseId": "cas_zlkoq0q3ih8lb4o4",
  "colour": "#3b82f6",                            // dropped: no boundary column
  "order": "0.000000000000000000000000000000",    // rounded to an int
  "createdAt": "2026-05-19 12:31:04.92"
}

// v2 source: CaseFile + File (one document, two v2 records)
{
  "id": "casfil_edojnsxhziik4ahw",
  "caseId": "cas_zlkoq0q3ih8lb4o4",
  "fileId": "file_f3kqx7kg70jugux7",
  "source": "upload",                        // dropped: v3 stamps IMPORT on everything
  "visibility": "solicitors",                // becomes FIRM_SIDE_ONLY
  "uploadingPersonId": "psn_itt7xgkh7qh8tjuz",
  "createdAt": "2026-05-19 12:30:15.724"
}

{
  "id": "file_f3kqx7kg70jugux7",
  "s3Key": "cases/cas_zlkoq0q3ih8lb4o4/39e14433-5c84-4be8-88e6-c0fb65fb8840-Letter%20to%20Blogs.docx",
  "originalFilename": "Letter to Blogs.docx",
  "fileSize": "67228",
  "displayName": "Letter to Blogs",
  "extension": "docx",
  "isDeleted": "f",
  "aiIngestionV3Status": "SUCCESS",          // dropped: v3 re-ingests on arrival
  "createdAt": "2026-05-19 12:30:15.724"
}

// canonical: matter_file_folder + matter_file
{
  "external_ref": "caseFilFol_0r5z9mlry5m86qfl",
  "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
  "title": "Email Correspondence",
  "order": 0
}

{
  "external_ref": "casfil_edojnsxhziik4ahw",
  "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
  "folder_external_ref": "caseFilFol_0r5z9mlry5m86qfl",   // via the join table
  "payload_uri": "s3://lawhive-v2-case-files/cases/cas_zlkoq0q3ih8lb4o4/39e14433-5c84-4be8-88e6-c0fb65fb8840-Letter%20to%20Blogs.docx",
  "payload_sha256": "7d55646dbe0b5ba4bf60c01982baaf62fba2be5ca353e989333bd16d62ca2d24",  // provisional
  "filename": "Letter to Blogs.docx",
  "file_size": 67228,
  "visibility": "FIRM_SIDE_ONLY",
  "uploader_person_external_ref": "psn_itt7xgkh7qh8tjuz",
  "created_at": "2026-05-19T12:30:15.724Z"
}

-- models/canonical/matter_file_folder.sql
select
    f.id as external_ref,
    f.caseId as matter_external_ref,
    f.title,
    cast(round(f.`order`) as int64) as `order`
from {{ source('v2', 'CaseFileFolder') }} f
join {{ ref('matter') }} m on m.external_ref = f.caseId

-- models/canonical/matter_file.sql
with folder_choice as (
    -- v2 allows many folders per file; the lowest-ordered one wins
    select j.A as case_file_id, j.B as folder_id
    from {{ source('v2', '_CaseFileToCaseFileFolder') }} j
    join {{ source('v2', 'CaseFileFolder') }} f on f.id = j.B
    qualify row_number() over (
        partition by j.A order by f.`order`, f.id
    ) = 1
)

select
    cf.id as external_ref,
    cf.caseId as matter_external_ref,
    fc.folder_id as folder_external_ref,
    concat('s3://', '{{ var("payload_bucket") }}', '/', f.s3Key) as payload_uri,
    -- provisional: replaced with the real hash when blobs are staged
    to_hex(sha256(concat('v2:s3Key:', f.s3Key))) as payload_sha256,
    coalesce(nullif(f.originalFilename, ''),
             concat(f.displayName, '.', f.extension)) as filename,
    f.fileSize as file_size,
    if(cf.visibility = 'all', 'ALL', 'FIRM_SIDE_ONLY') as visibility,
    cf.uploadingPersonId as uploader_person_external_ref,
    cf.createdAt as created_at
from {{ source('v2', 'CaseFile') }} cf
join {{ source('v2', 'File') }} f on f.id = cf.fileId
join {{ ref('matter') }} m on m.external_ref = cf.caseId
left join folder_choice fc on fc.case_file_id = cf.id
where not f.isDeleted
  and f.fileSize > 0

// v2 source: CaseNote on case 20345
{
  "id": "casnt_zo2spbvwb4lx8l2l",
  "createdById": "frmemp_6u2zf1bd039oes15",   // a firm seat, resolved to its person
  "content": "Example note",
  "caseId": "cas_zlkoq0q3ih8lb4o4",
  "isMarkdown": "t",
  "isDeleted": "f",
  "createdAt": "2025-10-24 11:16:50.522"
}

// canonical: matter_note
{
  "external_ref": "casnt_zo2spbvwb4lx8l2l",
  "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
  "name": null,
  "body_markdown": "Example note",
  "author_person_external_ref": "psn_itt7xgkh7qh8tjuz",   // the seat's person
  "created_at": "2025-10-24T11:16:50.522Z"
}

-- models/canonical/matter_note.sql
select
    n.id as external_ref,
    n.caseId as matter_external_ref,
    cast(null as string) as name,  -- v2 notes have no title
    n.content as body_markdown,    -- plain text is valid markdown as-is
    fe.personId as author_person_external_ref,
    n.createdAt as created_at
from {{ source('v2', 'CaseNote') }} n
join {{ ref('matter') }} m on m.external_ref = n.caseId
join {{ source('v2', 'FirmEmployee') }} fe on fe.id = n.createdById
where not n.isDeleted
  and trim(n.content) != ''

// v2 source: Message on case 20345's channel
{
  "id": "msg_8mkxfpzugpi5xfmz",
  "type": "text",
  "data": "{\"type\": \"text\", \"userId\": \"psn_itt7xgkh7qh8tjuz\", \"message\": \"Hi client\"}",
  "channelId": "chn_wfx61pwt35yeonkn",   // resolved to the case via CaseChannel
  "authorId": "psn_itt7xgkh7qh8tjuz",
  "isDeleted": null,
  "sendbirdMessageId": "12607981468",    // dropped: Sendbird correlation id
  "createdAt": "2025-08-13 13:41:43.124"
}

// canonical: chat_message (the body is pulled out of the data JSON)
{
  "external_ref": "msg_8mkxfpzugpi5xfmz",
  "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
  "author_person_external_ref": "psn_itt7xgkh7qh8tjuz",
  "body": "Hi client",
  "sent_at": "2025-08-13T13:41:43.124Z"
}

-- models/canonical/chat_message.sql
select
    msg.id as external_ref,
    cc.caseId as matter_external_ref,
    msg.authorId as author_person_external_ref,
    json_value(msg.data, '$.message') as body,
    msg.createdAt as sent_at
from {{ source('v2', 'Message') }} msg
join {{ source('v2', 'CaseChannel') }} cc
    on cc.channelId = msg.channelId and cc.isPrimary
join {{ ref('matter') }} m on m.external_ref = cc.caseId
where msg.type = 'text'  -- system/admin notices stay behind
  and coalesce(msg.isDeleted, false) = false
  and msg.authorId is not null
  and trim(coalesce(json_value(msg.data, '$.message'), '')) != ''

// v2 source: Call on case 20345 (a failed call: it never connected)
{
  "id": "call_fz1xfn7ljki40bzz",
  "initiatedById": "psn_itt7xgkh7qh8tjuz",
  "caseId": "cas_zlkoq0q3ih8lb4o4",
  "parties": "[{\"name\": \"Arthur Pemberton\", \"personId\": \"psn_itt7xgkh7qh8tjuz\", \"phoneNumber\": \"+447700900747\"}, {\"name\": \"Nina Ashford\", \"personId\": \"psn_595cke9mauudrujt\", \"phoneNumber\": \"+442079460001\"}]",
  "twilioSid": "CA9c36ff0f0e5ac504fff2b00c152816ab",   // dropped: telephony plumbing
  "recordingUrl": null,
  "transcriptionUrl": null,
  "durationSeconds": null,
  "startedAt": "2026-01-30 15:32:43.772",
  "completedAt": null,
  "callStatus": "failed",
  "attendanceNoteStatus": "not_requested",
  "createdAt": "2026-01-30 15:32:43.773"
}

// canonical: call
// The other-party label is everyone in parties except the dialler
{
  "external_ref": "call_fz1xfn7ljki40bzz",
  "matter_external_ref": "cas_zlkoq0q3ih8lb4o4",
  "direction": "OUTBOUND",
  "person_external_ref": "psn_itt7xgkh7qh8tjuz",
  "other_party_label": "Nina Ashford",
  "started_at": "2026-01-30T15:32:43.772Z",
  "ended_at": null,
  "recording_payload_uri": null,
  "recording_payload_sha256": null,
  "note_text": null
}

-- models/canonical/call.sql
select
    c.id as external_ref,
    c.caseId as matter_external_ref,
    'OUTBOUND' as direction,  -- every v2 call is platform-dialled
    c.initiatedById as person_external_ref,
    (
        select string_agg(
            coalesce(json_value(p, '$.name'), json_value(p, '$.phoneNumber')),
            ', '
        )
        from unnest(json_query_array(c.parties)) p
        where coalesce(json_value(p, '$.personId'), '') != c.initiatedById
    ) as other_party_label,
    coalesce(c.startedAt, c.createdAt) as started_at,  -- failed calls never started
    c.completedAt as ended_at,
    if(c.recordingUrl is not null,
       concat('s3://', '{{ var("payload_bucket") }}', '/', c.recordingUrl),
       null) as recording_payload_uri,
    if(c.recordingUrl is not null,
       to_hex(sha256(concat('v2:s3Key:', c.recordingUrl))),  -- provisional
       null) as recording_payload_sha256,
    cast(null as string) as note_text
from {{ source('v2', 'Call') }} c
join {{ ref('matter') }} m on m.external_ref = c.caseId