Skip to content

ERD v0.3 - Schema Freeze for Implementation

1. Goal

Define the production implementation data model aligned to: - PRD policy-config architecture - OpenAPI/AsyncAPI contracts - financial integrity (minor units) - idempotency/outbox reliability patterns

Identifier convention: - Canonical external resource names follow doc/architecture/Resource_Identifier_Spec.md. - resource_name is generated at API/event/audit boundaries and is not a primary key column.

2. Core Entities

organizations

  • id (PK uuid)
  • type (personal/enterprise)
  • name, slug
  • stripe_customer_id (nullable; tenant billing anchor)
  • created_at

projects

  • id (PK uuid)
  • org_id (FK organizations.id)
  • name, slug
  • created_at

users

  • id (PK uuid)
  • org_id (FK organizations.id, nullable; transitional field, authz must use membership tables)
  • username (unique)
  • oidc_issuer, oidc_subject (paired external identity anchor)
  • password_hash (nullable)
  • role (user/admin)
  • stripe_customer_id (nullable; transitional field, tenant billing uses organizations.stripe_customer_id)
  • last_low_balance_notified_at
  • created_at Constraint:
  • OIDC identity is unique on (oidc_issuer, oidc_subject) when present.
  • at least one auth anchor required: (oidc_issuer + oidc_subject) or password_hash.

account_sessions

  • id (PK uuid)
  • user_id (FK users.id)
  • provider (oidc/personal_token/service_account/password/unknown)
  • provider_session_id (nullable; upstream session identifier such as OIDC session_state/sid)
  • latest_token_id (latest JWT jti observed for local denylist enforcement)
  • device_label, user_agent, browser, os, ip_address, location_label
  • signed_in_at, last_seen_at, expires_at
  • revoked_at (nullable), revoked_by_user_id (nullable FK users.id), revoke_reason (nullable)
  • created_at, updated_at Constraints:
  • active provider sessions are unique per (user_id, provider_session_id) when provider_session_id is present.
  • token-only fallback sessions are unique per (user_id, latest_token_id) when provider_session_id is absent. Lifecycle note:
  • Account session revocation marks rows as revoked and denies the latest tracked token locally; refresh also checks revoked provider-session rows to prevent re-entry through a new access token.

tenant_memberships

  • id (PK uuid)
  • org_id (FK organizations.id)
  • user_id (FK users.id)
  • role (owner/admin/member/billing_viewer)
  • created_at
  • deleted_at (nullable)
  • deleted_by_user_id (nullable FK users.id)
  • delete_reason (nullable) Constraints:
  • active-row unique (org_id, user_id) where deleted_at is null.
  • MVP active-row constraint: unique (user_id) where deleted_at is null to enforce single-tenant membership until multi-tenant user support is enabled.
  • index (user_id, org_id) where deleted_at is null for membership resolution. Lifecycle note:
  • Membership rows are soft-deleted for audit/forensics continuity; authorization queries must only consider rows where deleted_at is null.

project_memberships

  • id (PK uuid)
  • project_id (FK projects.id)
  • user_id (FK users.id)
  • role (owner/admin/member/viewer)
  • created_at
  • deleted_at (nullable)
  • deleted_by_user_id (nullable FK users.id)
  • delete_reason (nullable) Constraints:
  • active-row unique (project_id, user_id) where deleted_at is null.
  • index (user_id, project_id) where deleted_at is null for project-scope authorization. Lifecycle note:
  • Authorization queries must only consider rows where deleted_at is null.

role_definitions

  • id (PK uuid)
  • scope_type (platform/tenant/project)
  • scope_id (nullable for platform)
  • name
  • is_builtin (bool)
  • is_assignable_to_service_accounts (bool)
  • state (active/disabled/deprecated)
  • current_version_id (nullable FK role_definition_versions.id)
  • created_at, updated_at Constraints:
  • scope_type=platform requires scope_id is null; tenant/project scopes require non-null scope_id.
  • unique platform role names for platform scope.
  • unique role names per (scope_type, scope_id) for non-platform scopes.

role_definition_versions

  • id (PK uuid)
  • role_definition_id (FK role_definitions.id)
  • version (integer >= 1)
  • permissions (jsonb)
  • change_reason (nullable)
  • created_by_user_id (nullable FK users.id)
  • created_at Constraints:
  • unique (role_definition_id, version)

