Database

Database

Overview

TicketPulse uses SQLite as its database, accessed through the jmoiron/sqlx library with the modernc.org/sqlite driver (a pure Go SQLite implementation -- no CGO required).

The database is configured via the DB_FILEPATH environment variable and is initialized on startup with automatic schema migrations.

Connection Settings

Setting	Value
Driver	modernc.org/sqlite (pure Go)
Max Open Connections	1
Max Idle Connections	1

SQLite uses a single-writer model, so connections are limited to 1 to prevent SQLITE_BUSY errors.

Migrations

Migrations are managed by Goose and embedded in the binary using Go's //go:embed directive. They run automatically on startup via goose.Up().

Migration Files

File	Description
001_initial_schema.sql	Core tables: users, user_tag_alerts, configuration, alert_logs, sla_alert_cache, daily_summary_log
002_add_sla_metric_and_label.sql	Adds metric_type and label to SLA tables
003_add_user_work_day_settings.sql	Adds work day settings columns to users
004_add_zendesk_tag_cache.sql	Creates zendesk_tag_cache table
005_add_slack_channel_name.sql	Adds slack_channel_name column to user_tag_alerts
006_add_daily_alert_log.sql	Creates skipped_alerts, acknowledgment_logs, daily_alert_log_sent tables

Schema

users

Stores registered users and their settings.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
email	TEXT UNIQUE	Google OAuth email
name	TEXT	Display name
role	TEXT	admin or agent
daily_summary	BOOLEAN	Daily summary enabled
selected_tags	TEXT	Legacy field
summary_time	DATETIME	Preferred summary delivery time
slack_user_id	TEXT	Slack user ID for DMs
work_day_start_time	TIME	Work day start (HH:MM)
work_day_end_time	TIME	Work day end (HH:MM)
timezone	TEXT	IANA timezone (e.g., America/New_York)
work_days	TEXT	JSON array of active work days
summary_tag_filter	TEXT	all_tags or configured_tags
summary_ticket_filter	TEXT	all_tickets or assigned_tickets
created_at	DATETIME	Row creation timestamp
updated_at	DATETIME	Row update timestamp

user_tag_alerts

Stores per-user tag alert rules.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
user_id	INTEGER FK	References users(id) with CASCADE delete
tag	TEXT	Zendesk tag to watch
slack_channel_id	TEXT	Slack channel ID for notifications
slack_channel_name	TEXT	Display name of the Slack channel
alert_type	TEXT	new_ticket, ticket_update, sla_reply, sla_resolution, or sla_deadline

configuration

Key-value store for application settings.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
key	TEXT UNIQUE	Configuration key
value	TEXT	Configuration value (sensitive values are AES-encrypted)

alert_logs

Records every alert that was sent.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
user_id	INTEGER FK	User whose rule triggered the alert
ticket_id	INTEGER	Zendesk ticket ID
tag	TEXT	Tag that matched
alert_type	TEXT	Type of alert sent
timestamp	DATETIME	When the alert was sent

sla_alert_cache

Deduplication cache for SLA alerts. Prevents sending the same SLA threshold alert repeatedly.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
user_id	INTEGER FK	User ID
ticket_id	INTEGER	Zendesk ticket ID
alert_type	VARCHAR	Alert type
metric_type	VARCHAR	reply_time or resolution_time
breach_at	TIMESTAMP	SLA breach time
created_at	TIMESTAMP	Cache entry creation time
label	TEXT	SLA threshold label (e.g., "Less than 1 hour remaining")

Unique constraint: (user_id, ticket_id, alert_type).

daily_summary_log

Deduplication log for daily summaries. Prevents sending multiple summaries to the same user on the same day.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
user_id	INTEGER FK	User ID
summary_date	DATE	Date the summary was sent (in user's timezone)
created_at	DATETIME	Row creation timestamp

Unique constraint: (user_id, summary_date).

zendesk_tag_cache

Cache of all Zendesk tags, refreshed every 6 hours.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
tag	TEXT	Tag name

skipped_alerts

Records alerts that were evaluated but not sent, along with the reason.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
ticket_id	INTEGER	Zendesk ticket ID
user_id	INTEGER FK	User ID
tag	TEXT	Tag that matched
alert_type	TEXT	Alert type
skip_reason	TEXT	Reason code (see Alert System)
metric_type	TEXT	SLA metric type (if applicable)
label	TEXT	SLA label (if applicable)
created_at	DATETIME	Row creation timestamp

acknowledgment_logs

Records when alerts are acknowledged via Slack.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
alert_log_id	INTEGER FK	References alert_logs(id) with SET NULL on delete
ticket_id	INTEGER	Zendesk ticket ID
slack_user_id	TEXT	Slack user ID of the acknowledger
slack_user_name	TEXT	Slack username
acknowledged_at	DATETIME	Timestamp of acknowledgment

daily_alert_log_sent

Deduplication table for the daily alert log digest.

Column	Type	Description
id	INTEGER PK	Auto-increment ID
log_date	DATE UNIQUE	Date for which the log was sent
created_at	DATETIME	Row creation timestamp

Data Retention

The scheduler automatically cleans up old data every 6 hours:

Table	Retention	Method
skipped_alerts	30 days	Deletes rows where created_at is older than 30 days
acknowledgment_logs	30 days	Deletes rows where acknowledged_at is older than 30 days
sla_alert_cache	24 hours	Deletes rows where created_at is older than 24 hours (cleared after each poll)

The alert_logs, daily_summary_log, and daily_alert_log_sent tables are not automatically pruned.

Database Interface

The db.Database interface abstracts database access for testability:

type Database interface {
    QueryRow(query string, args ...interface{}) *sql.Row
    Exec(query string, args ...interface{}) (sql.Result, error)
    Query(query string, args ...interface{}) (*sql.Rows, error)
    QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
    ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
    Select(dest interface{}, query string, args ...interface{}) error
    NamedExec(query string, arg interface{}) (sql.Result, error)
    Get(dest interface{}, query string, args ...interface{}) error
    Close() error
    Beginx() (*sqlx.Tx, error)
    Preparex(query string) (*sqlx.Stmt, error)
    GetDB() *sqlx.DB
}

Tests use db.InitDB(":memory:") to create an in-memory SQLite database with all migrations applied.