Skip to main content

ADR-0156: Tenant hierarchy, common fields stamped by PostgreSQL and a separate runtime login

Context​

The architecture reference places organizations and business units under the tenant (A6.1 core, A6.2 "tenant to organisations, projects, environments"), asks for the common fields environment_id, updated_by, version, validated metadata and correlation_id on the business tables (B7.2), and separates the administrator and runtime connections (B7.3).

Before this decision eai.organizations and eai.business_units did not exist, the environment was only a text column (usage_events.environment, application_deployments.environment), updated_by and version were carried by a few tables only, metadata was validated nowhere in the database, and the API and the migrations used the same login (EAI_DATABASE_URL).

Decision​

  1. Hierarchy (V162): eai.organizations (tenant, slug unique per tenant, status active or archived) and eai.business_units (organization, optional parent_id for nesting, fixed at creation so that no cycle can appear). A project is attached to an organization and one of its units (projects.organization_id, projects.business_unit_id). The foreign keys are composite with tenant_id or organization_id: a foreign key check bypasses RLS, so only a composite key prevents, even in the platform context, attaching a project or a unit to another tenant's organization. Forced RLS on both tables.
  2. Tenancy routes: GET and POST /v1/organizations, PATCH /v1/organizations/{organization_id}, GET and POST /v1/business-units, PATCH /v1/business-units/{business_unit_id}, plus organization_id, business_unit_id and metadata on projects. tenant_admin writes, any tenant role reads; every write is audited.
  3. Common fields stamped by the database (V162, V164): the trigger eai.stamp_common_fields() writes created_by and updated_by from the verified actor (app.actor_id), increments version on every update and writes trace_id (the correlation_id of the reference, the name already used by audit_events, usage_events and telemetry_spans) from app.correlation_id, which eai.db.tx sets from the W3C trace of the request. It applies to organizations, business_units, projects, environments, agents, applications and incidents. A caller can neither choose its version nor erase created_by. PATCH routes accept expected_version: a stale version is 409 version_conflict.
  4. metadata is validated in the database by eai.valid_metadata() (CHECK constraint): flat object, lower case keys (^[a-z][a-z0-9_.-]{0,62}$), text (1024 characters maximum), number, boolean or null values, 16 KB maximum. A CHECK constraint was preferred to a JSON Schema extension (pg_jsonschema): no extension to install at the client, and the rule holds for every writer, the API as well as a script. A refused metadata is 422 invalid_metadata.
  5. environment_id (V163): usage_events and application_deployments carry a foreign key to eai.environments (ON DELETE SET NULL), computed in the database by a trigger from (tenant, project by id or slug, environment name). The text column stays for the rows whose project has no such environment: environment_id is then NULL, never a guess. A value supplied by the writer is ignored and recomputed inside the row's tenant.
  6. Runtime login (V165): the group role eai_runtime (NOLOGIN, member of eai_app, no CREATE on any schema, owner of nothing, read only on schema_migrations). The operator creates the API login (CREATE ROLE eai_api LOGIN PASSWORD '...' IN ROLE eai_runtime) and gives it in EAI_RUNTIME_DATABASE_URL: the request pool and the worker use it; eai.migrate always keeps EAI_DATABASE_URL, the administrator login. Without EAI_RUNTIME_DATABASE_URL the previous behaviour stays (one login, RLS through SET LOCAL ROLE eai_app), so that no installation breaks; the production startup check requires TLS on both URLs.

Consequences​

  • Responses for projects, environments, agents, applications and incidents gain created_by, updated_by, version, metadata and trace_id.
  • A client writing concurrently on the same project or organization can pass expected_version to never overwrite a change.
  • The key EAI_RUNTIME_DATABASE_URL is added to the Control Plane OpenBao secret when the operator creates the runtime login; the chart does not require that key yet, so that the sync of an existing secret is not blocked.

Evidence​

services/control-plane/tests/test_tenancy_hierarchy.py (hierarchy, isolation between tenants down to the composite keys, stamped fields, optimistic concurrency, metadata refused in the database, environment_id resolved and recomputed, runtime login without DDL serving the API while the migration refuses it), services/control-plane/tests/test_data_model_mapping.py, services/control-plane/tests/test_security_attack_suite.py, and the request shape tests of the four SDKs.