Database Schema: Auto Report

This document details the database structure for the Auto Report module.

---

1. Overview

The Auto Report module uses a PostgreSQL database with Drizzle ORM. It stores report configurations, generation history, and snapshots of data used for generation.

Core Domain Model

---

2. Table Definitions

report_templates

Definitions for recurring automated reports.

Field	Type	Description
id	uuid	PK.
org_id	uuid	Organization scope.
name	text	Display name.
sources	jsonb	Modules and endpoints to query (e.g., ["hrm/employees", "drive/files"]).
system_prompt	text	Custom prompt logic for the LLM.
schedule_cron	text	Standard cron string for automation.

execution_logs

Tracking history for every run (automated or manual).

Field	Type	Description
id	uuid	PK.
template_id	uuid	FK to report_templates.id.
status	enum	pending, running, success, error.
error_message	text	Capture details if a run fails.

report_results

The final generated content and its context snapshot.

Field	Type	Description
id	uuid	PK.
execution_id	uuid	FK to execution_logs.id.
content_markdown	text	The AI-generated narrative report.
data_snapshot	jsonb	Frozen JSON copy of input data for reproducibility.

---

3. Data Retention Logic

To manage storage usage, the report_results table implements a time-to-live (TTL) logic (planned for v1.1). Results older than the organization's retention limit will be deleted.

-- Planned cleanup query
DELETE FROM report_results
WHERE id IN (
  SELECT r.id FROM report_results r
  JOIN execution_logs e ON r.execution_id = e.id
  WHERE e.started_at < NOW() - INTERVAL '90 days'
);