feat(reports): saved-report execution runtime - tickets, equality+IN, whitelisted columns (PMS-477) #344
No reviewers
Labels
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set
Reference
psa-systems/mokosh-server!344
Loading…
Reference in a new issue
No description provided.
Delete branch "feat/PMS-477-report-execution"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
PMS-457 Phase 1 shipped the saved-report DEFINITION CRUD - the rows persisted but were inert because nothing turned them into result data. This patch ships the compiler + executor that closes that loop:
POST /api/v1/reports/saved/{id}/executereads the saved row, compiles itsentity_type/filters/columns/sortJSONB through a per-entity compiler, runs a parameterised SELECT against the chosen entity, and returns rows shaped by the column aliases. Pagination is?page=&per_page=overridable per-request so the SPA can page through a result set without persisting page state on the saved row;per_pageis capped at 10_000 (anything larger should go through the scheduled-delivery path - PMS-478).Tight first cut: tickets entity only (time_entries / invoices / assets follow under the same ticket as a 2c phase). The compiler:
compiler.rs::ticket_columncovering the most-used 25 fields - ticket_number / title / description / source / is_billable / billing_status / created_at / updated_at / closed_at / status_id+name / priority_id+name / queue_id+name / type_id+name / category_name / company_id+name / contact_id+name / assigned_to_id+name / asset_name / created_by_name. Unknown column => 400 naming the offender;TICKET_RESPONSE_SELECTinsrc/modules/tickets/service.rsso the join shape stays in sync;lhs = $N(scalar value) orlhs = ANY($N::TYPE[])(array value) where TYPE comes from the column's whitelist type (uuid / text / bigint / boolean / timestamptz). Empty array, malformed UUID, type mismatch all 400;ORDER BY t.updated_at DESCwhen sort is empty so pagination is stable;columnsarray is empty so a freshly-saved blank report still renders something.Materialisation goes through Postgres
row_to_jsonso the per-column type handling lives in the database rather than in a hand-rolled FromRow for every possible column combo: the executor wraps the compiled SELECT inSELECT row_to_json(s) FROM (...) sand collectsserde_json::Valuerows. The SPA gets{ ticket_number: "...", "Subject": "..." }directly. The response carriesaliases: Vec<String>in declared order so the SPA renders columns in the saved-report's declared order rather than relying on JSON-object key iteration.Visibility:
executerunsgetfirst, so the existing author-or-shared rule applies - a private report authored by another user returns 404 on execute (same posture asget_one), and a shared report executes for any tenant member.Validation matrix has unit tests in
compiler.rs(empty conditions, unknown column, unknown filter field, empty-array filter, malformed UUID, default columns, default sort, explicit sort). Integration test attests/saved_reports_execute.rsdrives the HTTP surface end-to-end: happy path returns rows shaped by columns array, pagination overrides work, unsupported entity / unknown column both 400 with the offender named, private report 404s for non-author on execute, shared report executes for any tenant member.Phase 3 (PMS-478):
scheduled_reportstable + worker that streams large reports to email/S3 on a cron. Out of scope here.#PMS-477
PMS-457 Phase 1 shipped the saved-report DEFINITION CRUD - the rows persisted but were inert because nothing turned them into result data. This patch ships the compiler + executor that closes that loop: `POST /api/v1/reports/saved/{id}/execute` reads the saved row, compiles its `entity_type` / `filters` / `columns` / `sort` JSONB through a per-entity compiler, runs a parameterised SELECT against the chosen entity, and returns rows shaped by the column aliases. Pagination is `?page=&per_page=` overridable per-request so the SPA can page through a result set without persisting page state on the saved row; `per_page` is capped at 10_000 (anything larger should go through the scheduled-delivery path - PMS-478). Tight first cut: tickets entity only (time_entries / invoices / assets follow under the same ticket as a 2c phase). The compiler: * picks the column SQL from a hand-rolled whitelist in `compiler.rs::ticket_column` covering the most-used 25 fields - ticket_number / title / description / source / is_billable / billing_status / created_at / updated_at / closed_at / status_id+name / priority_id+name / queue_id+name / type_id+name / category_name / company_id+name / contact_id+name / assigned_to_id+name / asset_name / created_by_name. Unknown column => 400 naming the offender; * uses the same JOIN tree as the agent `TICKET_RESPONSE_SELECT` in `src/modules/tickets/service.rs` so the join shape stays in sync; * compiles filter values into either `lhs = $N` (scalar value) or `lhs = ANY($N::TYPE[])` (array value) where TYPE comes from the column's whitelist type (uuid / text / bigint / boolean / timestamptz). Empty array, malformed UUID, type mismatch all 400; * defaults to `ORDER BY t.updated_at DESC` when sort is empty so pagination is stable; * defaults to the agent endpoint's most-useful column subset when the saved report's `columns` array is empty so a freshly-saved blank report still renders something. Materialisation goes through Postgres `row_to_json` so the per-column type handling lives in the database rather than in a hand-rolled FromRow for every possible column combo: the executor wraps the compiled SELECT in `SELECT row_to_json(s) FROM (...) s` and collects `serde_json::Value` rows. The SPA gets `{ ticket_number: "...", "Subject": "..." }` directly. The response carries `aliases: Vec<String>` in declared order so the SPA renders columns in the saved-report's declared order rather than relying on JSON-object key iteration. Visibility: `execute` runs `get` first, so the existing author-or-shared rule applies - a private report authored by another user returns 404 on execute (same posture as `get_one`), and a shared report executes for any tenant member. Validation matrix has unit tests in `compiler.rs` (empty conditions, unknown column, unknown filter field, empty-array filter, malformed UUID, default columns, default sort, explicit sort). Integration test at `tests/saved_reports_execute.rs` drives the HTTP surface end-to-end: happy path returns rows shaped by columns array, pagination overrides work, unsupported entity / unknown column both 400 with the offender named, private report 404s for non-author on execute, shared report executes for any tenant member. Phase 3 (PMS-478): `scheduled_reports` table + worker that streams large reports to email/S3 on a cron. Out of scope here. #PMS-477