6238_Att H Illustrative Backend Infrastructure.docx

Attachment H – Illustrative Backend Infrastructure Illustrative Backend Infrastructure Purpose: This attachment presents a conceptual model of the backend data structures that could support a Smartsheet‑based framework for managing MHBG and SUPTRS block‑grant activity. It describes how source data may be organized, transformed, and linked to enable consistent financial tracking, program‑area classification, compliance monitoring, and phase‑management functions. The material is nonbinding and is intended to establish a shared understanding of the data environment and structural considerations that will guide the contractor’s design work and inform solution development.

Table of Contents

A. Source/Input Sheets (raw data entering the system)

(1) Active Contract Action Tracker Sheet Description: This sheet brings in contract actions that are still moving through the approval process and that use MH or SUPTRS block grant dollars. It pulls this information directly from ‘BHD’s 2022 Active Request Tracker’ in Smartsheet. Only rows that include block grant funding are kept.

This sheet provides things like contract numbers, amounts, dates, fund sources, and staff assignments. It gives the system a current view of what new or amended contracts are being worked on, even before they are fully executed.

Filter rule: Include a row only if MH‑SUPTRS Block Grant Funded is checked (true).

Load method: Smartsheet Data Mesh (source → filtered sheet), preserving row comments.

(2) Completed Contract Action Tracker Sheet Description: This sheet brings in contract actions that have already been fully approved and executed. It pulls directly from BHD’s ‘2022 Completed Request Tracker’ in Smartsheet and keeps only the contracts that use block grant funds.

Once a contract action is complete, the information here becomes part of the system’s permanent contract record. This sheet provides finalized details about contract amounts, dates, fund sources, and who is responsible for the contract.

Filter rule: Include a row only if MH‑SUPTRS Block Grant Funded is checked (true).

Load method: Smartsheet Data Mesh (source → filtered sheet), preserving row comments.

(3) Invoice Registry Sheet Purpose: This sheet is a log of all invoices that have been approved for payment. Staff manually enter invoice information here, including invoice number, contractor, amount, approval date, and the funding codes that apply.

The system uses this sheet to:

· Link planned payments to real invoices

· Track expenditures for contracts that require invoice-based payments It’s the system’s source of truth for “what has been invoiced and approved.”

(4) Payment Registry (SFMA) Sheet Purpose: This sheet stores actual payment transactions that have posted in the state’s accounting system (SFMA). These entries are imported directly from SFMA, not entered by hand.

This sheet shows:

· How much money has actually gone out the door

· Which contractor was paid

· When the payment was posted

· The SFMA coding that identifies the grant and fund category It is the system’s source of truth for cash expenditures, including:

· County RBASE payments (which don’t have invoices)

· Administrative costs paid directly through SFMA Notes: Payments recorded here contribute to Fund Category Actuals only for expenses without invoices (RBASE or Fund Categories that are Administration-coded).

(5) Grant Allocation Table Sheet Description: This is a simple reference table that lists each SAMHSA grant (by Grant Number) and the total amount awarded for that grant. It also records which federal fiscal year the grant belongs to.

The system uses this table to understand:

· How much money is available in each grant

· Which Grant Phase each pot of funding represents

(6) Funding Category Crosswalk Sheet Description: This table links the accounting codes used in SFMA (Grant Number, PCA, and sometimes CurDoc) to their corresponding SAMHSA reporting categories. It ensures the system can correctly identify which Program Area a payment belongs to.

In short, this sheet translates state accounting codes into the SAMHSA categories we’re required to report on. It’s the backbone for:

· Program Area classification

· Compliance tracking

· Automatic categorization of invoices and payments

BTC. Should be left blank for all other types of Fund Categories.

Invoice-Less Category

(7) Funding Allocations & Payment Rules Sheet Description: This sheet defines the rules for how each contract should be paid. For each contract or contract action, it lays out:

· How much funding is tied to the action

· Whether payments are fixed amounts or vary over time

· Whether payments occur monthly, quarterly, one‑time, or based on invoices

· The beginning and end dates for when payments should occur

· Which fund category the money should come from This sheet also contains the Phase Switch controls staff use when one fund category is nearly spent and payments must move to the next year’s pot of money. When staff check “Switch Phase Now,” the system automatically updates upcoming scheduled payments.

In short, this sheet is the system’s rulebook for when, how, and from which funding source payments should happen.

No

Switch Phase Now

User‑controlled trigger that initiates a phase switch for this payment rule. When checked, all future scheduled disbursements (those with Schedule Date ≥ TODAY()) in the Expected Disbursement Schedule are reassigned to the New Fund Category ID selected by the user. After the switch is completed, the checkbox automatically resets to unchecked so additional switches may be made later.

