Med Adherence SQL

gistfile1.txt

Below is a single, merged blueprint that folds in the strongest parts of both engineers' proposals and the prior plan. It is written in the Graphile‑Starter style you are already using.

---

## Checklist

* Separate adherence from the scorecard model; reuse patients/providers/organizations/data\_imports.
* Model categories, statuses, opportunities, action types, and reason codes as lookup tables with text PKs and smart tags.
* Add a global pharmacies reference; keep adherence facts org‑scoped for RLS.
* Store PDC as integer basis‑points (0..10000). Expose percents via computed columns or views.
* Define org‑scoped facts, an actions log with a 7‑day follow‑up rule, and optional fill history.
* Add indexes for org filters, trends, and drill‑downs; enable RLS first, then policies, then GRANTs.
* Provide views for current snapshot, provider‑weekly and org‑weekly trends, plus a follow‑up queue; outline import/reconciliation function and migration steps.

---

## ADR-000X - Separate Medication Adherence From Scorecard

**Context**
Provider scorecards are binary or bucketed compliance with optional weighting and episode logic. Medication adherence uses continuous PDC, different status vocabularies by category, weekly reconciliation, and workflow steps like follow‑up and validation.

**Decision**
Create a dedicated adherence module: new lookups, global pharmacies, org‑scoped weekly fact rows, actions, and optional fill history. Reuse patients, providers, organizations, data\_imports, identity helpers, and RLS patterns.

**Why**

* Keeps scorecard constraints and weights out of adherence.
* Makes PDC math and weekly flows first‑class without retrofitting legacy tables.
* Preserves existing multi‑tenant isolation and lineage via import\_id.

**Consequences**

* Two clear modules with minimal coupling.
* Import path mirrors existing data\_imports patterns.
* Future claims fill history can extend without touching scorecard.

---

# DDL - Medication Adherence

> Notes
>
> * Use text PK lookups instead of enums so forward‑only migrations stay simple.
> * Keep new identity helpers out of this module; re‑use existing `current_user_id()` and membership functions.
> * For all new tables with `created_at, updated_at`, attach your existing `_100_timestamps` trigger.

### 0) Import types seed

```sql
-- Allow data_imports.import_type to distinguish adherence loads
insert into app_public.scorecard_import_types(type, description)
values ('med_adherence', 'Medication adherence weekly extracts')
on conflict do nothing;

-- Optionally alias for payer-specific feeds
insert into app_public.scorecard_import_types(type, description)
values ('med_adherence_bcbstn', 'Medication adherence - BCBSTN weekly extracts')
on conflict do nothing;
```

---

## 1) Lookups

```sql
/* ---------------------------------------------------------------------------
Table: med_adherence_categories
Purpose: Enum-like category taxonomy. Values: statins, rasa, oad, supd, spc
Rationale: text PK + @enum Smart Tag for Graphile; group_type supports
           status set differences (traditional vs specialized).
--------------------------------------------------------------------------- */
create table app_public.med_adherence_categories (
  code       text primary key,
  label      text not null,
  group_type text not null check (group_type in ('traditional','specialized')),
  sort_order int not null default 0 check (sort_order >= 0)
);
comment on table app_public.med_adherence_categories is E'@enum';

-- Seed common categories
insert into app_public.med_adherence_categories(code,label,group_type,sort_order) values
 ('statins','Statins','traditional',10),
 ('rasa','RAS Antagonists','traditional',20),
 ('oad','Oral Diabetes (OAD)','traditional',30),
 ('supd','SUPD','specialized',40),
 ('spc','SPC','specialized',50);
```

