Database Patterns

7.0 Table Naming Convention

MANDATORY: Every MySQL table name must be prefixed with its owning service's domain abbreviation:

Service	Prefix	Example
`bin-call-manager`	`call_`	`call_calls`, `call_groupcalls`, `call_outbound_configs`
`bin-flow-manager`	`flow_`	`flow_flows`, `flow_activeflows`
`bin-customer-manager`	`customer_`	`customer_customers`
`bin-agent-manager`	`agent_`	`agent_agents`
`bin-billing-manager`	`billing_`	`billing_accounts`, `billing_billings`
`bin-conference-manager`	`conference_`	`conference_conferences`
`bin-campaign-manager`	`campaign_`	`campaign_campaigns`
`bin-number-manager`	`number_`	`number_numbers`
`bin-registrar-manager`	`registrar_`	`registrar_trunks`
`bin-schedule-manager`	`schedule_`	`schedule_schedules`, `schedule_executions`

Format: <domain>_<plural-entity> — always lowercase, words separated by underscores.

# CORRECT
CREATE TABLE call_outbound_configs (...)         # call-manager owns this table

# WRONG — missing service prefix
CREATE TABLE outbound_configs (...)

# WRONG — use short prefix, not full service name
CREATE TABLE call_manager_outbound_configs (...)

The Go constant in the matching pkg/dbhandler/ file must use the full prefixed name:

const outboundConfigTable = "call_outbound_configs"  // CORRECT
const outboundConfigTable = "outbound_configs"        // WRONG — missing prefix

When adding a new service, derive the prefix from the service name (e.g. bin-rag-manager → rag_) and add it to the table above.

(bin-call-manager was formerly listed here as a raw-SQL exception. It was migrated to squirrel in VOIP-1078 and is no longer an exception — both the §7.0 prefix rule and the §7.1 Squirrel rule apply to it.)

7.0a UUID Column Type — `BINARY(16)`, Never `VARCHAR(36)`

MANDATORY: Every column that stores a UUID (e.g. id, customer_id, agent_id, any _id foreign key, etc.) must be declared as BINARY(16) in MySQL. Never use VARCHAR(36).

# CORRECT
CREATE TABLE call_outbound_configs (
    id           BINARY(16)   NOT NULL,
    customer_id  BINARY(16)   NOT NULL,
    ...
)

# WRONG — silent data corruption when stored via Go's MySQL driver
CREATE TABLE call_outbound_configs (
    id           VARCHAR(36)  NOT NULL,
    customer_id  VARCHAR(36)  NOT NULL,
    ...
)

Why this matters:

Go's gofrs/uuid.UUID.Value() returns a 36-char string by default, not 16 bytes. The 16-byte form is only produced when call sites use either of:
- The commondatabasehandler.PrepareFields() pipeline with db:"id,uuid" tags (preferred — see §7.2).
- An explicit id.Bytes() call at the dbhandler call site (only when raw SQL is used and PrepareFields() is not).
A BINARY(16) column with the correct call-site form stores 16 bytes and JOINs match.
A VARCHAR(36) column with mixed call-site forms is the trap: some paths write 36-char strings, other paths (e.g., bootstrap migrations doing INSERT INTO ... SELECT c.id FROM customer_customers where c.id is BINARY(16)) write a different byte sequence into the same VARCHAR(36) column. Subsequent equality queries from Go (WHERE customer_id = ? with a 36-char string) match the API-inserted rows but not the migration-inserted rows.
Failure mode is silent: no errors, no warnings, just empty result sets and orphaned rows. The bug we hit in May 2026 (call_outbound_configs UUIDs declared as VARCHAR(36)) cost a full day of investigation because the API path was self-consistent while the bootstrap migration path silently produced different bytes.

Go-side requirement when raw SQL is used (no Squirrel + PrepareFields):

// CORRECT — explicitly send 16 bytes to BINARY(16)
h.db.ExecContext(ctx, q, c.ID.Bytes(), c.CustomerID.Bytes(), ...)
h.db.QueryContext(ctx, q, id.Bytes())

// WRONG — gofrs.UUID.Value() returns a 36-char string, won't fit BINARY(16)
h.db.ExecContext(ctx, q, c.ID, c.CustomerID, ...)