platform_role_bindings

  • id (PK uuid)
  • principal_type (user/service_account)
  • principal_id
  • role_definition_id (FK role_definitions.id)
  • created_at
  • deleted_at (nullable)
  • deleted_by_user_id (nullable FK users.id)
  • delete_reason (nullable) Constraints:
  • unique active row per (principal_type, principal_id, role_definition_id) where deleted_at is null.

tenant_role_bindings

  • id (PK uuid)
  • tenant_id (FK organizations.id)
  • principal_type (user/service_account)
  • principal_id
  • role_version_id (FK role_definition_versions.id)
  • created_at
  • deleted_at (nullable)
  • deleted_by_user_id (nullable FK users.id)
  • delete_reason (nullable) Constraints:
  • unique active row per (tenant_id, principal_type, principal_id, role_version_id) where deleted_at is null.

project_role_bindings

  • id (PK uuid)
  • project_id (FK projects.id)
  • principal_type (user/service_account)
  • principal_id
  • role_version_id (FK role_definition_versions.id)
  • created_at
  • deleted_at (nullable)
  • deleted_by_user_id (nullable FK users.id)
  • delete_reason (nullable) Constraints:
  • unique active row per (project_id, principal_type, principal_id, role_version_id) where deleted_at is null.

user_ssh_public_keys

  • id (PK uuid)
  • user_id (nullable FK users.id)
  • project_id (nullable FK projects.id)
  • created_by_service_account_id (nullable FK service_accounts.id)
  • name
  • public_key
  • fingerprint
  • is_default (bool)
  • last_used_at (nullable)
  • created_at
  • revoked_at (nullable) Constraints:
  • exactly one ownership scope per row: personal (user_id) or project (project_id)
  • fingerprint unique per personal owner for active (non-revoked) keys
  • fingerprint unique per project owner for active (non-revoked) keys
  • at most one active default key per personal owner
  • project-scoped keys cannot be default keys
  • public_key stores OpenSSH-formatted public key material only.

allocation_ssh_public_keys

  • id (PK uuid)
  • allocation_id (FK allocations.id, cascade delete)
  • ssh_public_key_id (FK user_ssh_public_keys.id)
  • created_at Constraints:
  • unique per (allocation_id, ssh_public_key_id)
  • supports provider-style multi-key install on a single allocation.

user_posix_identities

  • id (PK uuid)
  • user_id (FK users.id, unique)
  • username_on_node (unique, linux-safe)
  • uid (unique, reserved platform-safe range)
  • gid (unique, reserved platform-safe range)
  • supplemental_gids (int array)
  • created_at, updated_at Constraints:
  • Exactly one stable POSIX identity per user.
  • Identity values are immutable for runtime compatibility across multi-node clusters.

sku_catalog

  • sku (PK)
  • vendor
  • display_name
  • gpus_total
  • capacity_shape (baremetal/gpu_slice)
  • allowed_gpu_counts (integer array; non-empty for selectable slice products)
  • resource_profile (jsonb SKU-level capacity/topology hints)
  • gpu_hourly_price_minor (bigint)
  • currency (ISO-4217)
  • active

maas_sites

  • id (PK uuid)
  • name (unique)
  • region_code
  • api_base_url
  • api_token_vault_path (unique)
  • default_power_creds_vault_path (unique)
  • pxe_iface
  • pxe_vlan_vid
  • node_pxe_iface
  • distro_series
  • architecture
  • deploy_user
  • deploy_password_vault_path (unique)
  • deploy_ssh_iface
  • upstream_dns_servers (text array)
  • status (active/disabled)
  • credentials_configured_at
  • last_probe_at, last_probe_ok, last_probe_error
  • last_probe_version, last_probe_subversion, last_probe_capabilities
  • created_at, updated_at, disabled_at Notes:
  • stores only non-secret site metadata plus Vault logical paths
  • last probe fields are the operator-facing cached MAAS connectivity snapshot

