database

Documentation

Overview

Package database provides a SQLite database layer with string interning.

WRITE CONCURRENCY MODEL:

Single writer connection with read-only reader pool architecture:

• writerConn: Single connection (SetMaxOpenConns=1) for all write operations
• readerPool: Read-only connection pool for concurrent reads
• ExecContext: Routes writes to writerConn, reads to readerPool
• QueryContext: Routes writes to writerConn, reads to readerPool
• QueryRowContext: Routes writes to writerConn, reads to readerPool
• BeginTx (write): Uses writerConn, fully serialized by writerMu mutex
• BeginTx (read-only): Uses readerPool (concurrent)
• WAL mode allows concurrent readers during writes

The single writer connection + writerMu mutex eliminates both SQLITE_BUSY errors and "cannot start a transaction within a transaction" errors by fully serializing all write transactions. Only one write transaction can be active at a time.

STRING POOL SYSTEM:

String interning uses the database string_pool table as the source of truth. String operations are handled through dbinterface package functions that work within transactions using INSERT ... ON CONFLICT for deduplication.

CLEANUP CONCURRENCY PROTECTION:

Periodic cleanup removes unused strings from string_pool:

• Runs automatically every 24 hours
• atomic.Bool prevents overlapping cleanup operations
• Uses write transaction for atomicity

FAILURE MODES & RECOVERY:

1. Cleanup fails:

• Strings remain in database (orphaned but harmless)
• Next cleanup (24hrs) will retry
• Disk space gradually increases until next successful cleanup

MONITORING:

Use GetStringPoolMetrics() to monitor:

• cleanupDeleted = total strings removed (growing = healthy cleanup)

Index