Squirrel + commondatabasehandler.PrepareFields() automatically calls .Bytes() for fields tagged db:",uuid" — that is the preferred pattern (§7.2). Any remaining raw-SQL path must call .Bytes() explicitly.

Bootstrap migrations that copy UUIDs across tables must use the same byte representation:

# CORRECT — both columns are BINARY(16); c.id is already in the right form
INSERT INTO call_outbound_configs (id, customer_id, ...)
SELECT UNHEX(REPLACE(UUID(), '-', '')), c.id, ...
FROM customer_customers c
WHERE c.tm_delete IS NULL

# WRONG — c.id (BINARY(16)) implicitly converted to whatever VARCHAR(36) target stores it as
INSERT INTO call_outbound_configs (id, customer_id, ...)  -- customer_id VARCHAR(36)
SELECT UUID(), c.id, ...
FROM customer_customers ...

Migration ALTER pattern when fixing an existing table:

op.execute("DELETE FROM <table>")  # wipe corrupted rows
op.execute("ALTER TABLE <table> DROP INDEX <unique_key>")  # if any UUID is in a unique key
op.execute("ALTER TABLE <table> MODIFY id BINARY(16) NOT NULL")
op.execute("ALTER TABLE <table> MODIFY <foreign>_id BINARY(16) NOT NULL")
op.execute("ALTER TABLE <table> ADD UNIQUE KEY <unique_key> (<col>)")

This rule is enforced by the PostToolUse hook .claude/scripts/check-migration-uuid-columns.sh, which blocks any migration declaring a column ending in id as VARCHAR(36).

See also: §7.2 Go-side db:"id,uuid" tag (used by commondatabasehandler.PrepareFields() to send UUIDs as bytes); the "UUID Tag Gotcha" section at the bottom of this document.

7.1 Squirrel Query Builder (Mandatory)

All SQL queries MUST use the squirrel query builder. Raw SQL strings are forbidden.

// CORRECT — squirrel
query, args, _ := squirrel.Select(fields...).
    From(agentTable).
    Where(squirrel.Eq{string(agent.FieldID): id.Bytes()}).
    PlaceholderFormat(squirrel.Question).
    ToSql()

// WRONG — raw SQL
query := "SELECT * FROM agent_agents WHERE id = ?"

Exception: Expressions that squirrel's builder cannot express. Document WHY with a comment at every call site, and keep all operands bound as query arguments — never interpolate a value into the SQL text. Two sanctioned categories:

Computed/arithmetic expressions — e.g. cost_per_unit * ?, or an atomic call_count = call_count - 1 that must be evaluated by the database to avoid a lost-update race. Reference: bin-rag-manager/pkg/dbhandler/document.go:355, bin-call-manager/pkg/dbhandler/groupcall.go.
MySQL JSON functions — json_array_append, json_insert, json_set, json_remove, json_search, JSON_CONTAINS, JSON_EXTRACT. Squirrel has no builder form for any of these. Reference: bin-call-manager/pkg/dbhandler/json_expr.go (the shared expression helpers and the largest concentration of such sites), bin-agent-manager/pkg/dbhandler/agent.go:709, bin-conversation-manager/pkg/dbhandler/conversation.go:160-163.

Note these sites are usually invisible to unit tests, since SQLite (used as the in-memory test database across the monorepo) implements none of the MySQL JSON functions. Pin them with golden ToSql() string assertions instead — see bin-call-manager/pkg/dbhandler/json_expr_golden_test.go.

7.2 CRUD Operations

INSERT:

// CORRECT
a.TMCreate = h.utilHandler.TimeNow()
fields, _ := commondatabasehandler.PrepareFields(a)
sb := squirrel.Insert(agentTable).SetMap(fields).PlaceholderFormat(squirrel.Question)
query, args, _ := sb.ToSql()
_, err := h.db.ExecContext(ctx, query, args...)

SELECT:

// CORRECT — using GetDBFields + ScanRow
fields := commondatabasehandler.GetDBFields(&agent.Agent{})
query, args, _ := squirrel.Select(fields...).
    From(agentTable).
    Where(squirrel.Eq{string(agent.FieldID): id.Bytes()}).
    PlaceholderFormat(squirrel.Question).ToSql()