maas_site_policies

  • site_id (PK/FK maas_sites.id, cascade delete)
  • strict_pxe_preflight
  • enable_phase2_roce
  • require_hw_sync
  • hardware_sync_interval
  • release_fallback_no_erase
  • enable_deploy_retry_on_datasource_failure
  • max_deploy_retry_attempts
  • auto_claim_single_new_machine
  • batch_max_parallel
  • extra_cloud_init_bundle_path
  • enrollment_token_ttl_seconds
  • created_at, updated_at Notes:
  • separates stable site identity from mutable workflow policy
  • deploy identity and Vault paths remain site-scoped
  • PXE settings on the site row act as site defaults and may be overridden by profiles when needed
  • current first-slice implementation uses this row as the site's implicit default profile

maas_site_profiles

  • id (PK uuid)
  • site_id (FK maas_sites.id, cascade delete)
  • name
  • status (active/disabled)
  • is_default (boolean)
  • pxe_iface (nullable override)
  • pxe_vlan_vid (nullable override)
  • node_pxe_iface (nullable override)
  • distro_series
  • architecture
  • strict_pxe_preflight
  • enable_phase2_roce
  • require_hw_sync
  • hardware_sync_interval
  • release_fallback_no_erase
  • enable_deploy_retry_on_datasource_failure
  • max_deploy_retry_attempts
  • auto_claim_single_new_machine
  • batch_max_parallel
  • extra_cloud_init_bundle_path
  • enrollment_token_ttl_seconds
  • created_at, updated_at, disabled_at Constraints:
  • unique per (site_id, name)
  • unique default profile per site_id Notes:
  • one MAAS site can expose multiple operational policy bundles
  • deploy user and deploy credential path stay on the site; profiles do not override login identity
  • architecture and distro series are profile-scoped
  • PXE fields are site defaults with profile-level optional overrides
  • current site-management backend keeps maas_site_policies and the is_default=true profile synchronized for compatibility
  • onboarding/decommission requests should eventually reference both site_id and profile_id

maas_power_credential_overrides

  • id (PK uuid)
  • site_id (FK maas_sites.id, cascade delete)
  • selector_type (hostname/ipmi_ip/pxe_mac)
  • selector_value
  • vault_path (unique)
  • status (active/disabled)
  • created_at, updated_at Constraints:
  • unique per (site_id, selector_type, selector_value)
  • lets a site keep one default credential set plus explicit node/rack exceptions

maas_roce_assignments

  • id (PK uuid)
  • site_id (FK maas_sites.id, cascade delete)
  • hostname
  • interface
  • ipv4_cidr
  • created_at, updated_at Constraints:
  • unique per (site_id, hostname, interface)
  • replaces CSV-based phase-2 RoCE assignment state with typed records

node_onboardings

  • onboarding_id (PK uuid)
  • batch_id (nullable uuid)
  • node_id (nullable FK nodes.id)
  • site_id (FK maas_sites.id)
  • hostname
  • ipmi_ip (inet)
  • pxe_mac
  • maas_system_id
  • requested_by_user_id (nullable FK users.id)
  • current_stage
  • current_attempt
  • status (pending/running/completed/failed_retryable/failed_manual_intervention/cancelled/compensating/reconciled)
  • error_code, error_message, error_details
  • workflow_id (unique), workflow_run_id
  • requested_at, started_at, completed_at, updated_at Notes:
  • draft workflow read model for MAAS onboarding visibility
  • workflow-oriented details live here, not in nodes.status

node_onboarding_events

  • id (PK uuid)
  • onboarding_id (FK node_onboardings.onboarding_id, cascade delete)
  • stage
  • attempt
  • status (started/succeeded/failed/compensated/skipped)
  • message
  • details
  • occurred_at Notes:
  • append-only operator/event history for one onboarding workflow

node_decommissions

  • decommission_id (PK uuid)
  • node_id (FK nodes.id)
  • site_id (nullable FK maas_sites.id)
  • maas_system_id
  • mode (soft_reset/reimage/full_decommission/storage_cleanup)
  • status (pending/running/completed/failed_retryable/failed_manual_intervention/cancelled/compensating/reconciled)
  • current_stage
  • current_attempt
  • requested_by_user_id (nullable FK users.id)
  • error_code, error_message, error_details
  • workflow_id (unique), workflow_run_id
  • requested_at, started_at, completed_at, updated_at Notes:
  • draft workflow read model for MAAS decommission visibility

