Bufdir Report
Data Entity
Description
Generated report snapshots containing aggregated activity and participant statistics for Bufdir grant compliance submission. Scoped to an organization and reporting period; may be exported as PDF, CSV, or submitted directly via Bufdir API.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Primary key — unique identifier for each generated report snapshot | PKrequiredunique |
organization_id |
uuid |
Foreign key referencing the organization that owns this report. All aggregated data in report_data is scoped exclusively to this organization | required |
period_id |
uuid |
Foreign key referencing the report_period that defines the date window for data aggregation. One period may have multiple versioned reports if re-generated | required |
generated_at |
datetime |
UTC timestamp when report generation was completed and data was committed. Null while status is pending or generating | - |
generated_by |
uuid |
User ID of the coordinator or administrator who triggered report generation | required |
status |
enum |
Lifecycle state of the report: pending (queued), generating (in progress), completed (data aggregated, ready for export), failed (generation error), submitted (delivered to Bufdir API) | required |
report_data |
json |
Bufdir-schema-compliant aggregated payload containing activity counts by type, participant counts, hours totals, event statistics, and peer mentor breakdown. Populated by Bufdir Report Generator Service upon completion | - |
export_format |
enum |
The output format selected for export or submission. Null until the user selects a format after completion | - |
submission_id |
string |
External reference ID returned by Bufdir API on successful submission. Null for exported-only reports. Used to query submission status from Bufdir | - |
submitted_at |
datetime |
UTC timestamp when the report was successfully submitted to the Bufdir API. Null for reports that were only exported locally | - |
export_file_path |
string |
Supabase Storage path to the exported PDF, CSV, or Excel file. Null until export is triggered. Signed URLs are generated transiently from this path | - |
generation_job_id |
string |
Reference identifier for the async Edge Function job executing aggregation. Used to poll generation status and cancel in-progress jobs | - |
error_message |
text |
Human-readable error description populated when status is failed. Includes the failing aggregation step and error type for debugging | - |
report_version |
integer |
Monotonically incrementing version number per organization+period combination. Allows re-generation without overwriting prior snapshots. Starts at 1 | required |
activity_count |
integer |
Denormalized count of total activities included in this report snapshot. Stored for quick display in report history lists without parsing report_data | - |
participant_count |
integer |
Denormalized count of unique participants (contacts and event attendees) across all activities in the reporting period | - |
hours_total |
decimal |
Denormalized total hours of peer mentor activity logged in the reporting period. Stored with two decimal places for Bufdir compliance | - |
bufdir_schema_version |
string |
Version string of the Bufdir report schema used during generation (e.g. '2024-v2'). Tracks schema compatibility across API version changes | - |
is_latest_version |
boolean |
Flag indicating whether this is the most recently generated version for this organization+period combination. Only one record per org+period should have this true | required |
Database Indexes
idx_bufdir_reports_org_period
Columns: organization_id, period_id, report_version
idx_bufdir_reports_organization_id
Columns: organization_id
idx_bufdir_reports_period_id
Columns: period_id
idx_bufdir_reports_status
Columns: status
idx_bufdir_reports_generated_at
Columns: generated_at
idx_bufdir_reports_org_latest
Columns: organization_id, is_latest_version
idx_bufdir_reports_generated_by
Columns: generated_by
Validation Rules
valid_organization_reference
error
Validation failed
valid_period_reference
error
Validation failed
valid_status_transition
error
Validation failed
report_data_schema_compliance
error
Validation failed
non_negative_aggregates
error
Validation failed
export_file_path_on_export
error
Validation failed
report_version_monotonic
error
Validation failed
generated_by_active_user
error
Validation failed
Business Rules
org_data_isolation
All data aggregated into report_data must be strictly scoped to the generating organization. Cross-organization data leakage is a Bufdir compliance violation. Enforced via Supabase RLS and server-side scoping in the generator service
single_active_version_per_period
When a new report is generated for an org+period combination that already has a completed report, the previous report's is_latest_version flag must be set to false before the new report is finalized. Only the newest version is shown by default in the UI
submitted_report_immutable
Reports with status 'submitted' cannot be deleted or have their report_data modified. They form part of the Bufdir audit trail. Administrators may only add annotations; core data is frozen
coordinator_or_admin_generation_only
Only users with coordinator or admin role within the target organization may trigger report generation. Peer mentors cannot initiate Bufdir reports
submission_id_required_on_submit
When status transitions to 'submitted', submission_id and submitted_at must both be populated with the Bufdir API response values. A submission without a confirmation ID is treated as failed
period_must_belong_to_organization
The period_id referenced must be a report_period record whose organization_id matches the report's organization_id. Cross-organization period references are rejected
completed_period_required_for_generation
A report can only be generated for a reporting period whose end_date is in the past. Generating a report for a future or in-progress period is blocked to prevent incomplete data submission
CRUD Operations
Storage Configuration
Entity Relationships
Each Bufdir report is generated for a specific defined reporting period
An organization generates multiple Bufdir reports across different reporting periods