• type DB
  • func New(databasePath string) (*DB, error)
  • func NewForTest(conn *sql.DB) *DB
  • func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (dbinterface.TxQuerier, error)
  • func (db *DB) CleanupUnusedStrings(ctx context.Context) (int64, error)
  • func (db *DB) Close() error
  • func (db *DB) Conn() *sql.DB
  • func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
  • func (db *DB) GetStringPoolMetrics() (cleanupDeleted uint64)
  • func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
  • func (db *DB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
• type LicenseRepo
  • func NewLicenseRepo(db *DB) *LicenseRepo
  • func (r *LicenseRepo) DeleteLicense(ctx context.Context, licenseKey string) error
  • func (r *LicenseRepo) GetAllLicenses(ctx context.Context) ([]*models.ProductLicense, error)
  • func (r *LicenseRepo) GetLicenseByKey(ctx context.Context, licenseKey string) (*models.ProductLicense, error)
  • func (r *LicenseRepo) HasPremiumAccess(ctx context.Context) (bool, error)
  • func (r *LicenseRepo) StoreLicense(ctx context.Context, license *models.ProductLicense) error
  • func (r *LicenseRepo) UpdateLicenseActivation(ctx context.Context, license *models.ProductLicense) error
  • func (r *LicenseRepo) UpdateLicenseStatus(ctx context.Context, licenseID int, status string) error
  • func (r *LicenseRepo) UpdateLicenseValidation(ctx context.Context, license *models.ProductLicense) error
• type MetricsCollector
  • func NewMetricsCollector() *MetricsCollector
  • func (c *MetricsCollector) Collect(ch chan<- prometheus.Metric)
  • func (c *MetricsCollector) Describe(ch chan<- *prometheus.Desc)
• type Tx
  • func (t *Tx) Commit() error
  • func (t *Tx) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
  • func (t *Tx) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
  • func (t *Tx) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
  • func (t *Tx) Rollback() error

Types

type DB

type DB struct {
	// contains filtered or unexported fields
}

reader/writer fields on DB

func New

func New(databasePath string) (*DB, error)

func NewForTest

func NewForTest(conn *sql.DB) *DB

NewForTest wraps an existing sql.DB connection for testing purposes. This creates a minimal DB wrapper without running migrations or starting background goroutines. The caller is responsible for managing the underlying sql.DB connection lifecycle.

IMPORTANT LIMITATIONS FOR TESTING: - Does NOT start the stringPoolCleanupLoop (automatic cleanup is disabled) - Tests must manually call CleanupUnusedStrings() if testing cleanup behavior - String pool may grow unbounded during test execution - Tests should use short-lived databases or manually clean up - Uses the same connection for both reads and writes (no separate reader pool)

This differs from production where: - stringPoolCleanupLoop runs automatically every 24 hours - Unused strings are automatically removed - String pool size is bounded - Separate writer connection and reader pool for better concurrency

Note: This function is intended for testing only and should not be used in production code. Use New() for production database initialization.

func (*DB) BeginTx

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (dbinterface.TxQuerier, error)

BeginTx starts a transaction. Returns a wrapped transaction that uses statement caching.

CONCURRENCY MODEL: - Read-only transactions use the reader pool (concurrent) - Write transactions use the single writer connection (serialized via mutex + SQLite) - WAL mode allows concurrent readers during write transactions

STATEMENT CACHING STRATEGY: Uses separate prepared statement caches for writer and reader connections: - writerStmts: Cache for statements prepared on writerConn (single connection) - readerStmts: Cache for statements prepared on readerPool (concurrent connections)

Transaction methods check the appropriate connection-specific cache first. If a statement exists in the cache and was prepared on the same connection type as the transaction, it can be reused safely via tx.StmtContext().

If not cached, statements are prepared directly on the transaction and tracked for promotion to the appropriate cache after successful commit.

This ensures connection safety while providing caching benefits for repeated queries.

ISOLATION LEVEL: - SQLite defaults to SERIALIZABLE isolation (strongest guarantee) - Pass nil for opts to use SERIALIZABLE (recommended for most cases) - For read-only transactions, use: &sql.TxOptions{ReadOnly: true} - Read-only transactions allow full concurrency with writers in WAL mode

WHEN TO USE EACH: - Use ExecContext for simple, single-statement writes (INSERT, UPDATE, DELETE) - Use BeginTx for multi-statement operations that need atomicity - Use BeginTx when you need to read and write in a consistent snapshot

GUARANTEES: - ExecContext: Sequential execution through single writer connection, no partial writes visible - BeginTx (write): ACID properties, full transaction isolation, serialized via mutex + single writer connection - BeginTx (read-only): ACID properties, concurrent with writes - All write operations: Serialized through mutex + single writer connection (no "transaction within transaction" errors)

LIMITATIONS: - Write transactions are serialized (one at a time) due to mutex + single writer connection - Long-running write transactions will block other write transactions - Use read-only transactions when possible to avoid blocking writes

NOTE ON SERIALIZATION: SQLite with SetMaxOpenConns=1 does NOT properly queue BeginTx calls - it fails immediately with "cannot start a transaction within a transaction" instead of waiting. The writerMu mutex serializes write transactions for their ENTIRE lifetime (BeginTx through Commit/Rollback) to prevent this error. The mutex is released only when the transaction completes (Commit or Rollback). This means write transactions are fully serialized, but that's acceptable since SQLite can only handle one write transaction at a time anyway.

func (*DB) CleanupUnusedStrings

func (db *DB) CleanupUnusedStrings(ctx context.Context) (int64, error)

func (*DB) Close

func (db *DB) Close() error

func (*DB) Conn

func (db *DB) Conn() *sql.DB

func (*DB) ExecContext

func (db *DB) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

ExecContext routes write queries to the single writer connection and read queries to the reader pool. Uses prepared statements when possible. Do NOT use this for queries with RETURNING clauses - use QueryRowContext or QueryContext instead.

func (*DB) GetStringPoolMetrics

func (db *DB) GetStringPoolMetrics() (cleanupDeleted uint64)

GetStringPoolMetrics returns the current values of string pool cleanup metrics. These metrics track cleanup activity:

• cleanupDeleted: Total number of unused strings deleted since startup

This counter is cumulative and never resets. Use it to track cleanup effectiveness.

func (*DB) QueryContext

func (db *DB) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)

