Description
It turns out that the below SQL snippets can all be run one after the other to get the whole picture.
Below list might roughly correspond to a PR stack.
1. Add migrations
-- Migration
create table if not exists
ar_user_account_accredited_individuals (
id uuid primary key default gen_random_uuid(),
-- An actual business rule
accredited_individual_registration_number varchar unique not null,
power_of_attorney_holder_type varchar not null,
user_account_email varchar not null,
user_account_icn varchar
);
-- Supports replicating hardcoded table
create unique index if not exists
ar_user_account_accredited_indviduals_hardcoding
on ar_user_account_accredited_individuals (
accredited_individual_registration_number,
power_of_attorney_holder_type,
user_account_email
);
-- An actual business rule
create unique index if not exists
ar_uniq_power_of_attorney_holder_type_user_account_email
on ar_user_account_accredited_individuals (
power_of_attorney_holder_type,
user_account_email
);2. Add replication method
Our sibling team ARM has a process with the same architecture that we can refer to. The code for that is here. It reads from this location in the va.gov-team-sensitive repo.
- We could use a CSV there so it is changed directly from GH web UI
- Some validation will come from DB constraints, but others (like valid
power_of_attorney_holder_typecan come from application code)
-- Periodically replicating hardcoded email registrations table
-- Delete then insert inside a transaction, so others see something atomic
-- taking place.
begin;
with new (
accredited_individual_registration_number,
power_of_attorney_holder_type,
user_account_email
)
-- Just a dynamic example
as (values
('rn-' || now(), 'veteran_service_organization', 'rn@example.com'),
('rn-234', 'veteran_service_organization', 'rn-234@example.com'),
('rn-345', 'veteran_service_organization', 'rn-345@example.com'),
('rn', 'veteran_service_organization', now() || '@example.com'),
('rn-test', 'veteran_service_organization', 'rn-test@example.com')
)
delete from ar_user_account_accredited_individuals
using ar_user_account_accredited_individuals old
left join new using (
accredited_individual_registration_number,
power_of_attorney_holder_type,
user_account_email
)
where
ar_user_account_accredited_individuals.id = old.id and
new.power_of_attorney_holder_type is null
returning
old.accredited_individual_registration_number,
old.power_of_attorney_holder_type,
old.user_account_email
;
with new (
accredited_individual_registration_number,
power_of_attorney_holder_type,
user_account_email
)
-- Just a dynamic example
as (values
('rn-' || now(), 'veteran_service_organization', 'rn@example.com'),
('rn-234', 'veteran_service_organization', 'rn-234@example.com'),
('rn-345', 'veteran_service_organization', 'rn-345@example.com'),
('rn', 'veteran_service_organization', now() || '@example.com'),
('rn-test', 'veteran_service_organization', 'rn-test@example.com')
)
insert into ar_user_account_accredited_individuals (
accredited_individual_registration_number,
power_of_attorney_holder_type,
user_account_email
)
select * from new
on conflict (
accredited_individual_registration_number,
power_of_attorney_holder_type,
user_account_email
) do nothing
returning
accredited_individual_registration_number,
power_of_attorney_holder_type,
user_account_email
;
commit;3. Add user association calculation
This should be a (side-effecting) calculation that is memoized at the beginning of web requests that use this information for authorization. There might be some options on the most appropriate place to put this code, but see step number 5 for more discussion on authorization logic.
Also can see if there is an index that wasn't specified above that would support this query.
update ar_user_account_accredited_individuals
set user_account_icn = 'test-icn'
where accredited_individual_registration_number <> 'rn-test';
-- Authorizing web requests
-- Side-effecting calculation of `registration_number`s
-- Result should be cached by the application during web request
update ar_user_account_accredited_individuals
set user_account_icn =
-- Track/untrack ICN on matching/non-matching emails respectively
case when 'rn-test@example.com' = user_account_email then
'test-icn'
end
where
'rn-test@example.com' = user_account_email or
'test-icn' = user_account_icn
returning
-- And return `registration_number` for records where email matched
case when user_account_icn is not null then
accredited_individual_registration_number
end;4. Install & configure sidekiq job
The replication cron should be defined in /lib/periodic_jobs.rb.
# So that configuration lives in one place, we can template this into cron
# expression. Okay if that only works for values less than 60 minutes, but
# let's comment that if so.
PERIOD = 15.minutes
UNIQUENESS_PERIOD = 5.minutes # Read docs on what `unique_for` does.
QUEUE = :low # Feel free to scrutinize this queue choice.
sidekiq_options(
retry: false,
unique_for: UNIQUENESS_PERIOD,
queue: QUEUE
)5. Authorization logic
With these persisted associations, and given an ARP user account, we will be able to produce the set of digital-POA-request-accepting VSOs to which the user belongs. Knowing that set will let us scope down the set of POA requests the user can act on to those that are associated with the same digital-POA-request-accepting VSOs to which the user is associated.
Note: The extra stipulation of "digital-POA-request-accepting" in the preceding sentence. If this flag can be toggled from on to off, it'd be possible for digital POA requests to exist while the VSO flag is toggled off. Maybe let's assume that can't happen and add a code comment for now. I assume if we really wanted this, we'd be interested in a pausing mechanism that was not this flag.
If there are no such VSOs for a given user, then we would like to interpret and handle that scenario as one in which the user is 403 Forbidden from operating on these resources and accessing these areas of ARP.
This is distinct from how we'd interpret and handle the scenario where the user is associated to at least one digital-POA-request-accepting VSO, but there simply happen not to be any POA requests associated to any of those VSOs. In that case, we want to return empty POA request collections or 404 Not Founds for POA request member actions. I.e., we'd still consider the user authorized to operate on those resources and access those areas of ARP.
6. Observability
- Log (maybe at low levels like
info) the writes that take place in the above- Insertions and deletions when replicating the hardcoded table
- Maybe changed email matches/non-matches in the authorization path if there is a convenient way to get a handle on the pre-update state
7. Tests
Interested in an e2e test that demonstrates 403, 404, 200 in the midst of changes to the relevant data, both the hardcoded list changing, and the user email updating. A single test can walk one subject through each kind of change over time.
Model tests can be used when the level of detail is superfluous to the spirit of the e2e test.