dev-r-services-insert

Playbook: Inserting a New Row into dev_r_services

Problem

dev_r_services has multiple NOT NULL columns that are not visible in information_schema (they appear as is_nullable: YES) because they are enforced by a database trigger, not a column-level constraint. Omitting them causes 23502: null value in column "X" violates not-null constraint at INSERT time, even though the schema query looked fine.

Required fields (trigger-enforced, as of 2026-06-27):

• project_name — must match project_id (e.g. 'p24-infra')
• service_type — pick from: workflow, monitoring, secret, config, database, script
• owner — GitHub username of the responsible person (e.g. 'radieu')

In addition, there is no unique constraint on service_name, so ON CONFLICT (service_name) will fail with 42P10: there is no unique or exclusion constraint matching. Use a WHERE NOT EXISTS pattern instead.

---

Canonical insert template

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM dev_r_services WHERE service_name = '<service-name>') THEN
    INSERT INTO dev_r_services (
      service_name,
      project_id,   project_name,    -- project_name MUST equal project_id
      office_id,    ws_id,    app_id, -- standard p24-infra defaults below
      service_type,                  -- trigger-enforced NOT NULL
      element_type,
      status,
      owner,                         -- trigger-enforced NOT NULL
      compliance_workbook,
      workbook_url,
      compliance_notes
    ) VALUES (
      '<service-name>',
      'p24-infra', 'p24-infra',
      'p24-devops', '99', 'et-app',
      '<service_type>',
      '<element_type>',
      'active',
      'radieu',
      'yes',
      'docs/<ops-doc>.md',
      '<one-line compliance note>'
    );
  ELSE
    UPDATE dev_r_services SET
      service_type        = '<service_type>',
      element_type        = '<element_type>',
      status              = 'active',
      compliance_workbook = 'yes',
      workbook_url        = 'docs/<ops-doc>.md',
      compliance_notes    = '<one-line compliance note>'
    WHERE service_name = '<service-name>';
  END IF;
END $$;

Standard p24-infra values for the boilerplate columns

Column	Value	Note
project_id	'p24-infra'	string ID, not UUID
project_name	'p24-infra'	must match project_id
office_id	'p24-devops'	fixed for all p24-infra elements
ws_id	'99'	fixed for all p24-infra elements
app_id	'et-app'	fixed for all p24-infra elements
owner	'radieu'	GitHub username of responsible person

service_type values in use

Value	When to use
monitoring	Prometheus exporters, Grafana, Alertmanager, audit tables
workflow	Scripts, GitHub Actions, n8n workflows, cron jobs
secret	Credentials tracked in the registry
config	Config files, Caddy rules, Prometheus scrape configs
script	One-off Python/Bash scripts
database	PostgreSQL tables, indexes, schemas

element_type values in use

Value	When to use
supabase_table	Supabase/Postgres table
script	Shell or Python script
workflow	n8n or GitHub Actions workflow
config	Configuration file or block
credential	API key, password, token
container	Docker container

---

audit.actions required fields (same gotcha pattern)

When inserting into audit.actions, the schedule column is NOT NULL with no default — it must be provided in addition to cron_expression (which is optional/alias):

INSERT INTO audit.actions
  (id, project_id, name, description, action_type, schedule, cron_expression, per_run_token_cap, enabled)
VALUES
  ('<uuid>',
   (SELECT id FROM audit.projects WHERE name = 'p24-infra'),  -- look up, don't hardcode
   '<action name>',
   '<description>',
   '<action_type>',
   '0 2 * * 0',   -- schedule (NOT NULL, required)
   '0 2 * * 0',   -- cron_expression (nullable alias, set to same value)
   0,
   true)
ON CONFLICT (id) DO UPDATE SET ...;

Do NOT hardcode the project_id UUID. Always look it up with SELECT id FROM audit.projects WHERE name = 'p24-infra' — the UUID differs between environments and can change on reset.

---

Verify insert succeeded

SELECT service_name, service_type, element_type, compliance_workbook
FROM dev_r_services
WHERE service_name = '<service-name>';
-- Must return exactly one row; empty result means insert was silently skipped

---

Escalation

If trigger errors continue for columns not listed here:

-- Find all trigger-enforced constraints (not in information_schema)
SELECT trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'dev_r_services';