```sql
/* ---------------------------------------------------------------------------
Table: med_adherence_statuses
Purpose: Canonical status vocabulary across categories.
Note: Category-specific validity enforced via med_adherence_category_statuses.
--------------------------------------------------------------------------- */
create table app_public.med_adherence_statuses (
  code        text primary key,
  label       text not null,
  description text
);
comment on table app_public.med_adherence_statuses is E'@enum';

-- Traditional
insert into app_public.med_adherence_statuses(code,label,description) values
 ('compliant','Compliant','Meets adherence threshold'),
 ('non_compliant','Non-compliant','Below adherence threshold'),
 ('in_play','In-Play','Active but not yet compliant'),
 ('predicted_in_play','Predicted (In-Play)','Predicted to be in play'),
 ('unattained','Unattained','Not achievable or medication stopped'),
 ('predicted_unattainable','Predicted (Unattainable)','Predicted to be unattainable');

-- Specialized
insert into app_public.med_adherence_statuses(code,label,description) values
 ('excluded','Excluded','Excluded from measure'),
 ('in_follow_up','In Follow-up','Follow-up in progress');
```

```sql
/* ---------------------------------------------------------------------------
Table: med_adherence_category_statuses
Purpose: Allowed (category,status) pairs. This enforces valid combinations.
--------------------------------------------------------------------------- */
create table app_public.med_adherence_category_statuses (
  category_code text not null references app_public.med_adherence_categories(code) on delete cascade,
  status_code   text not null references app_public.med_adherence_statuses(code)   on delete cascade,
  primary key (category_code, status_code)
);

-- Seed allowed pairs
insert into app_public.med_adherence_category_statuses(category_code,status_code)
select c.code, s.code
from app_public.med_adherence_categories c
join app_public.med_adherence_statuses s on (
  -- traditional set
  (c.group_type = 'traditional' and s.code in (
    'compliant','non_compliant','in_play','predicted_in_play','unattained','predicted_unattainable'
  )) or
  -- specialized set
  (c.group_type = 'specialized' and s.code in ('compliant','excluded','in_follow_up'))
);
```

```sql
/* ---------------------------------------------------------------------------
Table: med_adherence_opportunities
Purpose: Payer "opportunity" codes (e.g., Late to Fill).
--------------------------------------------------------------------------- */
create table app_public.med_adherence_opportunities (
  code  text primary key,
  label text not null
);
comment on table app_public.med_adherence_opportunities is E'@enum';

insert into app_public.med_adherence_opportunities(code,label) values
 ('late_to_fill','Late to Fill'),
 ('refills_needed','Refills Needed');
```

```sql
/* ---------------------------------------------------------------------------
Tables: med_adherence_action_types, med_adherence_reason_codes,
        med_adherence_action_reason_map
Purpose: Auditable user intents plus allowable reason codes per action.
--------------------------------------------------------------------------- */
create table app_public.med_adherence_action_types (
  code  text primary key,
  label text not null
);
comment on table app_public.med_adherence_action_types is E'@enum';

insert into app_public.med_adherence_action_types(code,label) values
 ('follow_up','Follow-up'),
 ('validation_complete','Validation complete'),
 ('medication_stopped','Medication stopped'),
 ('exclude','Exclude');

create table app_public.med_adherence_reason_codes (
  code        text primary key,
  label       text not null,
  description text
);
comment on table app_public.med_adherence_reason_codes is E'@enum';

-- Map of which reasons are valid for which action (optional to start)
create table app_public.med_adherence_action_reason_map (
  action_code text not null references app_public.med_adherence_action_types(code) on delete cascade,
  reason_code text not null references app_public.med_adherence_reason_codes(code) on delete cascade,
  primary key (action_code, reason_code)
);
```

**RLS for lookups - global reference pattern**

```sql
alter table app_public.med_adherence_categories              enable row level security;
alter table app_public.med_adherence_statuses                enable row level security;
alter table app_public.med_adherence_category_statuses       enable row level security;
alter table app_public.med_adherence_opportunities           enable row level security;
alter table app_public.med_adherence_action_types            enable row level security;
alter table app_public.med_adherence_reason_codes            enable row level security;
alter table app_public.med_adherence_action_reason_map       enable row level security;

create policy select_all on app_public.med_adherence_categories        for select using (true);
create policy select_all on app_public.med_adherence_statuses          for select using (true);
create policy select_all on app_public.med_adherence_category_statuses for select using (true);
create policy select_all on app_public.med_adherence_opportunities     for select using (true);
create policy select_all on app_public.med_adherence_action_types      for select using (true);
create policy select_all on app_public.med_adherence_reason_codes      for select using (true);
create policy select_all on app_public.med_adherence_action_reason_map for select using (true);

create policy admin_all on app_public.med_adherence_categories
  for all using (exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin));
create policy admin_all on app_public.med_adherence_statuses
  for all using (exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin));
create policy admin_all on app_public.med_adherence_category_statuses
  for all using (exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin));
create policy admin_all on app_public.med_adherence_opportunities
  for all using (exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin));
create policy admin_all on app_public.med_adherence_action_types
  for all using (exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin));
create policy admin_all on app_public.med_adherence_reason_codes
  for all using (exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin));
create policy admin_all on app_public.med_adherence_action_reason_map
  for all using (exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin));
```