row, err := h.db.QueryContext(ctx, query, args...)
res := &agent.Agent{}
if err := commondatabasehandler.ScanRow(row, res); err != nil { ... }

// WRONG — manual rows.Scan
row.Scan(&m.ID, &m.CustomerID, &m.Name)  // FORBIDDEN

UPDATE:

// CORRECT
fields[agent.FieldTMUpdate] = h.utilHandler.TimeNow()
tmpFields, _ := commondatabasehandler.PrepareFields(fields)
q := squirrel.Update(agentTable).SetMap(tmpFields).Where(squirrel.Eq{"id": id.Bytes()})

DELETE (soft):

// CORRECT — soft delete by setting TMDelete
now := h.utilHandler.TimeNow()
return h.agentUpdate(ctx, id, map[agent.Field]any{
    agent.FieldTMDelete: now,
    agent.FieldTMUpdate: now,
})

7.3 Empty Slice Initialization

MANDATORY: List functions must initialize result slices as empty, never nil:

// CORRECT — empty slice
res := []*agent.Agent{}

// WRONG — nil slice serializes to null in JSON instead of []
var res []*agent.Agent

7.4 Cache-Aside Pattern

DB reads: cache-first, fallback to DB. Mutations: write-through.

// CORRECT — cache-aside read
func (h *handler) AgentGet(ctx context.Context, id uuid.UUID) (*agent.Agent, error) {
    if res, err := h.agentGetFromCache(ctx, id); err == nil {
        return res, nil  // cache hit
    }
    return h.agentGetFromDB(ctx, id)  // cache miss → DB → set cache
}

// CORRECT — write-through on mutation
func (h *handler) AgentCreate(ctx context.Context, a *agent.Agent) error {
    // ... insert to DB ...
    _ = h.agentUpdateToCache(ctx, a.ID)  // update cache after DB write
    return nil
}

7.5 Cursor-Based Pagination

Pagination uses TMCreate timestamp as cursor token:

// CORRECT
if token == "" {
    token = h.utilHandler.TimeGetCurTime()
}
sb := squirrel.Select(fields...).From(agentTable).
    Where(squirrel.Lt{string(agent.FieldTMCreate): token}).
    OrderBy(string(agent.FieldTMCreate) + " DESC").
    Limit(size)

7.6 Filter Application

Use commondatabasehandler.ApplyFields() for type-safe filter maps:

// CORRECT
sb, _ = commondatabasehandler.ApplyFields(sb, filters)
// Handles: uuid → bytes, "deleted: false" → tm_delete IS NULL, etc.

7.7 DB Operations Location

All database operations MUST live in pkg/dbhandler/. Business logic handlers receive DBHandler interface only.

// CORRECT — business handler uses interface
type agentHandler struct {
    db dbhandler.DBHandler  // interface, not *sql.DB
}

// WRONG — business handler accessing DB directly
type agentHandler struct {
    db *sql.DB  // FORBIDDEN outside dbhandler
}

Checklist

Use this checklist when adding or modifying database models and operations:

Model Definition

All uuid.UUID fields have ,uuid db tag (see §6.2 in models.md)
Slice/map fields stored as JSON have ,json db tag
Soft-delete field present: TMDelete *time.Time \db:"tm_delete"`` (nil = active)
All persisted fields have appropriate db:"column_name" tags
JSON tags match API expectations and the WebhookMessage variant

Database Operations

Table name uses the owning service's domain prefix (call_, flow_, agent_, …) (§7.0)
Queries use the squirrel query builder, not raw SQL (§7.1)
INSERT/UPDATE go through commondatabasehandler.PrepareFields (§7.2)
SELECT uses commondatabasehandler.GetDBFields + ScanRow — never manual rows.Scan (§7.2)
List/Gets functions initialize res := []*Type{} (empty, never nil) (§7.3)
All DB code lives in pkg/dbhandler/; business handlers receive the DBHandler interface only (§7.7)

UUID Tag Gotcha

commondatabasehandler.PrepareFields() and ApplyFields() use the ,uuid flag on db: tags to convert uuid.UUID values to MySQL BINARY(16). Without the flag, the UUID is sent as a string and never matches any row.

