Plan Groups

Plan groups are a really useful abstraction (especially for queries) but require maintenance as new plans are defined.

Plan groups are defined by a set of queries in oat3/sql/operations/planGroups/.
These queries update tables in the database.

Advice:

  • It’s best to do this against an up-to-date dev database first. That’s been hard to come by so it’s typically done with prayers against the production database.
  • New plans (not in a plan group) are reported in the automatic morning “OAT Health” email. Don’t let the list get too long! The current methodology does not let you work on a subset. If the list gets too long, things get confusing.

Here are the basic pieces:

  • list_plan_groups.sql: A query that lists relevant stuff that is useful in checking your work as you make changes. It shows one line for each edge in the plan group tree. Edges between interior nodes have no other information. Edges that connect to a leaf (i.e. a plan) show information about the plan.
  • create_pps_plan_info.sql: A query that creates the _oat.pps_plan_info table. This table is a trusted sourse of information about plans.
  • create_plan_groups.sql: A query that constructs plan groups based on _oat.pps_plan_info but with overrides to adjust the tree by adding extra divisions or overriding some information in plan info.

Adding new plans:

  1. psql service=??? < list_plan_groups.sql > DATE_0.txt: Establish a baseline of the current system before you do anything. Replace DATE with today’s date.
  2. psql service=??? < create_pps_plan_info.sql and then psql service=??? < create_plan_groups.sql. This creates the new/revised plan groups based on the info in _quest. It’s good maybe 80% of the time.
  3. Repeat until everything is correct:
    1. psql service=??? < list_plan_groups.sql > DATE_n.txt where DATE it today’s date and n is an increasing index number. This gives you a text file with the critical information on the whole set of plan groups as they just got revised.
    2. Use a good quality visual diff program to compare DATE_0.txt and DATE_n.txt. Do all of the differences make sense in light of what you just did? If yes, you’re done. If not, edit create_pps_plan_info.sql to make things right.

Adding new Alternate trees

This work is done in create_plan_groups.sql.