QueryContext routes write queries to the single writer connection and read queries to the reader pool. Uses prepared statements when possible.

func (*DB) QueryRowContext

func (db *DB) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row

QueryRowContext routes write queries to the single writer connection and read queries to the reader pool. Uses prepared statements when possible.

type LicenseRepo

type LicenseRepo struct {
	// contains filtered or unexported fields
}

func NewLicenseRepo

func NewLicenseRepo(db *DB) *LicenseRepo

func (*LicenseRepo) DeleteLicense

func (r *LicenseRepo) DeleteLicense(ctx context.Context, licenseKey string) error

DeleteLicense removes a license from the database

func (*LicenseRepo) GetAllLicenses

func (r *LicenseRepo) GetAllLicenses(ctx context.Context) ([]*models.ProductLicense, error)

GetAllLicenses retrieves all licenses

func (*LicenseRepo) GetLicenseByKey

func (r *LicenseRepo) GetLicenseByKey(ctx context.Context, licenseKey string) (*models.ProductLicense, error)

GetLicenseByKey retrieves a license by its key

func (*LicenseRepo) HasPremiumAccess

func (r *LicenseRepo) HasPremiumAccess(ctx context.Context) (bool, error)

HasPremiumAccess checks if the user has purchased premium access (one-time unlock)

func (*LicenseRepo) StoreLicense

func (r *LicenseRepo) StoreLicense(ctx context.Context, license *models.ProductLicense) error

func (*LicenseRepo) UpdateLicenseActivation

func (r *LicenseRepo) UpdateLicenseActivation(ctx context.Context, license *models.ProductLicense) error

UpdateLicenseActivation updates a license with activation details

func (*LicenseRepo) UpdateLicenseStatus

func (r *LicenseRepo) UpdateLicenseStatus(ctx context.Context, licenseID int, status string) error

func (*LicenseRepo) UpdateLicenseValidation

func (r *LicenseRepo) UpdateLicenseValidation(ctx context.Context, license *models.ProductLicense) error

type MetricsCollector

type MetricsCollector struct {
	// contains filtered or unexported fields
}

func NewMetricsCollector

func NewMetricsCollector() *MetricsCollector

func (*MetricsCollector) Collect

func (c *MetricsCollector) Collect(ch chan<- prometheus.Metric)

func (*MetricsCollector) Describe

func (c *MetricsCollector) Describe(ch chan<- *prometheus.Desc)

type Tx

type Tx struct {
	// contains filtered or unexported fields
}

Tx wraps sql.Tx to provide prepared statement caching for transaction queries

func (*Tx) Commit

func (t *Tx) Commit() error

Commit commits the transaction and releases the writer mutex if this is a write transaction. Also promotes any transaction-prepared statements to the DB cache for future use. On failure, the transaction remains active - caller must call Rollback() to release the mutex.

func (*Tx) ExecContext

func (t *Tx) ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)

ExecContext executes a query within the transaction. Uses connection-specific statement cache when available. If statement is not cached, prepares it on the transaction and marks it for promotion to DB cache after commit.

func (*Tx) QueryContext

func (t *Tx) QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)

QueryContext executes a query within the transaction. Uses connection-specific statement cache when available. If statement is not cached, prepares it on the transaction and marks it for promotion to DB cache after commit.

func (*Tx) QueryRowContext

func (t *Tx) QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row

QueryRowContext executes a query within the transaction. Uses connection-specific statement cache when available. If statement is not cached, prepares it on the transaction and marks it for promotion to DB cache after commit.

func (*Tx) Rollback

func (t *Tx) Rollback() error

Rollback rolls back the transaction and releases the writer mutex if this is a write transaction. Always releases the mutex since the transaction is done (either rolled back successfully, or already closed from a prior failed commit returning ErrTxDone). Does NOT promote statements to cache since the transaction failed.