---

## 2) Global reference: Pharmacies

```sql
/* ---------------------------------------------------------------------------
Table: pharmacies
Purpose: Directory of pharmacies - global reference, read for all. Mutations
         limited to admins. Org-scoped linkage happens in adherence facts.
--------------------------------------------------------------------------- */
create table app_public.pharmacies (
  id         uuid primary key default gen_random_uuid(),
  name       text not null,
  phone      text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint pharmacies_phone_format
    check (phone is null or phone ~ '^\+?[0-9\s\-\(\)\.]+$')
);

create index idx_pharmacies_lower_name on app_public.pharmacies (lower(trim(name)));

-- Timestamps
create trigger _100_timestamps
  before insert or update on app_public.pharmacies
  for each row execute function app_private.tg__timestamps();

-- RLS
alter table app_public.pharmacies enable row level security;
create policy select_all on app_public.pharmacies for select using (true);
create policy admin_all  on app_public.pharmacies for all using (
  exists (select 1 from app_public.users where id = app_public.current_user_id() and is_admin)
);
```

---

## 3) Core org‑scoped facts

```sql
/* ---------------------------------------------------------------------------
Table: med_adherence_records
Grain: organization_id + patient_id + category_code + medication_key + import_id
PDC:   store as integer basis-points (0..10000); expose percents via computed.
Notes:
- status_code validity enforced via composite FK to category-status map.
- provider_id is the attributed provider (may be null).
- prescriber_provider_id optional - link to prescriber if resolvable.
- Use 'status_source' to track payer/system/user origin of status.
--------------------------------------------------------------------------- */
create table app_public.med_adherence_records (
  id uuid primary key default gen_random_uuid(),

  organization_id uuid not null
    references app_public.organizations(id) on delete cascade,

  import_id uuid not null
    references app_public.data_imports(id) on delete cascade,

  patient_id uuid not null
    references app_public.patients(id) on delete cascade,

  provider_id uuid
    references app_public.providers(id) on delete set null,

  category_code text not null
    references app_public.med_adherence_categories(code),

  medication_key text not null,      -- e.g., "ROSUVASTATIN TAB 40MG"
  ndc_11        text,

  -- Enforce status is valid for category via composite FK.
  status_code   text not null,
  opportunity_code text references app_public.med_adherence_opportunities(code),

  pdc_rate_bps       int not null check (pdc_rate_bps between 0 and 10000),
  prior_year_pdc_bps int check (prior_year_pdc_bps between 0 and 10000),

  quantity        int check (quantity is null or quantity > 0),
  days_supply     int check (days_supply is null or days_supply > 0),
  remaining_fills int,

  prescriber_provider_id uuid references app_public.providers(id) on delete set null,

  last_fill_date    date,
  refill_due_date   date,
  absolute_fail_date date,

  pharmacy_id   uuid references app_public.pharmacies(id) on delete set null,
  pharmacy_name text,
  pharmacy_phone text check (pharmacy_phone is null or pharmacy_phone ~ '^\+?[0-9\s\-\(\)\.]+$'),

  low_income_subsidy boolean,

  status_source text default 'payer' check (status_source in ('payer','system','user')),

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),

  constraint med_adherence_records_uniq unique
    (organization_id, patient_id, category_code, medication_key, import_id),

  constraint med_status_valid_for_category
    foreign key (category_code, status_code)
    references app_public.med_adherence_category_statuses(category_code, status_code)
);

-- Indexes for RLS, listing, trends, and drill-downs
create index idx_med_rec_org          on app_public.med_adherence_records(organization_id);
create index idx_med_rec_import       on app_public.med_adherence_records(import_id);
create index idx_med_rec_patient      on app_public.med_adherence_records(patient_id);
create index idx_med_rec_provider     on app_public.med_adherence_records(provider_id);
create index idx_med_rec_category     on app_public.med_adherence_records(category_code);
create index idx_med_rec_status       on app_public.med_adherence_records(status_code);
create index idx_med_rec_pdc          on app_public.med_adherence_records(organization_id, pdc_rate_bps desc, id desc);
create index idx_med_rec_patient_cat  on app_public.med_adherence_records(patient_id, category_code);

-- Timestamps
create trigger _100_timestamps
  before insert or update on app_public.med_adherence_records
  for each row execute function app_private.tg__timestamps();

-- RLS
alter table app_public.med_adherence_records enable row level security;

create policy select_own on app_public.med_adherence_records
  for select using (
    organization_id in (select app_public.current_user_member_organization_ids())
  );

create policy manage_own on app_public.med_adherence_records
  for all using (
    organization_id in (select app_public.current_user_member_organization_ids())
  )
  with check (
    organization_id in (select app_public.current_user_member_organization_ids())
  );
```

