Skip to content
DashboardWebsite

Data Model

Most tenant data is scoped to an org_id. Many collections carry it directly; others use a composite ID that starts with it ("{org_id}:..."). Some records are user-scoped or platform-wide instead. Platform-wide collections include allowed_models, backups, backup_keys, address_lists, network_rules, network_counters, and oauth_clients. network_events can also carry an org_id for an org-scoped event. builtin_rule_syncs has no org_id either: it records which version of the pack files was synced.

Putting every collection in one diagram made it unreadable. The diagrams are split three ways along the same boundaries as the packages: identity and tenancy (the platform’s core), operations (platform-wide and org-scoped bookkeeping rather than identity), and the analysis engine (the product).

organizations appears in all three as the tenant anchor. It is shown in full in the first diagram and stubbed in the other two. Every relationship from the original diagram is preserved.

Open each diagram full-screen with the ⤢ button in its corner. Zoom with the scroll wheel and drag to pan.

This section shows who exists, which tenant they belong to, and how they authenticate. It covers the full identity surface of opentremor-platform. A product mounted on it inherits that surface.

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 /v1/admin/organizations/{org_id}, hard-deleted once past org_offboarding.grace_period_days"
        bool locked "set by a platform admin, and on deletion -- a locked org refuses its members"
        string agent_access "allowed, read_only or forbidden -- what access tokens and OAuth agents may do here"
        object injection_settings "core-owned: semantic scoring switch, connection_id, model, knowledge_sync_enabled, gate"
        object code_suggestions "core-owned: enabled, min_severity, max_per_review -- a repository can override enabled"
    }
    users {
        string _id "user_id (PK)"
        string email "unique"
        string password_hash "bcrypt, null for SSO-only users"
        string auth_provider "how the account was created: local, oidc or saml (org SSO), google or microsoft -- never changed by linking"
        array identities "linked sign-in identities, looked up by (provider, subject), never by email: google/microsoft, or sso:<org_id> with subject <issuer>|<sub>"
        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"
        string last_org_id "nullable -- where this account was last scoped; read at login to pick a landing org, re-checked against a live active membership, never an authorization input"
    }
    memberships {
        string _id "org_id:user_id (PK)"
        string role "owner, admin, member, viewer, auditor, or custom:<slug>"
        bool is_active "org-scoped suspension -- distinct from users.is_active; a member deactivated here keeps full access to their other orgs"
        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, viewer, auditor, pipeline, or custom:<slug>"
        string name "nullable -- a label for the key list"
        array ip_allowlist "nullable -- IPs or CIDRs the key is accepted from"
        bool is_active
    }
    access_tokens {
        string token_id "PK"
        string token_hash "SHA-256, unique -- raw token never stored"
        string user_id "FK -> users -- the token acts as this person"
        string name
        string scope "what the token may do, never more than its person"
        string org_id "nullable -- one org, or every org of its person"
        int session_version "the person's session generation at creation -- a bump revokes the token"
        datetime expires_at "TTL-indexed"
    }
    oauth_clients {
        string client_id "PK -- dynamic client registration"
        string client_name
        array redirect_uris
    }
    oauth_codes {
        string _id "the authorization code's hash -- single use"
        datetime expires_at "TTL-indexed backstop; checked on use"
    }
    oauth_grants {
        string grant_id "PK -- one connected app"
        string refresh_hash "SHA-256, unique"
        string user_id "FK -> users"
        string client_id "FK -> oauth_clients"
        string scope
        string org_id "nullable"
        datetime expires_at "TTL-indexed"
    }
    users ||--o{ access_tokens : "lends to an agent"
    users ||--o{ oauth_grants : "consents to"
    oauth_clients ||--o{ oauth_grants : "holds"
    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 email "nullable -- set makes the invite addressed: only that account may redeem it, and it is listed by GET /v1/auth/me/invitations. Null = open bearer link, discoverable by nobody"
        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 or saml -- the API calls it protocol; fixed at creation"
        string issuer "OIDC only: issuer URL"
        string client_id "OIDC only"
        string encrypted_client_secret "OIDC only: Fernet, never returned by the API"
        string idp_entity_id "SAML only: the assertions' Issuer"
        string idp_sso_url "SAML only: HTTP-Redirect sign-in URL"
        array idp_certificates "SAML only: PEM signing certificates, several to overlap a rotation"
        string email_attribute "SAML only, nullable"
        string default_role "granted on JIT provisioning -- never owner"
        array allowed_domains "nullable -- restricts who may sign in"
        array verified_domains "allowed domains whose DNS names the org; one org at most per domain"
        bool enabled
    }
    saml_responses {
        string _id "org_id|assertion_id -- a second insert is a replay"
        string ticket_hash "SHA-256 of the one-time ticket; unset once spent"
        datetime ticket_expires_at "2 minutes after the ACS accepted the response"
        object sign_in "in_response_to, email, subject; unset once spent"
        datetime expires_at "TTL-indexed: the assertion's NotOnOrAfter"
    }
    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 or SAML provider per org)"
    organizations ||--o| scim_configs : "connects (one SCIM bearer token per org)"
    organizations ||--o{ teams : "owns -- teams grant no access (group_role_mappings is a guarded exception, driven only by scim_configs); they own repositories and scope notification channels"
    teams ||--o{ team_members : "has"
    users ||--o{ team_members : "belongs to (any number of teams)"
    org_roles {
        string _id "{org_id}:{role_id} (PK)"
        string org_id "FK -> organizations"
        string name
        list permissions "permission names this custom role grants"
    }
    user_tokens {
        string _id "token_hash (PK) -- the raw token is never stored"
        string user_id "FK -> users"
        string purpose "password reset or email verification"
        datetime expires_at "TTL-indexed"
    }
    organizations ||--o{ org_roles : "defines custom roles"
    users ||--o{ user_tokens : "has single-use links"

This section covers org-scoped bookkeeping: background work, metering, telemetry, and the audit trail. The platform owns these collections, but they are separate from identity. Nothing here determines 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, failed, or cancelled"
        string trigger "api, scm_webhook (external_ref.provider names the host), or repository_scan"
        string request_id "nullable -- the X-Request-ID of the request that queued it"
        datetime updated_at "stamped on every write; what POST /v1/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 /v1/jobs/{job_id}/retry"
        object injection_scoring "nullable -- scored, stopped, cost_usd: what semantic injection scoring did on this run"
    }
    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_token_id "nullable -- set when a person acted through an access token or OAuth agent"
        string request_id "the X-Request-ID of the request that took the action"
        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"
    notification_channels {
        string _id "{org_id}:{channel_id} (PK)"
        string org_id "FK -> organizations"
        string kind "slack, teams, email, or webhook"
        string target_url "encrypted, write-only -- slack, teams and webhook only"
        list recipients "user ids -- email only"
        list events "subscribed event names"
        list team_ids "null = org-wide; otherwise only events for one of these teams"
    }
    notification_apps {
        string _id "{org_id}:{provider} (PK)"
        string client_id "an org's own Slack app"
        string client_secret "encrypted, write-only"
    }
    organizations ||--o{ notification_channels : "delivers events through"
    organizations ||--o| notification_apps : "registers a Slack app"
    audit_sinks {
        string sink_id "PK"
        string org_id "nullable -- null for a platform sink, which receives every org's events"
        string name
        object transport "https (JSON or Splunk HEC) or syslog over TLS; secrets encrypted"
        array actions "nullable -- the actions it forwards; null forwards all"
        object cursor "stored_at and event_id of the last event delivered"
        string lease_id "the drain that holds it; lease_until beside it"
        int consecutive_failures "next_attempt_at backs off from it"
    }
    backups {
        string run_id "PK"
        string status "running, succeeded, or failed"
        string trigger "schedule or manual"
        string target "local or s3"
        string location "the encrypted file's path or object key"
        object documents "documents written, per collection"
        int recipients "age recipients the file is encrypted to"
        datetime started_at
    }
    backup_keys {
        string key_id "PK"
        string recipient "age public key -- the private key never reaches the server"
        string label "where the identity is kept"
        datetime active_from "requested_at plus the change delay"
        datetime removed_from "nullable -- a removal waits the same delay"
    }
    address_lists {
        string list_id "PK"
        string name "unique"
        string entry_type "ip or host"
        string source "static, url, or file"
        object status "last_success_at, last_error, entry_count"
    }
    network_rules {
        string rule_id "PK"
        string service "api, dashboard, or docs"
        string path_prefix
        array lists "address_lists names"
        array cidrs
        string mode "report or enforce"
    }
    network_events {
        string layer "rule or key -- which check refused"
        string ref_id "the refusing rule_id or key_id"
        string org_id "nullable"
        datetime timestamp "the minute; repeats in it increment count"
        int count
    }
    network_counters {
        string rule_id
        string hour "one counter per rule per hour"
        int count
        datetime expires_at "TTL-indexed"
    }
    organizations ||--o{ audit_sinks : "streams its audit log to"
    address_lists ||--o{ network_rules : "named by"

Backups and network access collections are platform-wide. network_events can also carry an org_id for an org-scoped event.

The product’s own collections belong to opentremor-core. Another product mounted on the same platform would replace this diagram and leave the other two 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"
        object analysed_with "nullable -- source (server, client, or scan), connection_id, model: what produced analysis"
        datetime last_ingested_at "stamped on creation, refreshed on every add_to_changeset call -- the age signal the retention sweep redacts value on"
    }
    changeset_entries {
        string _id "org_id:changeset:revision_id:hash (PK)"
        string org_id
        string changeset
        string revision_id "the revision this unit belongs to -- one per ingest; the GitHub head SHA for a push"
        string hash "FK -> resources"
        datetime created_at
    }
    changesets {
        string _id "org_id:changeset (PK)"
        string org_id
        string changeset
        datetime created_at "when its first revision started -- GET /v1/changesets"
        string current_revision_id "what every read shows unless a revision is named"
        array revisions "revision_id, created_at, source, rule_types (pack -> variant), outcome -- oldest first"
        string repository "nullable -- provider/external_id, set once by the first review or ingest that names it"
        object pull_request "nullable -- number, title, url of the pull request it reviews"
        array review_team_ids "the teams that review it -- resolved by the review job when it runs"
    }
    findings {
        string _id "org_id:hash:rule_id (PK)"
        string org_id "FK -> organizations"
        string status "open, needs_review, acknowledged, suppressed, false_positive, or closed (set by a completed revision)"
        object closed_in "with closed: changeset, revision_id, at, status_before"
        int occurrence_count
        array assigned_team_ids "set when it enters needs_review, from its changesets' review teams"
        array repositories "provider/external_id, unioned on every sighting from its changesets' repositories"
        string reason_code "nullable -- the reason given with the last triage status"
        object ticket "nullable -- the Jira issue: key, url, synced_status, synced_changesets, jira_status, closed_by_opentremor"
    }
    custom_rules {
        string _id "org_id:rule_id (PK)"
        string org_id "FK -> organizations"
        string rule_id "slug of title, immutable after creation"
        string builtin_id "the pack file's own rule id (TFSEC-020) on a seeded row; how the sync finds it again"
        string category "FK -> rule_categories.category_id -- org-owned, not a fixed enum"
        string severity "CRITICAL, HIGH, MEDIUM, LOW, or INFO"
        array packs "one rule can target multiple packs"
        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 12 seeded defaults"
        bool is_builtin_seed
    }
    installed_packs {
        string _id "org_id:pack (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 changeset
        datetime expires_at "TTL-indexed (expireAfterSeconds=0); also checked explicitly on every read"
    }
    llm_credentials {
        string _id "org_id:connection_id (PK) -- one LLM connection per row"
        string provider_kind "anthropic, openai, mistral, or openai_compatible"
        string base_url "nullable -- checked against llm.egress"
        string encrypted_key "nullable, Fernet, never returned by the API"
        bool enabled
    }
    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 provider "github, gitlab, or jira"
        object config "llm_backend (provider kind), connection_id, model"
        string installation_id "GitHub -- null until the installation webhook; unique"
        object github_app "GitHub -- the org's own App: app_id, app_slug, html_url, encrypted_private_key, encrypted_webhook_secret"
        string base_url "GitLab -- checked against gitlab.egress"
        string encrypted_token "GitLab -- group access token; token_expires_at beside it"
        string webhook_id "GitLab -- the webhook URL's id; encrypted_webhook_secret beside it"
        string encrypted_tls_ca_pem "GitLab and Jira, nullable -- the org's own CA"
        string flavor "Jira -- cloud or data_center; base_url, the encrypted token, default_target, team_targets, product_targets, rules, transitions and sync sit beside it"
    }
    pr_gates {
        string _id "org_id:changeset (PK)"
        string org_id "FK -> organizations"
        string changeset "gh-{owner}-{repo}-{pr} or gl-{project_id}-{iid}"
        string provider "github or gitlab"
        string repo "owner/name, as shown"
        int pr_number
        string head_sha "the commit the check was posted to, and is re-posted to on triage"
        object locator "the host adapter's own keys, captured per run -- GitHub: installation_id; GitLab: project_id"
        int resource_count "quoted in the description, so a re-post says what the run said"
        array team_ids "the repository's review teams for this run"
    }
    repositories {
        string _id "org_id:provider:external_id (PK)"
        string org_id "FK -> organizations"
        string provider "github or gitlab"
        string external_id "the host's id -- GitHub repository id, GitLab project id; never the name"
        string full_name "refreshed whenever the repository is seen again"
        string status "active or removed -- removal keeps the row and its teams"
        string source "sync or webhook -- how it was first seen"
        array team_ids "owning teams (platform Team.team_id); dangling ids are filtered on read"
        string product_id "nullable -- FK -> products"
        array enabled_packs "nullable -- packs that run here on top of the org's and the product's"
        array suggested_packs "packs that claim files here and are off; a person applies or dismisses each"
        array dismissed_packs "never suggested here again"
        object rule_variants "pack name -> variant"
        array excluded_rules "rule_id, reason, excluded_by, excluded_at"
        object llm "nullable -- connection_id, model"
        object suggested_rule_variants "pack name -> variant the content suggests"
        datetime last_pull_request_at "nullable"
        bool review_drafts "nullable -- true reviews draft PRs; false or null holds a draft at pending until it is ready"
        bool code_suggestions "nullable -- overrides organizations.code_suggestions.enabled; null follows the org"
        object scan "nullable -- the last default-branch scan: changeset, job_id, status, plan, slices_done, slices_left, units, analysed_units, reused_units"
    }
    products {
        string _id "org_id:product_id (PK)"
        string org_id "FK -> organizations"
        string name
        string description "nullable"
        array team_ids "owning teams"
        array enabled_packs "packs that run on each of its repositories"
    }
    repository_ownership {
        string _id "org_id (PK)"
        array rules "pattern (glob on full_name), team_ids -- first match wins"
        string default_review_team_id "nullable -- applied at review time, never written onto a repository"
    }
    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{ changeset_entries : "referenced by"
    changesets ||--o{ changeset_entries : "revisions of"
    resources ||--o{ findings : "produces"
    organizations ||--o{ integrations : "connects (one per provider: github, gitlab, jira)"
    organizations ||--o{ report_links : "mints, time-limited"
    organizations ||--o{ github_manifest_states : "starts, single-use"
    organizations ||--o{ pr_gates : "one per pull request analysed"
    pr_gates }o--|| changeset_entries : "addressed by changeset"
    organizations ||--o{ repositories : "reaches through its integrations"
    organizations ||--o| repository_ownership : "routes unowned repositories"
    organizations ||--o{ products : "groups repositories into"
    products ||--o{ repositories : "contains"
    organizations ||--o{ report_templates : "composes, optionally sets one default per format"
    organizations ||--o{ rule_categories : "owns, fully editable/deletable"
    review_feedback {
        string _id "feedback_id (PK) -- append-only"
        string org_id "FK -> organizations"
        string resource_hash
        string rule_id
        string verdict "confirmed, rejected, severity_wrong, missed, or unclear"
        string source "dashboard_triage, pr_command, pr_review_comment, or api"
        bool trusted "decided at write time, never recomputed"
    }
    rule_proposals {
        string _id "{org_id}:{proposal_id} (PK)"
        string org_id "FK -> organizations"
        string rule_id
        string status "pending, deferred, applied, or dismissed"
    }
    knowledge_documents {
        string _id "org_id:provider:external_id:path (PK)"
        string org_id "FK -> organizations"
        string kind "adr or agent_instructions"
        string blob_sha "the content read -- a new one means the file changed"
        string commit_sha "the default-branch commit it was read at"
        string status "the ADR's own status, nullable"
        string outcome "extracted, not_active, flagged, too_large, skipped, or failed"
        int proposals "rule proposals this read produced"
        bool injection_scored "semantic injection scoring judged every pattern match in it"
        datetime read_at
    }
    builtin_rule_syncs {
        string _id "fingerprint of every pack's rule files (PK)"
        datetime claimed_at "the replica that inserted it syncs built-in rules into every org; the others skip"
    }
    organizations ||--o{ review_feedback : "records reviewer verdicts"
    organizations ||--o{ rule_proposals : "receives proposed rule changes"
    repositories ||--o{ knowledge_documents : "has ADRs read by the knowledge sync"
    knowledge_documents ||--o{ rule_proposals : "drafts"
    repositories ||--o{ changesets : "owns, through changesets.repository"

Why split resources from changeset_entries instead of one collection?

A unit of content, such as a helper module copied identically into a dozen services, can appear under several changesets at once. Store the content and its analysis in resources. Let changeset_entries hold thin pointers to it. This ensures the content is analysed only once, regardless of how many changesets reference it.

Why is a resource’s analysis org-scoped but not changeset-scoped?

A resource’s identity is its content hash. A different hash means different content and a new resource document, so it does not need re-analysis per changeset. Every changeset pointing to the same hash points to the same analysis. Deduplication does not cross organization boundaries: resources._id is "{org_id}:{hash}". Two organizations submitting identical input get separate resources and analyses. value.body can contain tenant-identifying details, such as customer names or internal hostnames. A cache hit across organizations could leak that information.

Why does findings exist separately from resources.analysis.findings?

resources.analysis.findings is raw LLM output from the most recent analysis of that content hash. It has no identity across re-analysis and cannot record a human decision. findings is the durable counterpart: one record per (org_id, resource_hash, rule_id). analysis_service.record_analysis() creates or refreshes it whenever it sees that triple. The record carries a status that a human can change via PATCH /v1/findings/{hash}/{rule_id}. That status survives re-analysis of the same resource instead of resetting.

Why does pr_gates exist rather than reading the jobs record?

jobs already carries changeset and an external_ref, but it cannot answer where to re-post the check. Jobs have no by-changeset lookup and are swept and retained on their own schedule. A gate must remain resolvable as long as the pull request is open. pr_gates uses (org_id, changeset) as its key, so re-analysis of the same PR overwrites the row instead of adding one per push. It is written immediately before the status is posted. Reversing that order could leave a posted pending status unreachable if the write failed.