​
ADR-0019: Unified org_module_configs Table for Per-Org Module Configuration

​
Status

Accepted

​
Tags

fieldforce, org-config, jsonb, multi-tenancy, modules

​
Decision

Per-org module configuration is stored in a single org_module_configs(org_id, module, config JSONB) table with a unique index on (org_id, module). Each module owns its config shape as a JSONB blob. Missing rows return Go-hardcoded defaults. Platform-wide defaults are version-controlled in Go code, not a database table.

​
Why

Every new module that introduces org-configurable settings would otherwise require its own migration and table (org_fieldforce_configs, org_leave_configs, etc.). A unified table allows any module to store its config without schema changes — only a new module value is needed. The JSONB shape per module is validated by the Go service layer, not the database, keeping the schema open for evolution. Rejected alternatives:
  • Per-module tables: org_fieldforce_configs, org_ai_configs, etc. One migration per new module, no unified API, no consistent query pattern. org_ai_configs already exists with this pattern and stays as-is (it handles encrypted keys that require special treatment). Rejected for new modules.
  • EAV org_configs(org_id, key, value text): Maximum flexibility, zero type safety. Structured config values become awkward string blobs. Rejected.
  • Platform-editable defaults in DB (platform_module_configs table): Adds admin UI complexity with no urgency. Defaults belong in code where they are version-controlled and reviewed. Deferred.

​
How it works

-- Schema (migration 005_fieldforce_phase2.sql)
CREATE TABLE org_module_configs (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id     text NOT NULL,
  module     text NOT NULL,
  config     JSONB NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX ON org_module_configs (org_id, module);
Read flow with default merge: 
GET /api/organizations/:org_id/configs/fieldforce
  1. SELECT config FROM org_module_configs WHERE org_id = ? AND module = 'fieldforce'
  2. Row found → unmarshal config JSONB → merge missing fields from DefaultFieldforceConfig
  3. Row not found → return DefaultFieldforceConfig unchanged
Write flow (upsert): 
PUT /api/organizations/:org_id/configs/fieldforce
  Body: { "overdue_notify_after_hours": 2 }
  → Validate: no negative values
  → INSERT INTO org_module_configs ... ON CONFLICT (org_id, module) DO UPDATE SET config = EXCLUDED.config
  → Return merged config (stored + defaults for unset fields)
Go-hardcoded defaults (not in DB): 
var DefaultFieldforceConfig = FieldforceConfig{
    OverdueNotifyAfterHours: 0,
    EscalationAfterHours:    24,
}

​
Known limitations

  • Dual config patterns coexist: org_ai_configs (legacy, encrypted key column) and org_module_configs (new, general-purpose JSONB). Future cleanup should fold org_ai_configs into org_module_configs. Until then, two patterns exist in the codebase.
  • No DB-level schema enforcement: JSONB accepts any structure — an incorrect config shape silently stores garbage. The Go service layer is the only validation point. Future modules must implement their own validation on write.

​
Rules for agents

  • New modules adding org-configurable settings MUST use org_module_configs — do not create new per-module config tables
  • Go services MUST merge stored config with hardcoded defaults field-by-field — a partial row (org set only some fields) must return full defaults for unset fields
  • PUT .../configs/:module MUST be restricted to Admin/Owner role only
  • Never return raw JSONB to the client — always unmarshal, merge with defaults, and return a typed struct
  • org_ai_configs remains separate — do not attempt to move encrypted API keys into org_module_configs

​
Bad pattern (do not generate)

// Creating a per-module table — defeats the purpose of the unified table
// migration: CREATE TABLE org_fieldforce_configs (org_id, overdue_hours, ...)  ← wrong

// Returning raw JSONB without merging defaults
func GetFieldforceConfig(orgID string) map[string]any {
    var row OrgModuleConfig
    db.Where("org_id = ? AND module = 'fieldforce'", orgID).First(&row)
    return row.Config // wrong — unset fields return zero values, not defaults
}

// Allowing any role to update config
func (h *ConfigHandler) UpdateConfig(c echo.Context) error {
    // missing role check — must require Admin/Owner
    h.service.Set(ctx, orgID, module, config)
}

​
Good pattern

// Merge stored config with hardcoded defaults
func (s *OrgModuleConfigService) GetFieldforceConfig(ctx context.Context, orgID string) (FieldforceConfig, error) {
    cfg := DefaultFieldforceConfig // start with defaults
    var row OrgModuleConfig
    err := s.repo.FindByOrgAndModule(ctx, orgID, "fieldforce", &row)
    if errors.Is(err, gorm.ErrRecordNotFound) {
        return cfg, nil // no row → full defaults
    }
    if err != nil { return cfg, err }
    // merge: only override defaults for fields explicitly set in stored config
    if v, ok := row.Config["overdue_notify_after_hours"]; ok {
        cfg.OverdueNotifyAfterHours = int(v.(float64))
    }
    if v, ok := row.Config["escalation_after_hours"]; ok {
        cfg.EscalationAfterHours = int(v.(float64))
    }
    return cfg, nil
}