**Computed columns for GraphQL convenience**

```sql
-- 5330 -> 53.3
create function app_public.med_adherence_records_pdc_percent(r app_public.med_adherence_records)
returns double precision language sql stable as $$
  select r.pdc_rate_bps / 100.0
$$;
comment on function app_public.med_adherence_records_pdc_percent(app_public.med_adherence_records)
is '@name pdcRatePercent';

create function app_public.med_adherence_records_prior_pdc_percent(r app_public.med_adherence_records)
returns double precision language sql stable as $$
  select case when r.prior_year_pdc_bps is null then null else r.prior_year_pdc_bps / 100.0 end
$$;
comment on function app_public.med_adherence_records_prior_pdc_percent(app_public.med_adherence_records)
is '@name priorYearPdcPercent';
```

---

## 4) User actions (audited)

```sql
/* ---------------------------------------------------------------------------
Table: med_adherence_actions
Purpose: Auditable workflow - follow-up, validation, stopped, exclude.
Notes:
- Link to patient+category always; optionally link to a specific record row.
- 7-day rule: store a date in follow_up_expires_at. Date is sufficient since
  payer updates are daily/weekly - avoids tz churn and simplifies queries.
--------------------------------------------------------------------------- */
create table app_public.med_adherence_actions (
  id uuid primary key default gen_random_uuid(),

  organization_id uuid not null references app_public.organizations(id) on delete cascade,
  patient_id      uuid not null references app_public.patients(id)      on delete cascade,
  category_code   text not null references app_public.med_adherence_categories(code),

  record_id uuid references app_public.med_adherence_records(id) on delete set null,

  action_code text not null references app_public.med_adherence_action_types(code),
  reason_code text references app_public.med_adherence_reason_codes(code),
  -- Enforce reason validity when you start scoping reasons by action:
  -- constraint reason_valid_for_action foreign key (action_code, reason_code)
  --   references app_public.med_adherence_action_reason_map(action_code, reason_code),

  notes text,

  follow_up_expires_at date,   -- set current_date + 7 for follow_up
  effective_date       date default current_date,

  created_by_user_id uuid not null references app_public.users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index idx_med_act_org              on app_public.med_adherence_actions(organization_id);
create index idx_med_act_patient_category on app_public.med_adherence_actions(patient_id, category_code);
create index idx_med_act_followup_exp     on app_public.med_adherence_actions(follow_up_expires_at);

create trigger _100_timestamps
  before insert or update on app_public.med_adherence_actions
  for each row execute function app_private.tg__timestamps();

alter table app_public.med_adherence_actions enable row level security;

create policy select_own on app_public.med_adherence_actions
  for select using (organization_id in (select app_public.current_user_member_organization_ids()));

create policy insert_own on app_public.med_adherence_actions
  for insert with check (
    organization_id in (select app_public.current_user_member_organization_ids())
    and created_by_user_id = app_public.current_user_id()
  );

create policy update_own on app_public.med_adherence_actions
  for update using (organization_id in (select app_public.current_user_member_organization_ids()));

create policy delete_own on app_public.med_adherence_actions
  for delete using (
    organization_id in (select app_public.current_user_member_organization_ids())
    and created_by_user_id = app_public.current_user_id()
  );
```

