Codegen IAM Permissions and Roles Population

After all phases are complete:

• iam_service_roles_populate.sql — AUTO-GENERATED. One role per service, permissions drawn from iam_permissions in the registry.
• iam_service_permissions_populate.sql — AUTO-GENERATED. Component wildcards from the registry; per-entity CRUD from domain entity models.
• iam_user_roles_populate.sql — Hand-maintained. SuperAdmin, TenantAdmin, UserRole and other non-service roles only.
• iam_permissions_populate.sql — deleted (replaced by the two generated files + the hand-maintained user-roles file).
• iam_roles_populate.sql — deleted (replaced by the generated service-roles file + the hand-maintained user-roles file).

Adding a new service requires only:

1. Add entry to ores_services_service_registry.json (name, iam_role, iam_permissions, dml_prefixes).
2. Add domain entity models with permissions arrays for each exposed entity.
3. Run codegen.

Add iam_permissions array to every service entry in the registry. This captures the full set of permissions the service role needs, including cross-component reads:

{
  "name": "workspace",
  "iam_role": "WorkspaceService",
  "description": "Workspace domain service",
  "iam_permissions": [
    "workspace::*",
    "iam::tenants:read"
  ]
}

Source of truth for the initial values: the current iam_roles_populate.sql service block. Every service that currently has a role entry gets its permissions transcribed verbatim. Services missing from iam_roles_populate.sql (discovered during inventory) get entries written from first principles.

New template sql_service_roles_populate.mustache iterates over the registry and emits one role upsert + N permission assignments per service. Replaces the service section of iam_roles_populate.sql.

The non-service roles (SuperAdmin, TenantAdmin, UserRole, etc.) are extracted into iam_user_roles_populate.sql (hand-maintained). setup_schema.sql is updated to \ir both files.

New template sql_service_permissions_populate.mustache generates:

-- <Description> component wildcard
PERFORM ores_iam_permissions_upsert_fn(..., '<name>::*',
    'Full access to all <description> operations');

One entry per service, derived purely from name and description in the registry.

Each domain entity model gains a permissions array:

{
  "domain_entity": {
    "component": "workspace",
    "entity_plural": "workspaces",
    "permissions": ["read", "write", "delete"]
  }
}

The same sql_service_permissions_populate.mustache (or a companion template) generates the per-operation upserts:

PERFORM ores_iam_permissions_upsert_fn(..., 'workspace::workspaces:read',   'View workspace details');
PERFORM ores_iam_permissions_upsert_fn(..., 'workspace::workspaces:write',  'Create and modify workspaces');
PERFORM ores_iam_permissions_upsert_fn(..., 'workspace::workspaces:delete', 'Archive workspaces');

This phase requires updating all existing domain entity models. The inventory (see above) provides the definitive list and ensures no permission currently in iam_permissions_populate.sql is dropped.

• Delete iam_permissions_populate.sql and iam_roles_populate.sql (fully replaced by generated equivalents + iam_user_roles_populate.sql).
• Verify recreate_database.sh passes on a clean database.
• Update any documentation referencing the deleted files.

1. Audit iam_permissions_populate.sql and iam_roles_populate.sql: classify every entry into the five categories in the Inventory table above.
2. Identify any gaps (services in the registry with no role entry; entities with no permission entries).
3. Add iam_permissions array to every service in ores_services_service_registry.json, sourced from the current iam_roles_populate.sql service block.
4. Document gaps found; fix any missing entries in the hand-written files as part of this phase so the baseline is clean before codegen replaces them.

1. Write sql_service_roles_populate.mustache.
2. Run codegen; diff output against hand-written service-role block.
3. Extract non-service roles into iam_user_roles_populate.sql.
4. Update setup_schema.sql \ir references.
5. Verify recreate_database.sh passes.

1. Write sql_service_permissions_populate.mustache (wildcard section only).
2. Run codegen; verify output matches wildcard lines in iam_permissions_populate.sql.
3. Remove wildcard lines from iam_permissions_populate.sql; add \ir for generated file.
4. Verify recreate_database.sh passes.

1. Add permissions array to all domain entity models (guided by inventory).
2. Extend sql_service_permissions_populate.mustache to emit per-entity CRUD lines.
3. Run codegen; diff against remaining entries in iam_permissions_populate.sql.
4. Replace hand-written per-entity lines with \ir for generated output.
5. Verify recreate_database.sh passes.

1. Delete iam_permissions_populate.sql and iam_roles_populate.sql.
2. Final clean recreate_database.sh run.
3. Update developer documentation.