Expected Disbursement Schedule → Payment Rule ID, Fund Category ID, Prior Fund Category ID, Date New Fund Category ID Assigned, Switch Sequence #, Schedule Date

Contingent on a Phase Switch

Funding Category Crosswalk → Fund Category ID Derived from the next Schedule Date ≥ TODAY() in Expected Disbursement Schedule (EDS).

If there are no future EDS rows, do nothing.

Set Switch Count = Switch Count + 1.

If there are no future Expected Disbursement Schedule rows, do not increment.

Remains unchanged until the next switch.

Returns the Program Area (MHBG or SUPTRS) associated with that Fund Category in the Crosswalk.

MHBG; SUPTRS

Funding Category Crosswalk → Current Document

* When Switch Phase Now changes from unchecked → checked, an automation should occur in this order:

1. Pre‑check:

· If New Fund Category ID is blank, automation immediately unchecks Switch Phase Now and alerts the user that a New Fund Category ID must be selected before switching.

2. If valid:

· Identify all Expected Disbursement Schedule rows where:

· Payment Rule ID matches this row, and

· Schedule Date ≥ TODAY().

· For each such row:

· Set Prior Fund Category ID = existing Fund Category ID

· Set Fund Category ID = New Fund Category ID

· Set Date New Fund Category ID Assigned = TODAY()

· Set Switch Sequence # = Switch Count + 1

3. Update metadata on this Payment Rule row:

· Switch Count = Switch Count + 1

· Last Switch Date = TODAY()

· Current Fund Category ID = New Fund Category ID

4. Post‑switch reset:

· Automatically uncheck Switch Phase Now to allow additional future switches.

B. Core Transformation Sheets

(1) Active Contract Master Record Sheet Description: This sheet creates a single, clean summary for every active contract that uses MHBG or SUPTRS funds. It pulls together all completed contract actions for a contract and combines them into one record.

It standardizes things like contractor name, contract dates, total dollar limits, and assigned staff so that every contract has one authoritative set of details. Only contracts that are still active (not yet past their expiration date) are included.

This sheet essentially answers: “What is the current official information for each active contract?”

Return all unique Program Areas for the contract using:

=JOIN(DISTINCT(COLLECT({FundingRules Program Area}, {FundingRules Contract No}, [Contract No]@row)), ", ")

(2) Expected Disbursement Schedule Sheet Description: This sheet turns the payment rules into a detailed payment schedule, one row per expected payment. It predicts:

· When each payment is due

· How much it should be

· Which fund category it should charge

· Which Program Area it will count toward If a Phase Switch happens, only future payments are updated—past ones stay unchanged so you have a clear audit trail.

This sheet provides the month‑by‑month “spend plan” that shows the system’s best estimate of what spending will look like over time.

Assigned automatically when expected disbursement rows are generated from the corresponding payment rule

Assigned sequentially (1, 2, 3, …) based on the installment generation logic for the rule

If Payment Type = Fixed:

One‑time:

Schedule Amount = Total Amount Governed by Rule Monthly:

Schedule Amount = Fixed Payment Amount Quarterly:

Schedule Amount = Fixed Payment Amount If Payment Type = Variable:

Total Amount Governed by Rule × (Number of days in this scheduled period ÷ Total number of days in the entire contract performance period )

Funding Category Crosswalk → Fund Category ID and Funding Category for the Next Phase

Assigned at row creation using the Funding Allocations & Payment Rule’s Current Fund Category ID.

When a phase switch occurs, only rows with Schedule Date ≥ TODAY() are updated.

For such rows, this value is reassigned to the value of ‘Fund Category ID for the Next Phase’ from the Funding Category Crosswalk.

Funding Category Crosswalk → SAMHSA Funding Category

This Sheet → Fund Category ID

If ‘Other Capacity Building/ Systems Development Activity?’ = TRUE for the parent Payment Rule → set Program Area = “Other Capacity Building / Systems Development”.

Otherwise → Program Area = lookup from the Funding Category Crosswalk using this row’s Fund Category ID.

Other Capacity Building/ System Development, + Other Valid Program Areas per Funding Category Crosswalk

Yes

If Schedule Date month ≥ October → FFY = YEAR(Schedule Date) + 1 Otherwise → FFY = YEAR(Schedule Date)

Funding Allocations & Payment Rules → Current Fund Category ID

Populated only when a Switch Phase Now event occurs.

Stores the previous Fund Category ID for audit history

Remains blank if no reattribution to a new Fund Category ID has occurred.

Set to the value of Switch Count + 1 from the Funding Allocations & Payment Rules sheet after the Switch Phase Now box is checked.

C. Output/ Roll-Up Sheets (Dashboard-facing data)

