Story 2664: Matter team DB invariants and create-only seed

This walkthrough covers F1 only: database invariants for business.matter_assignments and the atomic create-time team seed path. It does not narrow matter read visibility, can_read_matter, entity affiliation, or the SELECT policy; those are W2/F5 scope. Story-3386 later broadened create and lead eligibility while retaining this database-owned seed.

Why this exists

Matter creation used an application ORM insert to create the first lead_counsel row. That left the bootstrap path coupled to a broad write policy and made it possible for future changes to reopen the team table through opened_by_user_id. F1 moves the bootstrap into a narrow database-owned function and makes normal team writes pure lateral reach: firm admin, or a user already assigned to that matter.

1. One lead counsel per matter

Migration 0192_matter_team_invariants adds the partial unique index:

UNIQUE (firm_id, matter_id)
WHERE team_role = 'lead_counsel'

The same invariant is mirrored in MatterAssignment.__table_args__ so SQLModel metadata and the physical database agree.

2. Write policy is lateral reach only

The INSERT, UPDATE, and DELETE policies for matter_assignments are rewritten to:

firm_id = app.current_firm_id_or_null()
AND (
  app.current_firm_role_or_null() = 'firm_admin'
  OR EXISTS (
    SELECT 1
    FROM business.matter_assignments ma
    WHERE ma.firm_id = matter_assignments.firm_id
      AND ma.matter_id = matter_assignments.matter_id
      AND ma.user_id = app.current_user_id_or_null()
  )
)

There is no opened_by_user_id bootstrap in the write policies. The opening user does not get team write rights unless they are a firm admin or are already on that matter team.

3. Create-only seed function

business.seed_matter_team(firm_id, matter_id, lead_user_id, members) is SECURITY DEFINER, owned by the migration role, not pramaan_app. Public execute is revoked and pramaan_app gets the only application execute grant.

Guard	Effect
Transaction-local `app.creating_matter_id`	The function can run only inside the matter create transaction for that matter id.
One-shot existing-assignment check	A matter that already has any team row cannot be seeded again.
Active target locks	The creator, lead, and every additional target are locked and must be active same-firm members, editors, or admins.
Creator staffing	The creator is the lead when self-named; otherwise the creator is inserted as a member.
Member coercion	Member payload `team_role` is ignored; inserted members are always `member`.
`SELECT DISTINCT`	Duplicate member ids are deduped before insert.

4. Atomic application create path

MatterService.create_matter now allocates the new matter UUID, sets set_config('app.creating_matter_id', matter_id, true), locks the active client, flushes the matter row, and calls business.seed_matter_team(...) in the same transaction. _assign_lead_counsel keeps its service-level name, but its ORM insert body is replaced by the database seed call.

set local app.creating_matter_id = matter.id
lock active client + creator membership
insert matter
select business.seed_matter_team(firm_id, matter.id, lead_user_id, members)
insert proceedings / parties
commit

Verification covers the migration text and SQLModel mirror, the database seed function's owner and grants, dedup and role filtering, create-route seeding, unique lead enforcement, one-shot behavior, and the removed opened_by_user_id write bootstrap.