Configure SQL Alchemy ORM to properly use year-based partitioned Notification tables

[MackHalliday]

User Story - Business Need

During the implementation of #95, we identified that SQLAlchemy ORM does not natively support querying year-based partitioned tables directly through ORM objects. This limitation arises because SQLAlchemy does not create distinct ORM objects for partitioned tables—only the parent table is represented.

While PostgreSQL automatically routes queries to the appropriate partitioned table when the filter condition aligns with the partitioning key (created_at), SQLAlchemy ORM lacks a simple, intuitive way to target specific year-based partitions when needed. For instance:

Queries like Notification.where(Notification.created_at >= '2024-01-01', Notification.created_at < '2025-01-01') work and query properly on the partitioned tables because PostgreSQL optimizes them at execution.
However, for more granular control (e.g., explicitly querying notifications_2024), SQLAlchemy does not offer direct support.

• Sync with Kyle when ticket is picked up.
• Ticket is understood, and QA has been contacted (if the ticket has a QA label).

User Story(ies)

As a developer working with SQLAlchemy queries on the Notification table,
I want a straightforward and "graceful" way to query year-based partitioned Notification tables,
So that the application can efficiently and correctly target specific partitions and maintain readability and maintainability in the codebase.

Additional Info and Resources

• A solution using a method to specify the date or attaching a where clause created_at >= DB_PARTITION_DATE (@k-macmillan's suggestion) would allow developers to explicitly query a specific partition table by year while still using SQLAlchemy. Postgres docs mention it is how you prune unwanted partitions.
• Test endpoints GET or POST db/test/ can be updated to test different queries.

Acceptance Criteria

• This work is added to the sprint review slide deck (key win bullet point and demo slide)
• A dao method is implemented that makes use of the partition tables
• Documentation is created in the wiki to explain how to query against year-based partitioned tables
• If testing database is available, tests are added to verify the correctness of queries using the partitioning mechanism.
• Ensure EXPLAIN or query logs confirm that the correct partition is being targeted.
• Ensure edge case is handle where we may need to query for a Notification from a past year. Example. A notification that was created on Dec 31st and now it's January.

QA Considerations

• Queries targeting specific years use the appropriate partition (e.g., notifications_2024).
• Queries without year filters default to the parent table as expected.
• Documentation provides clear examples for developers to follow.

Potential Dependencies

[npmartin-oddball]

@npmartin-oddball Part of SMS wrap epic. Please add.