node_decommission_events

  • id (PK uuid)
  • decommission_id (FK node_decommissions.decommission_id, cascade delete)
  • stage
  • attempt
  • status (started/succeeded/failed/compensated/skipped)
  • message
  • details
  • occurred_at

node_maas_state

  • node_id (PK/FK nodes.id, cascade delete)
  • site_id (FK maas_sites.id)
  • maas_system_id
  • last_maas_status
  • last_maas_power_state
  • last_maas_ips (text array)
  • last_reconciled_at
  • drift_detected
  • drift_details
  • drift_resolved_at Notes:
  • authoritative runtime reconciliation snapshot for the MAAS binding of an enrolled node
  • distinct from historical onboarding/decommission workflow records

nodes

  • id (PK uuid)
  • org_id (FK organizations.id, nullable)
  • onboarding_mode (manual/maas)
  • region_code
  • host, hostname, port
  • ssh_username
  • access_method
  • access_secret_enc
  • sku (FK sku_catalog.sku)
  • gpus_total
  • status (registered/bootstrap_issued/enrolling/active/offline/quarantined/draining/retired/removing)
  • status_changed_at
  • created_at API projection note:
  • Admin node APIs include derived occupancy context (current_allocation) from allocations in active/releasing/release_failed states.
  • Admin/public node APIs expose occupancy_status (available/assigned/releasing/cleanup/unavailable) as a projection: lifecycle state controls schedulability; allocations control current occupancy. Node tenancy semantics:
  • org_id is null => shared/unassigned pool node.
  • org_id is not null => tenant-dedicated node.

node_resource_slots

  • id (PK uuid)
  • node_id (FK nodes.id, cascade delete)
  • parent_slot_id (nullable self-FK; reserved for future child slots under a parent physical GPU)
  • slot_index
  • shape (gpu_slice)
  • status (available/reserved/provisioning/active/releasing/cleanup/cleanup_blocked/failed/disabled)
  • sharing_model (exclusive_device, nullable; v1 accepts only whole-device exclusive slots)
  • profile_name (nullable runtime profile name; v1 uses whole-GPU VM profiles, not vGPU/MIG profiles)
  • gpu_model, gpu_count, gpu_pci_addr, gpu_uuid, mig_profile
  • gpu_memory_mib, compute_milli, max_claims (nullable future fractional/shared capacity dimensions)
  • nvme_device
  • network_fabric (infiniband/roce/ethernet/none/any)
  • network_device, mac_address, private_ip
  • fabric_pci_addr, fabric_device_id, fabric_metadata
  • numa_node
  • vcpu_count, memory_mib
  • capacity_metadata, health_metadata
  • created_at, updated_at Constraints:
  • unique per (node_id, slot_index) Notes:
  • rows represent approved schedulable bundles, not raw discovery candidates
  • candidate discovery output must be reviewed or matched to a site topology profile before a slot becomes available
  • one slot can be claimed by at most one active/reserved allocation claim
  • fractional/shared GPU inventory is reserved for a later phase and is not accepted by the v1 admin slot API; current schedulable slots are whole-GPU exclusive VM slots

allocations

  • id (PK uuid)
  • org_id (FK organizations.id, not null)
  • project_id (FK projects.id, not null)
  • requested_by_user_id (FK users.id, attribution only)
  • node_id (FK nodes.id, nullable for scheduler flows/pre-assignment states)
  • username_on_node (snapshot of user_posix_identities.username_on_node at allocation time; stable per-user across all nodes)
  • host_snapshot, hostname_snapshot, port_snapshot (nullable snapshots)
  • region_code
  • scheduler_type (bare_metal/slurm/k8s/ray)
  • scheduler_ref, scheduler_metadata
  • sku_snapshot, gpus_total_snapshot
  • capacity_shape (baremetal/gpu_slice)
  • placement_status (unplaced/reserved/provisioning/active/releasing/released/failed)
  • placement_metadata (jsonb read-model metadata, not scheduling source of truth)
  • status (requested/provisioning/active/releasing/released/failed/release_failed)
  • provisioning_started_at
  • active_at
  • ssh_private_key_enc, ssh_public_key (deprecated transitional columns; not required for runtime user access path)
  • failure_reason (nullable, user-facing machine-readable reason when status=failed)
  • release_failed_reason (nullable, user-facing reason when status=release_failed)
  • created_at, released_at, release_failed_at