(1) Fund Category Balances Sheet Description: This sheet shows the big‑picture financial status for each fund category across the block grants. For every fund category, it brings together:

· How much has been planned for future payments

· How much has actually been spent

· How much funding is still available It uses both invoices and payment data to calculate actual spending, and it knows when to use one or the other (for example, RBASE and administrative payments don’t have invoices). This sheet is the foundation for understanding financial health at the “fund category” level.

Note: Actuals pull from the Invoice Registry whenever invoices exist; Payment Registry amounts contribute to actuals only for expenses that do not have invoices (payments processed through the RBASE system and Administration-coded payments).

Direct lookup; one row per Fund Category ID

Direct lookup; no calculation

Funding Allocations & Payment Rules → Other Capacity Building/ Systems Development Activity?

If any Payment Rule associated with this Fund Category ID has OCB/SDA Override? = TRUE, then Program Area = “Other Capacity Building / Systems Development Activities.”

Otherwise, Program Area = the value mapped to this Fund Category ID in the Funding Category Crosswalk.

Other Capacity Building/ System Development, + Other Valid Program Areas per Funding Category Crosswalk

Yes

Direct lookup; no calculation

Direct lookup; no calculation

Direct lookup; no calculation

Expected Disbursement Schedule → Schedule Amount, Schedule Date, Fund Category ID SUM of Schedule Amounts in the Expected Disbursement Schedule where Fund Category ID = this row’s Fund Category ID, and Schedule Date ≥ TODAY()

Invoice Registry → Amount, Fund Category ID Payment Registry → Amount, Fund Category ID Funding Allocations & Payment Rules → Invoice Required?, Contract No, Fund Category ID

SUM of Invoice Registry Amounts where Fund Category ID = this row’s Fund Category ID; AND Invoice Required? =TRUE in Funding Allocations & Payment Rules (lookup based on Contract No and Fund Category ID;) AND Program Area in the Funding Category Crosswalk ≠ ‘Primary Prevention’ SUM of Payment Registry Amounts where Fund Category ID = this row’s Fund Category ID; and [Invoice Required? = FALSE in Funding Allocations & Payment Rules (lookup based on Contract No and Fund Category ID); OR Program Area in the Funding Category Crosswalk = ‘Primary Prevention’]

(2) Program Area Balances Sheet Description: This sheet adds up information from multiple fund categories to show the financial status for each Program Area, such as Primary Prevention, Treatment & Recovery, Crisis, EASA, or Administration.

It also checks whether each Program Area is meeting SAMHSA’s required minimums or staying under maximum spending limits. It pulls together:

· Actual spending

· Planned future spending

· Total obligated funds (actual + planned)

· Compliance status

· Remaining dollars This sheet helps staff quickly see whether each Program Area is on track or at risk of non‑compliance.

Note: Actuals pull from the Invoice Registry whenever invoices exist; Payment Registry amounts contribute to actuals only for expenses that do not have invoices (payments processed through the RBASE system and Administration-coded payments).

Return each distinct Program from Fund Category Balances

Return the Grant Type associated with all Fund Category IDs belonging to this Program Area. Must be the same for all categories in the Program Area.

=JOIN(DISTINCT(COLLECT({Fund Category ID}, {Program Area}, ProgramArea@row)), ", ")

Valid Fund Category IDs in Funding Category Crosswalk

Yes

Planned Total (Program Area)

Total planned future disbursements across all Fund Category IDs in this Program Area.

SUM of Planned Total for all Fund Category IDs where Program Area = this Program Area.

SUM of Actual Spent for all Fund Category IDs where Program Area = this Program Area.

Total Obligated (Program Area)

Total of actual expenditures plus all future planned disbursements for this Program Area.

Currency

Auto

This sheet → Actual Spent, Planned Total

= Actual Spent + Planned Total

Program Area Minimum %

Compliance minimum percentage for this Program Area, when applicable.

If all included Fund Categories share the same Minimum %, return that value; else leave blank (not applicable).

Program Area Maximum %

Compliance maximum percentage for this Program Area, when applicable.

Same rule as Minimum %: return the Program Area-level maximum if applicable.

If Minimum % exists: (Grant Total Allocation for this Grant Type × Minimum %). If no minimum, blank.

If Maximum % exists: (Grant Total Allocation × Maximum %). If no maximum, blank.

Checked if Total Obligated ≥ Min Dollars Required.

Checked if Total Obligated > Max Dollars Allowed.

Narrative Status (Program Area)

Human‑readable summary of Program Area status (e.g., on track, approaching compliance risk).

If Exceeds Max → “Exceeds Maximum Threshold”; Else if Not Meeting Min → “Below Minimum Requirement”; Else → “In Compliance.”

