Database¶

Engine and connections¶

All verified databases are PostgreSQL:

the Jasper application database stores the five tables below;
the external Central database stores sessions, users, groups, resources, and permissions;
each configured report datasource is also connected with the PostgreSQL PHP extension/JDBC driver.

PHP uses ordinary pg_connect() calls and closes many endpoint/runner connections explicitly. It does not configure persistent PHP connections or an application-side pool. Generated Tomcat JNDI resources use the Tomcat JDBC pool with maxTotal=20, maxIdle=10, maxWaitMillis=10000, validationQuery=SELECT 1, and testOnBorrow=true.

Application schema¶

database/schema.sql creates exactly five tables in public.

erDiagram
    JASPER_SCHEDULE ||--o{ JASPER_SCHEDULE_EXECUTION : "schedule_id; ON DELETE SET NULL"
    JASPER_DATASOURCE {
        serial id PK
        varchar name UK
    }
    JASPER_ACCESS_GROUP {
        serial id PK
        varchar name UK
    }
    JASPER_SCHEDULE {
        serial id PK
        varchar report_name
        varchar datasource_name
    }
    JASPER_RECIPIENT {
        serial id PK
        varchar email
    }
    JASPER_SCHEDULE_EXECUTION {
        serial id PK
        integer schedule_id FK
    }

There are no foreign keys from schedules to datasource names, report files, recipients, access groups, or templates.

Tables¶

`jasper_datasource`¶

Column	Type	Default/constraint
`id`	`serial`	Primary key
`name`	`varchar(80)`	Required, unique
`host`	`varchar(250)`	Required
`port`	`integer`	Required, `5432`
`dbname`	`varchar(120)`	Required
`username`	`varchar(250)`	Required
`password`	`varchar(250)`	Required, empty string
`schema_name`	`varchar(80)`	Required, `public`
`enabled`	`boolean`	Required, `true`
`created_at`	`timestamp without time zone`	Required, `now()`

Enabled rows generate Tomcat JNDI resources. Passwords are stored and written to XML without encryption.

`jasper_access_group`¶

Column	Type	Default/constraint
`id`	`serial`	Primary key
`name`	`varchar(120)`	Required, unique

Schema initialization inserts Public idempotently. This table supplies report-definition metadata choices; runtime ACLs use Central auth_groups, not this table.

`jasper_schedule`¶

Column	Type	Default
`id`	`serial`	Primary key
`cron_period`	`varchar(40)`	`never`
`cron_custom`	`varchar(120)`	empty
`report_name`	`varchar(120)`	required, no default
`datasource_name`	`varchar(80)`	empty
`format`	`varchar(20)`	`pdf`
`filename`	`varchar(250)`	empty
`email`	`text`	empty
`email_subject`	`varchar(500)`	empty
`email_body`	`text`	empty
`email_template`	`varchar(120)`	empty
`noemail`	`boolean`	`false`
`url_opt_params`	`text`	empty
`enabled`	`boolean`	`true`
`created_at`, `updated_at`	`timestamp without time zone`	`now()`

Important mismatch: code conditionally supports schedule_mode, repeat_minutes, hourly_minute, daily_time, weekly_day, weekly_time, monthly_day, monthly_time, and once_datetime only if schedule_mode already exists. schema.sql creates none of them. With the supplied schema, schedule writes fall back to legacy cron_period/cron_custom; mode-specific time fields are not persisted, so daily/weekly/monthly/once behavior can lose the selected values or fail to produce the intended timer.

`jasper_recipient`¶

Column	Type	Default/constraint
`id`	`serial`	Primary key
`name`	`varchar(120)`	Required
`email`	`varchar(250)`	Required; case-insensitive uniqueness enforced by index
`notes`	`text`	Required, empty string
`created_at`	`timestamp without time zone`	Required, `now()`
`updated_at`	`timestamp without time zone`	Required, `now()`

`jasper_schedule_execution`¶

Column	Type	Default/constraint
`id`	`serial`	Primary key
`schedule_id`	`integer`	Nullable FK to `jasper_schedule(id)`, `ON DELETE SET NULL`
`status`	`varchar(40)`	Required, `pending`
`started_at`	`timestamp without time zone`	Required, `now()`
`finished_at`	`timestamp without time zone`	Nullable
`output_file`	`text`	Required, empty string
`email_status`	`varchar(80)`	Required, empty string
`error_message`	`text`	Required, empty string

A run inserts status running; success/failure updates the row.

Indexes¶

Name	Definition
Implicit primary/unique indexes	Every primary key; datasource name; access-group name
`jasper_recipient_email_lower_idx`	Unique on `lower(email)`
`jasper_schedule_execution_schedule_idx`	`(schedule_id, started_at DESC)`

No index is supplied for schedule enablement/timing, execution status, or datasource enablement.

Initialization¶

schema.sql uses CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, and an idempotent Public insert. It does not create the database, role, grants, non-public schema, extensions, Central tables, or example datasource rows.

JRS_DB_SCHEMA can redirect runtime queries to another schema, but the checked-in SQL is hardcoded to public; edit or set the SQL search/qualification deliberately when using another schema.

Migrations and compatibility¶

No migration framework, migration directory, schema-version table, or scheduler-column migration exists. jrs_schedule_ensure_schema() is an empty function. Applying schema.sql to an existing database does not alter old tables or add missing columns.

Before production scheduling, create and review an explicit migration for the conditional fields or constrain use to verified legacy timer behavior. Back up both PostgreSQL and filesystem report/schedule data before schema changes.

External Central schema¶

Authentication and authorization query Central tables:

dashboard_sessions, dashboard_users
auth_groups, auth_group_members
resources, resource_permissions

Their DDL, indexes, migrations, and ownership are not included here. Resource synchronization performs idempotent resource upserts and conditional default permission grants; it is not an application-schema migration.