Constraints: - Max one active baremetal allocation per node (partial unique index on node_id where status=active and capacity_shape=baremetal) - project/tenant consistency: allocation project must belong to allocation tenant - Per-allocation SSH key set is optional and stored in allocation_ssh_public_keys. - If no override rows exist for an allocation, runtime uses the user's active default SSH key. - Bare-metal active/releasing/released allocations require non-null node_id - username_on_node must be derived from stable per-user POSIX identity, not per-allocation random generation - provisioning_started_at required for provisioning/active/releasing/released/release_failed statuses - active_at required for active/releasing/released/release_failed statuses - active/releasing/released/release_failed statuses require non-null host_snapshot and port_snapshot for connection UX Security note: - scheduler_metadata may include scheduler integration credentials in Phase-2 paths; credential-bearing values must be envelope-encrypted with the same canonical format used by _enc columns.

allocation_resource_claims

  • id (PK uuid)
  • allocation_id (FK allocations.id, cascade delete)
  • node_id (FK nodes.id)
  • slot_id (nullable FK node_resource_slots.id)
  • claim_kind (node_exclusive/slot)
  • status (reserved/provisioning/active/releasing/released/failed)
  • resource_snapshot (jsonb immutable claim-time resource snapshot)
  • created_at, released_at Constraints:
  • node_exclusive claims must have slot_id = null
  • slot claims must have slot_id is not null
  • one active/reserved node-exclusive claim per node
  • one active/reserved slot claim per slot Notes:
  • this is the placement source of truth for both baremetal and slices
  • allocations.node_id remains a compatibility/read-model field during migration

service_accounts

  • id (PK uuid)
  • org_id (FK organizations.id)
  • project_id (FK projects.id)
  • name, slug
  • description (nullable)
  • state (active/disabled/deleted)
  • created_by_user_id (FK users.id)
  • created_at
  • disabled_at (nullable)
  • deleted_at (nullable) Constraints:
  • unique (project_id, slug)
  • project/tenant consistency: service account project must belong to service account tenant

service_account_credentials

  • id (PK uuid)
  • service_account_id (FK service_accounts.id)
  • key_id
  • public_jwk
  • private_key_enc
  • algorithm
  • state (active/rotated/revoked)
  • created_at
  • expires_at (nullable)
  • revoked_at (nullable) Constraints:
  • unique (service_account_id, key_id)

access_credentials

  • id (PK uuid)
  • display_name
  • description (nullable)
  • scope_type (platform/tenant/project)
  • scope_id (nullable only for platform)
  • resource_type (nullable)
  • resource_id (nullable)
  • operational_domain (platform-control/platform-ops/tenant-runtime)
  • credential_kind (ssh_key/password/token/certificate_bundle)
  • usage_tags (jsonb array)
  • principal_username (nullable)
  • custody_backend (vault)
  • custody_instance
  • vault_path (nullable, unique when present)
  • delivery_mode (vault_ref/vault_wrapped_token)
  • status (active/disabled/rotation_pending/expired/deleted)
  • last_validated_at (nullable)
  • last_used_at (nullable)
  • rotation_due_at (nullable)
  • created_at
  • updated_at
  • deleted_at (nullable) Constraints:
  • scope_type=platform requires scope_id is null
  • tenant/project scope requires scope_id
  • usage_tags must remain a JSON array
  • vault_path unique when present Notes:
  • DB stores metadata and custody reference only; secret material remains in Vault.
  • attachment target (resource_type/resource_id) is separate from hierarchy scope.
  • this resource is intended to unify machine-access and OCI-style access credentials under one platform model.

node_agent_lifecycles

  • lifecycle_id (PK uuid)
  • node_id (FK nodes.id)
  • requested_by_user_id (FK users.id, nullable)
  • mode (reimage/manual_install/rebootstrap)
  • scenario (bootstrap_install/in_place_upgrade/repair_reinstall/certificate_repair/drift_reconcile)
  • status (pending/running/completed/failed_retryable/failed_manual_intervention/cancelled)
  • desired_agent_version (nullable)
  • reported_agent_version (nullable)
  • safety_policy (idle_only/drain_then_upgrade/force)
  • error_code (nullable)
  • error_message (nullable)
  • error_details (jsonb)
  • correlation_id
  • requested_at
  • started_at (nullable)
  • completed_at (nullable)
  • updated_at Notes:
  • records explicit node-agent lifecycle runs instead of overloading MAAS decommission state
  • current node lifecycle state is derived from the most recent run plus desired/reported version fields returned by the admin API