**Convenience function for follow‑up**

```sql
/* ---------------------------------------------------------------------------
Function: med_adherence_mark_follow_up
Purpose: Create a follow-up action with a default +7 day expiry, tied to
         patient+category, optionally to a specific record.
--------------------------------------------------------------------------- */
create function app_public.med_adherence_mark_follow_up(
  p_patient_id    uuid,
  p_category_code text,
  p_record_id     uuid default null,
  p_reason_code   text default null,
  p_notes         text default null
) returns app_public.med_adherence_actions
language plpgsql
security definer
set search_path from current
as $$
declare
  v_org uuid;
  v_action app_public.med_adherence_actions;
begin
  select organization_id into v_org
  from app_public.patients
  where id = p_patient_id
    and organization_id in (select app_public.current_user_member_organization_ids());

  if v_org is null then
    raise exception 'Patient not found or access denied';
  end if;

  insert into app_public.med_adherence_actions (
    organization_id, patient_id, category_code, record_id,
    action_code, reason_code, notes, follow_up_expires_at,
    effective_date, created_by_user_id
  ) values (
    v_org, p_patient_id, p_category_code, p_record_id,
    'follow_up', p_reason_code, p_notes, current_date + 7,
    current_date, app_public.current_user_id()
  )
  returning * into v_action;

  return v_action;
end
$$;
comment on function app_public.med_adherence_mark_follow_up(uuid, text, uuid, text, text)
is 'Create a follow-up action for a patient/category with a 7-day expiration window';
```

---

## 5) Optional: Prescription fill history

```sql
/* ---------------------------------------------------------------------------
Table: patient_rx_fills
Purpose: Claims-like fill facts for trend lines and analytics.
Note: Seed from last_fill_date if that is all you get initially.
--------------------------------------------------------------------------- */
create table app_public.patient_rx_fills (
  id uuid primary key default gen_random_uuid(),

  organization_id uuid not null references app_public.organizations(id) on delete cascade,
  patient_id      uuid not null references app_public.patients(id)      on delete cascade,
  provider_id     uuid references app_public.providers(id) on delete set null,
  pharmacy_id     uuid references app_public.pharmacies(id) on delete set null,

  category_code   text not null references app_public.med_adherence_categories(code),
  medication_key  text not null,
  ndc_11          text,

  fill_date   date not null,
  days_supply int,
  quantity    int,

  import_id uuid not null references app_public.data_imports(id) on delete cascade,

  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index idx_rx_fills_org          on app_public.patient_rx_fills(organization_id);
create index idx_rx_fills_patient_date on app_public.patient_rx_fills(patient_id, fill_date desc);

create trigger _100_timestamps
  before insert or update on app_public.patient_rx_fills
  for each row execute function app_private.tg__timestamps();

alter table app_public.patient_rx_fills enable row level security;

create policy select_own on app_public.patient_rx_fills
  for select using (organization_id in (select app_public.current_user_member_organization_ids()));

create policy manage_own on app_public.patient_rx_fills
  for all using (organization_id in (select app_public.current_user_member_organization_ids()))
  with check (organization_id in (select app_public.current_user_member_organization_ids()));
```

---

## 6) Views for GraphQL and reporting

