Persistence and Data Model

Persistence and Data Model

Hooray uses SQLite with transactional migrations, foreign keys, JSON validation, deterministic ordering, bounded pagination, and explicit busy/error handling.

Core records

The initial schema defines normalized records for:

• scan runs and assets;
• components and dependency edges;
• findings, evidence, and remediations;
• policy decisions;
• policy documents and exceptions;
• audit events; and
• retention events.

Monitor targets, cursors, and delivery events are managed by the store/runtime monitor persistence layer; the hooray monitor targets add|list|remove subcommands register and manage watch entries on top of it. Do not infer their physical schema solely from migrations/001_init.sql.

Integrity

• Run IDs, component IDs, finding IDs, and relationships are validated before mutation.
• Duplicate run IDs fail rather than overwriting committed history.
• Malformed stored JSON and normalized identifiers fail closed.
• Future, gapped, or corrupt migration histories are rejected transactionally.
• Retention cascades normalized rows and records the deletion outcome.
• Policy and exception updates use optimistic conflict detection and audit logging.

Concurrency

The API serializes store access through its configured state boundary. Factory connections handle SQLite busy conditions without corrupting committed state. Monitor delivery claims are conditional and transactional across independent connections.

Backup and operations

Back up the configured database path with a SQLite-consistent method. Keep the database on reliable local storage, restrict filesystem access, and monitor free disk space. Treat policy documents, exception ownership, and audit records as security-sensitive operational data even though report serializers redact credential-like metadata.