usage_records

  • id (PK uuid)
  • org_id, project_id
  • allocation_id (FK allocations.id)
  • requested_by_user_id (FK users.id)
  • region_code
  • sku (FK sku_catalog.sku)
  • start_time, end_time, last_billed_at
  • accrued_cost_minor (bigint)
  • currency (ISO-4217) Constraints:
  • end_time must be null or greater than start_time
  • last_billed_at must be null or greater/equal to start_time

ledger_entries

  • id (PK uuid)
  • org_id, project_id
  • requested_by_user_id (FK users.id)
  • entry_type (usage_debit/stripe_credit/admin_credit/refund_credit/adjustment)
  • amount_minor (signed bigint, non-zero)
  • currency (ISO-4217)
  • reference_type, reference_id
  • metadata
  • created_at

Note: - Balance is projection from ledger sum; no mutable balance source of truth. - refund_credit is used for internal balance credits. - Provider-card refunds are tracked in refund_requests and do not mutate platform balance ledger by default.

refund_requests

  • id (PK uuid)
  • org_id (FK organizations.id, not null)
  • user_id (FK users.id; refund beneficiary/target user)
  • requested_by_user_id (nullable FK users.id; actor attribution)
  • amount_minor, currency
  • reason
  • payment_reference
  • mode (auto_policy/force_internal_credit)
  • outcome (provider_refund/internal_credit, nullable until resolved)
  • status (accepted/completed/failed)
  • policy_applied (jsonb)
  • provider_refund_id (nullable)
  • internal_ledger_entry_id (nullable FK ledger_entries.id)
  • correlation_id
  • created_at, updated_at Constraint:
  • outcome=internal_credit requires internal_ledger_entry_id
  • outcome=provider_refund requires internal_ledger_entry_id null

stripe_events

  • id (PK text provider event id)
  • event_type
  • payload (jsonb)
  • processed_at

payment_sessions

  • id (PK uuid)
  • org_id (FK organizations.id, not null)
  • initiated_by_user_id (FK users.id; actor attribution for checkout initiation)
  • stripe_checkout_session_id (unique)
  • stripe_payment_intent_id (nullable)
  • idempotency_key (nullable)
  • requested_amount_minor
  • credited_amount_minor (nullable)
  • currency
  • status (initiated/checkout_completed/credited/failed_reconcile/expired)
  • initiated_at
  • checkout_completed_at, credited_at, failed_at (nullable)
  • failure_reason (nullable)
  • stripe_event_id (nullable FK stripe_events.id)
  • ledger_entry_id (nullable FK ledger_entries.id)
  • correlation_id
  • created_at Constraints:
  • idempotency uniqueness on (org_id, initiated_by_user_id, idempotency_key) when idempotency key is present.
  • session remains tenant-owned while retaining user attribution for audit and reconciliation.

audit_logs

  • id (PK uuid)
  • org_id (nullable FK; null only for platform-scoped system events with no tenant context)
  • actor_user_id (nullable FK users.id)
  • actor_service_account_id (nullable FK service_accounts.id)
  • actor_role (user/admin/service_account/system)
  • action
  • target_type, target_id
  • result (success/failure)
  • correlation_id
  • metadata
  • occurred_at

idempotency_keys

  • id (PK uuid)
  • org_id (nullable FK organizations.id; null for platform-scoped idempotency domains)
  • request_scope
  • idempotency_key
  • request_hash
  • response_code, response_body
  • replayable (boolean)
  • resource_type, resource_id
  • created_at, expires_at
  • TTL baseline: 24h (idempotency.key_ttl_seconds planned policy key)
  • uniqueness:
  • tenant-scoped: (org_id, request_scope, idempotency_key)
  • platform-scoped (org_id null): (request_scope, idempotency_key) Constraints:
  • replayable responses are only persisted for non-5xx results

outbox_events

  • id (PK uuid)
  • aggregate_type, aggregate_id
  • event_type
  • payload
  • correlation_id
  • status (pending/published/failed)
  • retry_count
  • last_attempted_at, occurred_at, published_at Constraint:
  • published status requires non-null published_at