```sql
/* ---------------------------------------------------------------------------
View: med_adherence_current
Purpose: Most recent snapshot per patient-category-medication for each org,
         using the latest completed adherence import.
--------------------------------------------------------------------------- */
create view app_public.med_adherence_current as
with latest_import_per_org as (
  select distinct on (di.organization_id)
    di.organization_id,
    di.id as import_id,
    di.report_date,
    di.created_at
  from app_public.data_imports di
  where di.status = 'completed'
    and di.import_type in ('med_adherence','med_adherence_bcbstn')
  order by di.organization_id, di.report_date desc, di.created_at desc
),
ranked as (
  select
    mar.*,
    row_number() over (
      partition by mar.organization_id, mar.patient_id, mar.category_code, mar.medication_key
      order by mar.updated_at desc, mar.id desc
    ) as rn
  from app_public.med_adherence_records mar
  join latest_import_per_org l on l.import_id = mar.import_id
)
select *
from ranked
where rn = 1;
comment on view app_public.med_adherence_current is
  E'@primaryKey id\n@foreignKey (patient_id) references app_public.patients (id)\n@foreignKey (provider_id) references app_public.providers (id)';
```

```sql
/* ---------------------------------------------------------------------------
View: med_adherence_provider_weekly
Purpose: Weekly provider-level trend by category (basis-points).
--------------------------------------------------------------------------- */
create view app_public.med_adherence_provider_weekly as
select
  mar.provider_id,
  mar.category_code,
  di.organization_id,
  di.report_date as week_of,
  count(distinct mar.patient_id) as patient_count,
  round(avg(mar.pdc_rate_bps))::int as avg_pdc_bps
from app_public.med_adherence_records mar
join app_public.data_imports di on di.id = mar.import_id
where di.status = 'completed'
group by mar.provider_id, mar.category_code, di.organization_id, di.report_date
order by week_of desc;

comment on view app_public.med_adherence_provider_weekly is
  E'@primaryKey provider_id,organization_id,category_code,week_of\n@foreignKey (provider_id) references app_public.providers (id)';
```

```sql
/* ---------------------------------------------------------------------------
View: med_adherence_org_weekly
Purpose: Weekly org-population trend by category (basis-points).
--------------------------------------------------------------------------- */
create view app_public.med_adherence_org_weekly as
select
  di.organization_id,
  mar.category_code,
  di.report_date as week_of,
  count(distinct mar.patient_id) as patient_count,
  round(avg(mar.pdc_rate_bps))::int as avg_pdc_bps
from app_public.med_adherence_records mar
join app_public.data_imports di on di.id = mar.import_id
where di.status = 'completed'
group by di.organization_id, mar.category_code, di.report_date
order by week_of desc;
comment on view app_public.med_adherence_org_weekly is
  E'@primaryKey organization_id,category_code,week_of';
```

```sql
/* ---------------------------------------------------------------------------
View: med_adherence_follow_up_queue
Purpose: Worklist - latest follow_up not expired and not superseded by a later
         validation_complete. Joins optional record for context.
--------------------------------------------------------------------------- */
create view app_public.med_adherence_follow_up_queue as
with latest_follow_up as (
  select distinct on (a.organization_id, a.patient_id, a.category_code)
    a.*
  from app_public.med_adherence_actions a
  where a.action_code = 'follow_up'
  order by a.organization_id, a.patient_id, a.category_code, a.created_at desc
),
latest_validation as (
  select distinct on (a.organization_id, a.patient_id, a.category_code)
    a.organization_id, a.patient_id, a.category_code, a.created_at as validated_at
  from app_public.med_adherence_actions a
  where a.action_code = 'validation_complete'
  order by a.organization_id, a.patient_id, a.category_code, a.created_at desc
),
todo as (
  select lf.*
  from latest_follow_up lf
  left join latest_validation lv
    on lv.organization_id = lf.organization_id
   and lv.patient_id      = lf.patient_id
   and lv.category_code   = lf.category_code
  where (lf.follow_up_expires_at is null or lf.follow_up_expires_at >= current_date)
    and (lv.validated_at is null or lv.validated_at < lf.created_at)
)
select
  lf.id as action_id,
  lf.organization_id,
  lf.patient_id,
  p.name as patient_name,
  lf.category_code,
  lf.record_id,
  mar.provider_id,
  pr.name as provider_name,
  mar.medication_key,
  mar.pdc_rate_bps,
  mar.status_code,
  mar.refill_due_date,
  lf.follow_up_expires_at,
  lf.created_at as follow_up_created_at
from todo lf
left join app_public.med_adherence_records mar on mar.id = lf.record_id
left join app_public.patients p  on p.id  = lf.patient_id
left join app_public.providers pr on pr.id = mar.provider_id;

comment on view app_public.med_adherence_follow_up_queue is
  E'@primaryKey action_id\n@foreignKey (patient_id) references app_public.patients (id)';
```

