Award lifetime, proposal UIDs, and the project/account boundary

[nate-layman]

Context

While preparing materials for the AI4RA annual meeting I bumped into several places where the UDM as currently written doesn't cleanly express how research admin offices actually think about the proposal → award → project → account chain. Capturing the questions here so we can address them as a set.

Concerns

1. What's the canonical UID across the lifetime of a funded effort? Working theory: the proposal number is the persistent UID. An office may bid five proposals; one wins. From that moment forward the project's identity is anchored to that proposal. The award number from the sponsor can change (competing vs. non-competing continuations, supplements), but the proposal that started the work doesn't.

2. FAIN should persist for the whole lifetime of the award. Per NCURA best practice (Research Administration Handbook), the first funded award number, the federal FAIN, must be queryable across every modification, continuation, and supplement. Right now the schema captures Award.Award_Number and a Modification table, but there's no obvious way to ask "give me everything associated with this FAIN from inception through closeout."

3. Competing vs. non-competing continuation. These have different downstream behavior (new award number on competing renewals; same award number on non-competing). The UDM should distinguish them, probably via the Modification.Event_Type AllowedValues plus an explicit field for "is this a new competing segment?"

4. Finance/accounting numbers are separate from sponsor numbers.

   • Speed type = account type in PeopleSoft
   • Project number = subaccount number on the institution side
   • Neither is the same as the sponsor's award number or the FAIN
   • The current FinanceCode table connects funds + accounts + awards but doesn't explicitly carry a label for these institutional identifiers
   • We need a clean separation between "what the sponsor calls this" and "what the institution calls this."

5. First transaction = onset of award lifetime. The audit/reporting view should anchor the start of an award's lifetime to whatever first transaction posts against it. Today there's no derived field or view that surfaces this.

6. "Sponsored project" ≠ "research." The Project table's Project_Type (AllowedValues) handles this implicitly, but the language in vignettes and the dashboard sometimes uses "research" where "sponsored" is more accurate. Worth a docs pass and possibly a CHECK-constraint-level reminder that the schema is for sponsored work, of which research is one category.

Open questions for design

• Should Award get an explicit FAIN column distinct from Award_Number?
• Should Modification get a Continuation_Type field (Competing / Non-Competing / Supplement / Other) at the table level rather than as an AllowedValue?
• Do we need a vw_Award_Lifetime reference view that unions the original award + all modifications + transactions by FAIN?
• Should institutional identifiers (speed type, project number, subaccount) live on FinanceCode or on a new AccountingIdentifier table tied to Award?

References

• NCURA Research Administration Handbook, best practices on award lifecycle and identifier persistence
• Existing UDM tables touched: Proposal, Award, Modification, FinanceCode, Fund, Account, Transaction, Project