Generated Report
Data Entity
Description
Represents a generated Bufdir export file (PDF or Excel/CSV) for a specific organization and reporting period. Stores the Supabase Storage file reference, triggering user identity, generation timestamp, and export format. Maintains an auditable history of all submitted Bufdir reports for grant funding accountability.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the generated report record. Generated server-side using uuid_generate_v4(). | PKrequiredunique |
organization_id |
uuid |
Foreign key reference to the organization that owns this report. Enforces multi-tenant data isolation via RLS. | required |
report_period_id |
uuid |
Foreign key reference to the report_period entity that defines the date range covered by this export. Ensures traceability between generated files and grant cycles. | required |
triggered_by_user_id |
uuid |
Supabase Auth user ID of the coordinator or org admin who initiated the export. Required for audit trail and accountability. | required |
format |
enum |
The output file format of the generated Bufdir report. Determines which generator service was invoked. | required |
status |
enum |
Lifecycle state of the report generation job. Transitions: pending → generating → completed | failed. | required |
file_storage_path |
string |
Org-scoped path within the Supabase Storage export bucket. Pattern: {org_id}/{report_id}/{filename}. Null until generation completes successfully. | unique |
file_url |
string |
Time-limited signed download URL generated from the storage path. Not persisted permanently — regenerated on demand via export-storage-bucket. Stored transiently for immediate post-generation download. | - |
generated_at |
datetime |
UTC timestamp when the generation job was initiated. Set on record creation and immutable thereafter. | required |
completed_at |
datetime |
UTC timestamp when generation completed (status transitioned to completed or failed). Null while pending or generating. | - |
scope_level |
enum |
The organizational scope level at which the report was generated. Determines which hierarchy nodes were aggregated. | - |
scope_id |
uuid |
The organization unit ID that was the root scope for aggregation. References organization_units.id. Null means entire organization scope. | - |
file_size_bytes |
integer |
Size of the generated file in bytes. Populated on completion for storage monitoring and display in download UI. | - |
error_message |
text |
Human-readable error description when status is 'failed'. Null for non-failed records. Used for admin diagnostics, not shown to end users. | - |
activity_count |
integer |
Snapshot of the total number of activity records included in this export at generation time. Provides a quick consistency check without re-running aggregation. | - |
metadata |
json |
Flexible JSONB bag for generation parameters: bufdir schema version used, column mapping config ID, aggregation RPC version, participant count, deduplication stats. Enables forward-compatibility and audit reproducibility. | - |
Database Indexes
idx_generated_report_org_id
Columns: organization_id
idx_generated_report_org_period
Columns: organization_id, report_period_id
idx_generated_report_triggered_by
Columns: triggered_by_user_id
idx_generated_report_status
Columns: organization_id, status
idx_generated_report_generated_at
Columns: organization_id, generated_at
idx_generated_report_storage_path
Columns: file_storage_path
Validation Rules
valid_organization_id
error
Validation failed
valid_report_period_id
error
Validation failed
valid_format_enum
error
Validation failed
valid_status_transition
error
Validation failed
valid_scope_combination
error
Validation failed
valid_triggered_by_user
error
Validation failed
storage_path_format
error
Validation failed
completed_at_after_generated_at
error
Validation failed
Business Rules
immutable_audit_record
Once a generated_report record reaches status 'completed' or 'failed', core fields (organization_id, report_period_id, triggered_by_user_id, format, generated_at, activity_count) are immutable. This preserves the audit trail required for Bufdir grant accountability. Only status, completed_at, file_storage_path, file_url, and error_message may be updated after creation.
org_scoped_access
All reads and writes on generated_report are filtered by organization_id via Supabase RLS. Coordinators and org admins may only access reports belonging to their own organization. Cross-org access is denied.
single_in_progress_per_period
An organization may not have more than one report in 'pending' or 'generating' status for the same report_period_id simultaneously. A new generation request is rejected if a job is already in progress for that period, preventing duplicate concurrent exports.
storage_path_required_on_completion
When status transitions to 'completed', file_storage_path must be non-null. Any attempt to mark a record as completed without a valid storage path is rejected.
role_restricted_creation
Only users with the 'coordinator' or 'org_admin' role within the target organization may create generated_report records. Peer mentors are not authorized to trigger Bufdir exports.
file_deletion_on_retention_expiry
Generated report files in Supabase Storage are retained permanently for audit purposes. However, signed URLs expire after a configurable window (default 24 hours). The file_url field is not a permanent reference — consumers must request a fresh signed URL via export-storage-bucket.
CRUD Operations
Storage Configuration
Entity Relationships
Each Bufdir export file references the reporting period it covers, ensuring traceability between reports and grant cycles
All Bufdir export files are scoped to the requesting organization for audit trail and re-download purposes