Data Model
Multi-tenant collection design
Section titled “Multi-tenant collection design”Everything is scoped to an org_id — there is no cross-org access without switching credentials, and every collection either carries org_id directly or is keyed by a composite ID that starts with it ("{org_id}:...") — with one deliberate exception, allowed_models (platform-wide, superadmin-managed, no org_id at all).
Every collection in one diagram was unreadable — 25 entities is past the point where an ERD teaches anything. It’s split three ways instead, along the same boundary the packages are: identity and tenancy (the platform’s core), operations (also platform, but org-scoped bookkeeping rather than identity), and the analysis engine (the product).
organizations appears in all three as the tenant anchor; it’s shown in full in the first and
stubbed in the other two. Every relationship from the original diagram is preserved.
Each diagram can be opened full-screen with the ⤢ button in its corner, then zoomed with the scroll wheel and dragged to pan.
Identity and tenancy
Section titled “Identity and tenancy”Who exists, which tenant they belong to, and how they authenticate. This is the whole of
opentremor-platform’s identity surface — a product mounted on it inherits all of it.
erDiagram
organizations {
string _id "org_id (PK)"
string name
string plan_tier
object quota "monthly_budget_usd, null = unlimited"
datetime pending_deletion_at "nullable -- set by DELETE /admin/organizations/{org_id}, hard-deleted once past org_offboarding.grace_period_days"
}
users {
string _id "user_id (PK)"
string email "unique"
string password_hash "bcrypt, null for SSO-only users"
string auth_provider "local, or oidc -- set/updated on first SSO login"
string external_subject_id "the IdP's `sub` claim, when auth_provider is oidc"
bool is_superadmin "cross-tenant platform admin, independent of OrgRole"
bool is_active "account-wide -- false blocks login and kills live sessions, in every org"
int session_token_version "session generation -- bumped to revoke every session JWT this account holds"
}
memberships {
string _id "org_id:user_id (PK)"
string role "owner, admin, member, or viewer"
string provisioned_via "nullable -- scim, sso, or invite; provenance only, no access-control effect"
}
api_keys {
string _id "UUID key_id (PK)"
string org_id "FK -> organizations"
string key_hash "SHA-256 -- raw key never stored"
string role "owner, admin, member, or viewer"
bool is_active
}
teams {
string _id "org_id:team_id (PK)"
string org_id "FK -> organizations"
string team_id "slug of name, immutable after creation"
string name "no uniqueness enforced -- two teams can share a name; may originate from manual admin creation or 1:1 from a SCIM Group"
}
team_members {
string _id "org_id:team_id:user_id (PK)"
string org_id "FK -> organizations"
string team_id "FK -> teams"
string user_id "FK -> users -- must already be a member of this org"
string added_at
}
org_invites {
string invite_id "UUID (PK)"
string token_hash "SHA-256 -- raw token never stored, same pattern as report_links.token_hash"
string org_id "FK -> organizations"
string role "viewer/member/admin -- never owner, rejected at creation"
string created_by "user_id of the admin who minted it"
bool revoked "explicit revoke, independent of expiry"
datetime expires_at "TTL-indexed (expireAfterSeconds=0); reusable until this or revoked, unlike report_links (single-purpose per mint)"
}
sso_connections {
string _id "org_id (PK)"
string provider "oidc"
string issuer "OIDC issuer URL"
string client_id
string encrypted_client_secret "Fernet, never returned by the API"
string default_role "granted on JIT provisioning -- never owner"
array allowed_domains "nullable -- unique across every org's connections when set"
bool enabled
}
scim_configs {
string _id "org_id (PK)"
bool enabled
string token_hash "SHA-256 -- raw bearer token never stored, same pattern as api_keys.key_hash; uniquely indexed"
string default_role "fallback when no group_role_mappings entry matches -- never owner"
array group_role_mappings "ordered {pattern, role}; role never owner; first regex match against a user's current Team names wins"
}
organizations ||--o{ users : "via memberships"
organizations ||--o{ org_invites : "mints, reusable until expiry/revoked"
organizations ||--o| sso_connections : "connects (one OIDC provider per org)"
organizations ||--o| scim_configs : "connects (one SCIM bearer token per org)"
organizations ||--o{ teams : "owns, pure grouping -- no access-control effect (group_role_mappings is a guarded exception, driven only by scim_configs)"
teams ||--o{ team_members : "has"
users ||--o{ team_members : "belongs to (any number of teams)"
Operations
Section titled “Operations”Org-scoped bookkeeping: background work, metering, telemetry and the audit trail. Platform-owned as well, but deliberately separate from identity — nothing here decides who anyone is.
erDiagram
organizations {
string org_id PK "shown in full under Identity and tenancy above"
}
jobs {
string _id "UUID job_id (PK)"
string org_id "FK -> organizations"
string status "queued, running, done, or failed"
string trigger "api, or github_webhook"
datetime updated_at "stamped on every write; what POST /admin/jobs/sweep compares against jobs.stuck_timeout_minutes"
string llm_backend "nullable -- absent on a job created before retry support existed"
string model "nullable"
string rule_type "nullable"
string retry_of "nullable -- job_id this run resumed, if created via POST /jobs/{job_id}/retry"
}
usage_events {
string org_id "FK -> organizations"
string type "llm_tokens, ..."
float cost_usd
datetime timestamp "TTL-indexed"
}
spans {
string span_id "UUID (PK)"
string org_id
string type "request, ingest, or analysis"
datetime timestamp "TTL-indexed"
string key_id "API-key principal, nullable"
string user_id "session principal, nullable -- exactly one of key_id/user_id is set"
}
audit_events {
string event_id "UUID (PK)"
string org_id "nullable -- null for a genuinely org-less platform action, e.g. superadmin.grant"
string action "e.g. settings.update, api_key.create, org.lock"
string target_type
string target_id
string actor_user_id "nullable, XOR actor_key_id"
string actor_key_id "nullable, XOR actor_user_id"
string actor_email "denormalized at write time"
object before "nullable -- only the fields that changed, never a secret"
object after "nullable -- only the fields that changed, never a secret"
datetime timestamp "TTL-indexed on retention.audit_days -- its own independent window, no longer shared with spans/usage_events"
}
organizations ||--o{ jobs : "runs"
organizations ||--o{ audit_events : "logs privileged actions, org_id nullable for platform-wide ones"
Analysis engine
Section titled “Analysis engine”The product’s own collections. Everything here belongs to opentremor-core; a different
product mounted on the same platform would replace this diagram wholesale and leave the two
above untouched.
erDiagram
organizations {
string org_id PK "shown in full under Identity and tenancy above"
}
resources {
string _id "org_id:hash (PK)"
string org_id "FK -> organizations"
string hash
object value "type, name, action, body, metadata -- or {redacted: true, redacted_at} once the retention sweep has redacted it"
object analysis "null, or {summary, findings[]} -- survives redaction of value"
datetime last_ingested_at "stamped on creation, refreshed on every add_to_namespace call -- the age signal the retention sweep redacts value on"
}
namespace_entries {
string _id "org_id:namespace:hash (PK)"
string org_id
string namespace
string hash "FK -> resources"
datetime created_at "set once, on this (org,namespace) pair's first entry -- namespace creation date, GET /namespaces"
}
findings {
string _id "org_id:hash:rule_id (PK)"
string org_id "FK -> organizations"
string status "open, needs_review, acknowledged, suppressed, or false_positive"
int occurrence_count
}
custom_rules {
string _id "org_id:rule_id (PK)"
string org_id "FK -> organizations"
string rule_id "slug of title, immutable after creation"
string category "FK -> rule_categories.category_id -- org-owned, not a fixed enum"
string severity "CRITICAL, HIGH, MEDIUM, LOW, or INFO"
array analyzers "one rule can target multiple analyzers"
bool enabled
bool requires_review "a match always starts at needs_review, regardless of LLM confidence"
}
rule_categories {
string _id "org_id:category_id (PK)"
string org_id "FK -> organizations"
string category_id "slug of name, immutable after creation"
string name "editable -- every category is, including the 9 seeded defaults"
bool is_builtin_seed
}
installed_analyzers {
string _id "org_id:analyzer (PK)"
bool enabled
}
report_templates {
string _id "org_id:template_id (PK)"
string org_id "FK -> organizations"
string template_id "slug of name, immutable after creation"
string format "markdown, markdown_light, or html -- immutable after creation"
string source "Jinja2, rendered via SandboxedEnvironment"
bool is_default "at most one true per (org_id, format)"
bool is_builtin_seed
}
report_links {
string link_id "UUID (PK)"
string token_hash "SHA-256 -- raw token never stored, same pattern as api_keys.key_hash"
string org_id "FK -> organizations"
string namespace
datetime expires_at "TTL-indexed (expireAfterSeconds=0); also checked explicitly on every read"
}
llm_credentials {
string _id "org_id:provider (PK)"
string encrypted_key "Fernet, never returned by the API"
}
allowed_models {
string _id "llm_backend:model (PK)"
string llm_backend "anthropic, mistral, or openai"
string model "provider-specific model name"
float input_price_per_1m_usd "manual entry -- no provider exposes a pricing API"
float output_price_per_1m_usd "manual entry"
bool enabled "empty collection == unrestricted; first row turns on enforcement"
string created_by "user_id of the superadmin who added it"
}
integrations {
string _id "org_id:provider (PK)"
string installation_id "GitHub App installation, nullable until (auto-)connected"
object config "llm_backend, model, rule_type"
object github_app "nullable -- app_id, app_slug, html_url, encrypted_private_key, encrypted_webhook_secret; null = use the platform's config.github"
string url "provider=review_webhook only -- POSTed on every needs_review transition"
string encrypted_secret "provider=review_webhook only -- Fernet, nullable, never returned by the API"
bool enabled "provider=review_webhook only"
}
github_manifest_states {
string _id "state_id, UUID (PK)"
string state_hash "SHA-256 -- raw token never stored, same pattern as report_links.token_hash"
string org_id "FK -> organizations"
string user_id "the admin who started the flow"
datetime expires_at "TTL-indexed (expireAfterSeconds=0); single-use, deleted on first read regardless"
}
organizations ||--o{ resources : "owns"
resources ||--o{ namespace_entries : "referenced by"
resources ||--o{ findings : "produces"
organizations ||--o| integrations : "connects (one GitHub App per org, this pass)"
organizations ||--o{ report_links : "mints, time-limited"
organizations ||--o{ github_manifest_states : "starts, single-use"
organizations ||--o{ report_templates : "composes, optionally sets one default per format"
organizations ||--o{ rule_categories : "owns, fully editable/deletable"
Why split resources from namespace_entries instead of one collection?
One unit of content (say, a module block that a dozen Terraform stacks all reference identically) can legitimately show up under several namespaces at once. Keeping the content and its analysis in resources, and letting namespace_entries just be thin pointers into it, means that content only ever gets analysed once no matter how many namespaces end up referencing it.
Why is a resource’s analysis org-scoped but not namespace-scoped?
The resource’s identity is its content hash — a different hash means different content means a genuinely new resource document, so there’s nothing to “re-analyse per namespace” in the first place; every namespace pointing at the same hash is necessarily pointing at the same analysis. What dedup does not cross is the organization boundary: resources._id is "{org_id}:{hash}", so two orgs submitting byte-identical input each get their own independent resource and analysis. That’s deliberate — value.body can carry things like account IDs or bucket names that are themselves tenant-identifying, so even a “harmless” cache hit across orgs would leak information across a tenant boundary.
Why does findings exist separately from resources.analysis.findings?
resources.analysis.findings is raw LLM output — whatever came back from the most recent analysis of that content hash. It has no identity across re-analysis and no place to record a human decision. findings is the durable counterpart: one record per (org_id, resource_hash, rule_id), created/refreshed every time analysis_service.record_analysis() sees that triple, carrying a status a human can change via PATCH /findings/{hash}/{rule_id} — and that status survives re-analysis of the same resource rather than resetting.