Notifications and reports

SMTP or ntfy channels (tenant or global) with encrypted credentials and the events they receive.

11 columns

Column	Type	Null	Default	References
`id` PK	`bigint(20) unsigned`
`tenant_id`	`bigint(20) unsigned`	yes		`tenants` on delete cascade
`name`	`varchar(255)`
`type`	`varchar(16)`
`config`	`text`
`events`	`longtext`
`enabled`	`tinyint(1)`		`1`
`last_sent_at`	`timestamp`	yes
`last_error`	`text`	yes
`created_at`	`timestamp`	yes
`updated_at`	`timestamp`	yes

Indexes: tenant_id

Sent notifications with their channel, event and result.

7 columns

Column	Type	Null	Default	References
`id` PK	`bigint(20) unsigned`
`notification_channel_id`	`bigint(20) unsigned`			`notification_channels` on delete cascade
`event`	`varchar(32)`
`subject`	`varchar(255)`
`status`	`varchar(16)`
`error`	`text`	yes
`created_at`	`timestamp`		`current_timestamp()`

Indexes: notification_channel_id

Report definitions: product, frequency, time, timezone, formats and recipients.

16 columns

Column	Type	Null	Default	References
`id` PK	`bigint(20) unsigned`
`tenant_id`	`bigint(20) unsigned`			`tenants` on delete cascade
`name`	`varchar(255)`
`category`	`varchar(16)`
`frequency`	`varchar(16)`
`time_of_day`	`varchar(5)`		`06:00`
`weekday`	`tinyint(3) unsigned`		`1`
`day_of_month`	`tinyint(3) unsigned`		`1`
`month`	`tinyint(3) unsigned`		`1`
`timezone`	`varchar(255)`		`UTC`
`formats`	`longtext`
`recipients`	`longtext`
`enabled`	`tinyint(1)`		`1`
`next_run_at`	`timestamp`	yes
`created_at`	`timestamp`	yes
`updated_at`	`timestamp`	yes

Indexes: enabled, next_run_at · tenant_id

Generated reports in the archive: period, hash chain over the files, retention date.

20 columns

Column	Type	Null	Default	References
`id` PK	`bigint(20) unsigned`
`tenant_id`	`bigint(20) unsigned`			`tenants` on delete cascade
`report_id`	`bigint(20) unsigned`	yes		`reports` on delete set null
`name`	`varchar(255)`
`category`	`varchar(16)`
`frequency`	`varchar(16)`
`period_start`	`timestamp`
`period_end`	`timestamp`
`timezone`	`varchar(255)`
`changes`	`int(10) unsigned`		`0`
`trigger`	`varchar(16)`		`schedule`
`status`	`varchar(16)`		`generated`
`recipients`	`longtext`	yes
`error`	`text`	yes
`sent_at`	`timestamp`	yes
`previous_hash`	`varchar(64)`	yes
`chain_hash`	`varchar(64)`	yes
`expires_at`	`timestamp`
`created_at`	`timestamp`	yes
`updated_at`	`timestamp`	yes

Indexes: report_id · tenant_id, id

Files of a generated report (PDF, CSV, XLSX, ODS) with their SHA-256.

9 columns

Column	Type	Default	References
`id` PK	`bigint(20) unsigned`
`report_run_id`	`bigint(20) unsigned`		`report_runs` on delete cascade
`format`	`varchar(8)`
`filename`	`varchar(255)`
`mime`	`varchar(128)`
`size`	`bigint(20) unsigned`
`sha256`	`varchar(64)`
`content`	`longtext`
`created_at`	`timestamp`	`current_timestamp()`

Indexes: report_run_id