(3) Program Area Spend Plan Dashboard Source Sheet Purpose: This sheet prepares Program Area information in a dashboard‑ready format. Each row represents one Program Area within one Grant Phase.

It brings together everything leadership needs for monitoring, including:

· Actual spending to date

· Upcoming planned spending

· Whether the Program Area is getting close to the end of its available funds

· Whether it is meeting SAMHSA requirements

· A narrative status like “On Track,” “Approaching Phase End,” or “Switch Required Soon” This sheet essentially feeds the main Program Area spend‑plan dashboard used by program leadership.

Program Area

The SAMHSA Program Area represented by this row. One row per Program Area per Grant Phase.

Direct lookup

Valid Program Areas

Yes

Direct lookup

MHBG; SUPTRS

Yes

Grant Phase

The FFY associated with this Program Area’s Fund Category IDs.

Return the Grant Phase associated with Fund Category IDs in this Program Area.

Two‑digit FFY

Yes

Included Fund Category IDs

List of all Fund Category IDs that roll up to this Program Area.

Direct lookup

Actual Spent (Program Area)

Direct lookup

Yes

SUM Schedule Amount where Program Area = this Program Area AND TODAY() ≤ Schedule Date ≤ FFY-end.

Yes

SUM Schedule Amount where Program Area = this Program Area AND Schedule Date ≥ TODAY().

Yes

Total Obligated (Program Area)

= Actual Spent + Planned Remaining (All Future)

Yes

Direct lookup

Conditional

Direct lookup

Conditional

If Program Area has min/max %, derive allocation from Grant; else, sum allocations from included Fund Category IDs.

Yes

= MAX(0, 1 – (Total Obligated ÷ Program Area Allocation))

Checked if Total Obligated ≥ Min Dollars Required

Compliance – Exceeds Maximum?

Indicates whether obligated dollars exceed the Program Area maximum allowed.

Checked if Total Obligated > Max Dollars Allowed

Switch Soon?

Indicates whether this Program Area is within its final 5% of available dollars.

Checked if Program Area Remaining % < 5%

If Exceeds Max → “Exceeds Maximum Threshold”; Else if Not Meeting Min → “Below Minimum Requirement”; Else if Program Area Remaining % < 5% → “Switch Required Soon”; Else if Program Area Remaining % < 15% → “Approaching Phase End”; Else → “On Track.”

On Track; Approaching Phase End; Switch Required Soon; Below Minimum; Exceeds Maximum

No

(4) Grant-Level Spend Plan Source Sheet Description: This sheet rolls everything up one level further — to the entire grant. For each SAMHSA grant, it summarizes:

· All actual spending so far

· All future scheduled spending up to the grant’s expiration

· How much of the grant has been obligated

· How much remains available

· Whether the grant overall is nearing the end of its funding It also produces grant‑level pacing messages like “On Track” or “Switch Required Soon.” This sheet supports leadership’s top‑level view of how each grant as a whole is performing.

Direct lookup

Direct lookup

= DATE([Grant Expiration FY], 9, 30)

Yes

=JOIN(DISTINCT(COLLECT({Crosswalk Fund Category ID},{Crosswalk Grant No}, [Grant Number]@row)), ", ")

Yes

Funding Category Crosswalk → Grant No = SUM of Actual Spent (Fund Category) for all Fund Category IDs belonging to this grant.

Yes

SUM Schedule Amount from the Expected Disbursement Schedule where Grant Number = this grant AND TODAY() ≤ Schedule Date ≤ Grant Expiration Date

Yes

= [Actual Spent (Grant)] + [Planned Remaining (to Expiration, Grant)]

Yes

Yes

= MAX(0, 1 − (Total Obligated (Grant) ÷ Total Allocated (Grant)))

Checked when Grant Remaining % < 5%.

Text

This sheet → Grant Remaining %

If <5% ⇒ “Switch Required Soon”; else if <15% ⇒ “Approaching Phase End”; else ⇒ “On Track”

On Track; Approaching Phase End; Switch Required Soon

No

Yes

(5) Contract x Program Area Dashboard Source Sheet Description: This sheet provides a dashboard‑ready operational view of spending activity for each Contract × Program Area combination funded by the block grants. Each row represents one active contract broken out by Program Area, allowing program staff to monitor performance at the level where spending decisions are actually made.

The sheet displays:

· Obligated dollars and actual expenditures for the Program Area under that contract

· Whether spending for that Program Area is pacing faster or slower than expected

· Issue indicators such as incorrect fund category charges, invoice‑to‑payment mismatches, overspending, or contracts approaching expiration

· The Program Area’s quarterly burn rate This sheet serves as an early‑warning and oversight tool for contract managers and program leadership, helping them identify risks, confirm proper coding, and understand how each contract is performing within its assigned Program Areas.