> RLS note: Views inherit RLS from underlying tables. We do not duplicate RLS conditions in the views.

---

## 7) GRANTs

```sql
-- Lookups and pharmacies - SELECT to visitor (writes via admin role)
grant select on app_public.pharmacies                           to db_visitor;
grant select on app_public.med_adherence_categories             to db_visitor;
grant select on app_public.med_adherence_statuses               to db_visitor;
grant select on app_public.med_adherence_category_statuses      to db_visitor;
grant select on app_public.med_adherence_opportunities          to db_visitor;
grant select on app_public.med_adherence_action_types           to db_visitor;
grant select on app_public.med_adherence_reason_codes           to db_visitor;
grant select on app_public.med_adherence_action_reason_map      to db_visitor;

-- Facts
grant select on app_public.med_adherence_records to db_visitor;
grant insert (
  organization_id, import_id, patient_id, provider_id, category_code,
  medication_key, ndc_11, status_code, opportunity_code,
  pdc_rate_bps, prior_year_pdc_bps, quantity, days_supply, remaining_fills,
  prescriber_provider_id, last_fill_date, refill_due_date, absolute_fail_date,
  pharmacy_id, pharmacy_name, pharmacy_phone, low_income_subsidy, status_source
) on app_public.med_adherence_records to db_visitor;
grant update (
  status_code, opportunity_code, pdc_rate_bps, prior_year_pdc_bps,
  last_fill_date, refill_due_date, absolute_fail_date,
  pharmacy_id, pharmacy_name, pharmacy_phone
) on app_public.med_adherence_records to db_visitor;

-- Actions
grant select, insert, update, delete on app_public.med_adherence_actions to db_visitor;

-- Fills
grant select on app_public.patient_rx_fills to db_visitor;
grant insert (
  organization_id, patient_id, provider_id, pharmacy_id,
  category_code, medication_key, ndc_11, fill_date, days_supply, quantity, import_id
) on app_public.patient_rx_fills to db_visitor;
```

---

## 8) Import and reconciliation function (pattern)

> Keep the function private, `SECURITY DEFINER`, set `search_path from current`. It should:
>
> * Resolve or create patient/provider within org as per your business rules.
> * Normalize category/status/opportunity strings to codes.
> * Convert "53.3%" to `pdc_rate_bps = 5330`.
> * Upsert `med_adherence_records` on the unique grain.
> * Optionally seed `patient_rx_fills` from `last_fill_date`.
> * Return number of rows processed.

Skeleton:

```sql
create function app_private.import_med_adherence(
  p_org uuid,
  p_import_id uuid,
  p_rows jsonb
) returns integer
language plpgsql
security definer
set search_path from current
as $$
declare
  v_row jsonb;
  v_count int := 0;
  v_patient_id uuid;
  v_provider_id uuid;
  v_prescriber_id uuid;
  v_pharmacy_id uuid;
  v_category text;
  v_status text;
  v_opportunity text;
  v_med_key text;
  v_pdc_bps int;
  v_pdc_prior_bps int;
begin
  -- Iterate rows
  for v_row in select * from jsonb_array_elements(p_rows) loop
    -- Resolve entities (external IDs and fuzzy provider/pharmacy name matches)
    -- Map category/status/opportunity to codes (lowercase, replace spaces/parentheses)
    -- Convert PDC "%" -> basis-points
    -- Insert/upsert into med_adherence_records using ON CONFLICT on unique constraint
    -- Optionally insert into patient_rx_fills (from last_fill_date)

    -- ... implement as per your ingest pipeline ...

    v_count := v_count + 1;
  end loop;
  return v_count;
end
$$;
comment on function app_private.import_med_adherence(uuid, uuid, jsonb)
is 'Bulk-import medication adherence weekly data into med_adherence_records; idempotent per (org, import_id)';
```