Symptoms of a missing ,uuid tag:

GET with filters returns [] even though data exists
POST works but GET by ID fails
No errors in logs — just empty results

// CORRECT
type Call struct {
    ID         uuid.UUID `json:"id" db:"id,uuid"`
    CustomerID uuid.UUID `json:"customer_id" db:"customer_id,uuid"`
    // ...
}

// WRONG — silent failures
type Call struct {
    ID         uuid.UUID `json:"id" db:"id"`           // BUG: queries will fail
    CustomerID uuid.UUID `json:"customer_id" db:"customer_id"` // BUG
}

Transaction Pattern

When a multi-step write must be atomic, wrap it in a transaction:

func (h *dbHandler) CreateWithRelated(ctx context.Context, model *Model) error {
    tx, err := h.db.BeginTx(ctx, nil)
    if err != nil {
        return errors.Wrap(err, "could not begin transaction")
    }
    defer tx.Rollback()

    // Insert main record
    if err := h.insertModel(ctx, tx, model); err != nil {
        return err
    }

    // Insert related records
    if err := h.insertRelated(ctx, tx, model.ID, model.Related); err != nil {
        return err
    }

    return tx.Commit()
}

Debugging Database Issues

Log the generated query

sql, args, _ := query.ToSql()
log.WithFields(logrus.Fields{
    "sql":  sql,
    "args": args,
}).Debug("Executing query")

Verify UUID conversion

id := uuid.FromStringOrNil("...")
log.Debugf("UUID bytes: %x", id.Bytes())

Confirm the soft-delete filter is applied

commondatabasehandler.ApplyFields adds tm_delete IS NULL automatically when the deleted: false filter sentinel is present — verify by inspecting the rendered SQL.

Pagination Defaults

Most services define page-size constants and clamp incoming sizes:

const (
    DefaultPageSize = 100
    MaxPageSize     = 1000
)

if size <= 0 || size > MaxPageSize {
    size = DefaultPageSize
}

The cursor token is the previous page's tm_create value (see §7.5).

Legacy Soft-Delete Variant

A handful of older services still use a sentinel tm_delete string instead of a nullable timestamp:

// Legacy convention — used in a small number of older services
const DefaultTimeStamp = "9999-01-01 00:00:00.000000"

query := sq.Select("*").
    From("call_calls").
    Where(sq.Eq{"tm_delete": databasehandler.DefaultTimeStamp})

New code should follow the canonical *time.Time / nil-as-active pattern documented in §6.3 and §7.2. The legacy sentinel pattern is documented here only because some dbhandler/ packages still use it; do not introduce it in new tables.

Navigation Menu

Uh oh!

Database Patterns

7.0 Table Naming Convention

7.0a UUID Column Type — `BINARY(16)`, Never `VARCHAR(36)`

7.1 Squirrel Query Builder (Mandatory)

7.2 CRUD Operations

7.3 Empty Slice Initialization

7.4 Cache-Aside Pattern

7.5 Cursor-Based Pagination

7.6 Filter Application

7.7 DB Operations Location

Checklist

Model Definition

Database Operations

UUID Tag Gotcha

Transaction Pattern

Debugging Database Issues

Log the generated query

Verify UUID conversion

Confirm the soft-delete filter is applied

Pagination Defaults

Legacy Soft-Delete Variant

Uh oh!

FilesExpand file tree

database.md

Latest commit

History

database.md

File metadata and controls

Database Patterns

7.0 Table Naming Convention

7.0a UUID Column Type — BINARY(16), Never VARCHAR(36)

7.1 Squirrel Query Builder (Mandatory)

7.2 CRUD Operations

7.3 Empty Slice Initialization

7.4 Cache-Aside Pattern

7.5 Cursor-Based Pagination

7.6 Filter Application

7.7 DB Operations Location

Checklist

Model Definition

Database Operations

UUID Tag Gotcha

Transaction Pattern

Debugging Database Issues

Log the generated query

Verify UUID conversion

Confirm the soft-delete filter is applied

Pagination Defaults

Legacy Soft-Delete Variant

7.0a UUID Column Type — `BINARY(16)`, Never `VARCHAR(36)`