policy_definitions

  • policy_key (PK text)
  • description
  • value_type (integer/string/boolean/json/duration)
  • default_value (jsonb)
  • bounds (jsonb)
  • created_at

policy_values

  • id (PK uuid)
  • policy_key (FK policy_definitions.policy_key)
  • scope_type (global/plan/org/project/user)
  • scope_id (uuid nullable, polymorphic by scope_type)
  • value (jsonb)
  • effective_at
  • created_by_user_id (nullable FK users.id)
  • reason
  • correlation_id (nullable for bootstrap/seed writes; required for runtime policy mutations)
  • created_at Constraints:
  • global scope must have null scope_id
  • non-global scope must have non-null scope_id
  • uniqueness enforced via partial indexes:
  • global: (policy_key, effective_at)
  • scoped: (policy_key, scope_type, scope_id, effective_at) Polymorphic scope mapping (application-enforced):
  • scope_type='org' -> scope_id references organizations.id
  • scope_type='project' -> scope_id references projects.id
  • scope_type='plan' -> scope_id references pricing_plans.id
  • scope_type='user' -> scope_id references users.id (retained for future compatibility; out of MVP evaluation chain) Resolution order:
  • MVP chain: global -> org -> project (more specific wins).
  • Phase-2 extension with pricing plans: global -> plan -> org -> project (more specific wins).

policy_change_events

  • id (PK uuid)
  • policy_value_id (FK policy_values.id)
  • before_value, after_value
  • changed_by_user_id (nullable FK users.id)
  • correlation_id
  • changed_at

storage_objects

  • id (PK uuid)
  • org_id (FK organizations.id, not null)
  • project_id (FK projects.id, not null)
  • created_by_user_id (FK users.id)
  • path (unique per project)
  • object_type (file/dir)
  • size_bytes
  • updated_at

subscriptions (Phase-2 additive)

  • id (PK uuid)
  • org_id, plan_id
  • status (pending/active/cancelled/expired)
  • starts_at, ends_at Constraint:
  • ends_at is null for open-ended subscriptions or strictly greater than starts_at

commit_contracts (Phase-2 additive)

  • id (PK uuid)
  • org_id
  • commitment_minor, currency
  • starts_at, ends_at
  • terms Constraint:
  • ends_at is nullable to support evergreen contracts; when present, ends_at > starts_at

invoice_headers (Phase-2 additive)

  • id (PK uuid)
  • org_id
  • period_start, period_end
  • status (draft/open/paid/void)
  • currency, total_minor Constraint:
  • period_end > period_start

3. Relationship Summary

  • organization 1..* projects, memberships, nodes, policy/finance records
  • project 1..* allocations, storage_objects, service_accounts
  • user 1..* attribution records (requested_by_*), SSH keys, POSIX identity
  • node 1..* allocations (max 1 active at a time)
  • allocation 1..* usage_records
  • user 1..* tenant/project memberships, audit logs (actor), policy change records
  • policy_definitions 1..* policy_values
  • policy_values 1..* policy_change_events

4. Contract Alignment Notes

  • Money fields use minor units + currency across data model.
  • Allocation status enum matches API state machine fully.
  • Refund API maps directly to refund_requests outcome/status/policy_applied.
  • Audit APIs map to enriched audit_logs with correlation and result.
  • Async and retry reliability map to outbox_events + idempotency_keys.
  • OIDC-first auth maps to users.(oidc_issuer, oidc_subject); local password path remains optional for dev-only compatibility.

5. Prototype Migration Mapping (Reference)

  • users.balance -> opening ledger_entries credit/debit projection seed
  • usage.cost -> usage_records.accrued_cost_minor (currency normalized)
  • stripeEvents[] -> stripe_events
  • nodes.assignedAllocationId -> derived via active allocation index
  • no production carry-forward for JSON-file persistence patterns

6. Phase-2 Extensibility Guarantees

  • Tenant/project scope fields already present on core workload and billing tables.
  • Scheduler-agnostic fields present (scheduler_type, scheduler_ref, metadata).
  • Enterprise billing projection tables remain additive.
  • Policy hierarchy tables support global/plan/org/project rollout without schema rewrite (user scope retained for forward compatibility only).