---

## 9) Migration plan

1. Insert new `scorecard_import_types` values.
2. Create lookups; seed category, status, mapping, opportunities, actions, reason codes.
3. Enable RLS and add `select_all` and `admin_all` policies on lookups.
4. Create `pharmacies`; index; timestamps; enable RLS and policies.
5. Create `med_adherence_records`; indexes; timestamps; enable RLS and manage policies.
6. Create `med_adherence_actions`; indexes; timestamps; enable RLS and policies.
7. Optionally create `patient_rx_fills`; indexes; timestamps; RLS and policies.
8. Create views: `med_adherence_current`, `med_adherence_provider_weekly`, `med_adherence_org_weekly`, `med_adherence_follow_up_queue`; add smart comments.
9. Add computed columns for PDC percent display.
10. GRANTs to `db_visitor` after policies.
11. Implement `app_private.import_med_adherence(...)` and wire into worker/ingest.
12. Add end‑to‑end tests:

* RLS isolation via org membership changes.
* Idempotent import on the same `(org, import_id)`.
* Follow‑up queue clears after `validation_complete` or expiry.

---

## 10) Indexing and query paths

* RLS and joins: `organization_id` indexed on all org tables.
* Trend charts: `import_id`, `category_code`, and `(organization_id, pdc_rate_bps desc)` for top/bottom.
* Patient drill‑down: `(patient_id, category_code)` and `provider_id`.
* Name resolution: `idx_pharmacies_lower_name` aids pharmacy matching at ingest.

---

## 11) Workflow notes

* Follow‑up 7‑day rule: create a `follow_up` action with `follow_up_expires_at = current_date + 7`. A nightly worker can flag or auto‑close items past expiry if no `validation_complete`.
* Validation complete: either just record the action, or, if policy allows, a small SD function can update the latest snapshot status from the current import to `compliant` when pickup is confirmed. Keep side effects in explicit functions, not in identity helpers.
* Medication stopped: record `medication_stopped` and set most recent snapshot status to `unattained` if business rules require.

---

## 12) CSV field mapping (BCBSTN sample)

* Member ID -> resolve `patients.external_id` -> `patient_id`.
* First/Last/DOB/Phone -> optional assist or creation rules.
* Low‑Income Subsidy? -> `low_income_subsidy` boolean.
* Attr Provider Name -> resolve `providers.external_id` or by name; set `provider_id`.
* Quality Measure -> normalize to `category_code` (e.g., "Statins" -> `statins`).
* Status -> normalize to codes (e.g., "Predicted (Unattainable)" -> `predicted_unattainable`).
* Opportunity -> `med_adherence_opportunities.code` (e.g., "Late to Fill" -> `late_to_fill`).
* Medication -> `medication_key`; optional `ndc_11`.
* Quantity/Days Supply/Remaining Fills -> numeric columns.
* Prescriber Name -> optional `prescriber_provider_id`.
* PDC Rate/Prior Year PDC -> basis‑points (strip "%", multiply by 100).
* Last Fill/Refill Due/Absolute Fail -> date columns.
* Pharmacy + Phone -> resolve to `pharmacies.id` by normalized name; also store raw strings.

---

## 13) Testing

* RLS: in a transaction, set jwt claims to impersonate a user, create org memberships, and verify row visibility changes instantly.
* Import: run `import_med_adherence` twice with same `p_import_id` and confirm upsert behavior and row counts.
* Views: ensure provider/org trend views reflect the latest completed import only.
* Follow‑up queue: add follow\_up, then validation\_complete; verify queue hides item.

---

## 14) Why certain choices

* **Composite FK (category\_code, status\_code)** enforces valid status per category in the database, not in app code.
* **PDC as basis‑points** keeps values numeric and fast in PostGraphile without string casting, while retaining 0.01% precision.
* **Date for follow\_up\_expires\_at** is enough for a weekly cadence and simpler to reason about than timestamptz.
* **Global pharmacies** avoid org duplication and keep linkage org‑scoped in facts.
* **Views with smart comments** give clean GraphQL shapes with no resolvers.

---