Deadline as a first-class concept across the UDM

[nate-layman]

Context

Deadlines drive almost every workflow in research administration: proposals, reports, compliance renewals, subaward monitoring, invoicing, effort certification. In the UDM today, deadlines exist only as date fields scattered across individual tables (e.g. RFA.Submission_Deadline, AwardDeliverable.Due_Date, ComplianceRequirement.Expiration_Date, Invoice.Due_Date). There is no unified way to ask the model "what's coming due in the next 30 days?"

Why this matters

• Operational urgency. Missed deadlines have direct consequences: lost proposals, audit findings, expired protocols, late reports. The thing research admins look at most should be queryable in one shot.
• Cross-domain visibility. A PI might have a proposal deadline, a progress report due, and an IRB renewal pending in the same week. Each lives in a different table; there's no view that surfaces them together.
• AI and dashboards. Any assistant or dashboard built on the UDM will need to answer deadline-aware questions repeatedly. Pushing that work into the schema (rather than each consumer reimplementing it) keeps behavior consistent.

Possible designs

1. Reference view first (vw_Upcoming_Deadlines). Union every dated obligation across the model into a single typed view: deadline_date, deadline_type, source_entity_type, source_entity_id, responsible_personnel_id, status. Cheapest, no schema change.

2. Polymorphic Deadline table. A first-class table that any entity can attach deadlines to (similar to how Document works today via Related_Entity_Type + Related_Entity_ID). Adds explicit metadata: Deadline_Type, Reminder_Lead_Days, Is_Recurring, Status, etc. More expressive, more schema surface area.

3. Hybrid. Keep authoritative dates on their owning tables (RFA, AwardDeliverable, ComplianceRequirement, etc.), but add a Deadline table for additional deadlines that don't map cleanly to existing tables (institutional internal deadlines, reminder dates, escalation dates).

Open questions

• Is the goal a queryable view (option 1) or a separately-managed deadline registry (option 2/3)?
• Which existing date fields should be treated as canonical deadlines vs. just historical timestamps?
• Do we need a Reminder or Notification concept alongside, or is that out of scope (workflow tool territory)?
• Should Deadline_Type use AllowedValues (institution-specific) or CHECK (universal taxonomy)?

References

• Existing UDM date fields touched: RFA.Submission_Deadline, RFA.LOI_Deadline, RFA.Pre_Proposal_Deadline, AwardDeliverable.Due_Date, ComplianceRequirement.Expiration_Date, Invoice.Due_Date, Effort.Period_End_Date, Modification.Effective_Date
• Related view: vw_Expiring_Awards, vw_Overdue_Deliverables (both already deadline-shaped but narrow in scope)

[nate-layman]

Expanding scope: deadlines are one face of a bigger temporal-events gap

After filing this I realized the deadline issue is really a symptom of a broader problem. The UDM treats dates as table attributes but doesn't have first-class concepts for:

• Status change history. When did an award move from Pending to Active? When did a proposal go Draft → In Review → Submitted? Right now each table has a single *_Status field showing the current state; the transitions are lost.
• Agreement dates. Distinct from start dates: when was the document signed, when did the sponsor counter-sign, when did the institution accept the terms. These drive obligations independent of period of performance.
• Elapsed time between events. "How long from proposal submission to award notification?" "Time from award start to first transaction?" "Average time in each status?" These analytics depend on having the timestamps preserved as events, not just current states.

Where this overlaps with the deadline issue above

• Both need typed events with a timestamp, an entity reference, and metadata.
• A vw_Upcoming_Deadlines view and a vw_Status_Timeline view would share most of their plumbing.
• The polymorphic option (a Deadline table) might generalize to an Event or MilestoneEvent table that covers status changes, deadlines, agreement signings, and operator-defined milestones.

Open questions added by this expansion

• Do we want a single Event / Milestone table or separate purpose-built tables (StatusChange, Deadline, AgreementEvent)?
• Is ActivityLog (which currently records data-change events for audit) the right place for status-change tracking, or is that semantically different and should stay separate?
• Should the schema enforce that any field with _Status automatically gets transition history written somewhere, or is that an implementation concern?
• What reference views would surface elapsed-time analytics most usefully (proposal → award, signed → first transaction, opened → closed)?