Id RFC 002 data model

RFC-002 — Data Model & KDB Schema

*tatus:*Draft
*ate:*20260408
*uthor:*Koder Team
*epends on:*RFC-001

Summary

Defines the data model for all Koder ID microservices, stored in KDB. Each service has its own namespace with complete schema isolation. Multi-tenancy is enforced via tenant_id in every table.

Design Principles

*ervice-owned data*— each service is the sole owner of its namespace
*enant isolation*— tenant_id as partition key in every table, enforced by KDB RLS
*mmutable audit trail*— all mutations produce audit log entries
*oft deletes*— records are marked deleted_at, never physically removed (compliance)
*LIDs for IDs*— sortable, unique, no coordination needed between services

Common Fields

Every table includes:

id          TEXT PRIMARY KEY,    -- ULID
tenant_id   TEXT NOT NULL,       -- tenant partition key
created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
deleted_at  TIMESTAMPTZ          -- soft delete marker

Identity Service — Namespace: `koder_id_identity`

Table: `users`

Core user identity record.

CREATE TABLE users (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    username        TEXT NOT NULL,
    email           TEXT NOT NULL,
    email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
    display_name    TEXT,
    avatar_url      TEXT,
    locale          TEXT DEFAULT 'en-US',
    status          TEXT NOT NULL DEFAULT 'active',  -- active, disabled, locked
    metadata        JSONB,                           -- extensible key-value
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,

    UNIQUE (tenant_id, username),
    UNIQUE (tenant_id, email)
);

Table: `credentials`

Password and recovery codes. Separated from users to limit access.

CREATE TABLE credentials (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    user_id         TEXT NOT NULL,
    type            TEXT NOT NULL,          -- 'password', 'recovery_code'
    hash            TEXT NOT NULL,          -- Argon2id hash
    salt            TEXT,                   -- if needed beyond Argon2id embedded salt
    version         INTEGER NOT NULL DEFAULT 1,  -- for hash algorithm migration
    expires_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,

    UNIQUE (tenant_id, user_id, type)
);

Table: `mfa_devices`

TOTP and WebAuthn registrations.

CREATE TABLE mfa_devices (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    user_id         TEXT NOT NULL,
    type            TEXT NOT NULL,          -- 'totp', 'webauthn'
    name            TEXT NOT NULL,          -- user-friendly device name
    -- TOTP fields
    totp_secret     TEXT,                   -- encrypted TOTP secret
    totp_algorithm  TEXT DEFAULT 'SHA1',
    totp_digits     INTEGER DEFAULT 6,
    totp_period     INTEGER DEFAULT 30,
    -- WebAuthn fields
    webauthn_credential_id   BYTEA,
    webauthn_public_key      BYTEA,
    webauthn_aaguid          TEXT,
    webauthn_sign_count      BIGINT DEFAULT 0,
    webauthn_transports      TEXT[],        -- 'usb', 'nfc', 'ble', 'internal'
    -- Common
    verified        BOOLEAN NOT NULL DEFAULT FALSE,
    last_used_at    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

Auth Service — Namespace: `koder_id_auth`

Table: `auth_flows`

Tracks in~~progress authentication flows (multi~~step: password → MFA).

CREATE TABLE auth_flows (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    user_id         TEXT,                   -- set after password step succeeds
    status          TEXT NOT NULL DEFAULT 'pending',  -- pending, password_ok, mfa_required, completed, failed
    steps_completed TEXT[] DEFAULT '{}',    -- ['password', 'totp'] or ['password', 'webauthn']
    ip_address      INET,
    user_agent      TEXT,
    error_count     INTEGER DEFAULT 0,
    expires_at      TIMESTAMPTZ NOT NULL,   -- flow timeout (e.g. 10 minutes)
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Table: `auth_events`

Audit log of authentication attempts.

CREATE TABLE auth_events (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    user_id         TEXT,
    flow_id         TEXT,
    event_type      TEXT NOT NULL,          -- 'login_success', 'login_failed', 'mfa_success', 'mfa_failed', 'lockout'
    ip_address      INET,
    user_agent      TEXT,
    details         JSONB,                  -- additional context
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Table: `lockouts`

Brute-force protection.

CREATE TABLE lockouts (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    user_id         TEXT NOT NULL,
    failed_attempts INTEGER NOT NULL DEFAULT 0,
    locked_until    TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (tenant_id, user_id)
);

OAuth2/OIDC Service — Namespace: `koder_id_oauth`

Table: `clients`

OAuth2 client applications (registered by tenants).

CREATE TABLE clients (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    client_id       TEXT NOT NULL,          -- public client identifier
    client_secret   TEXT,                   -- hashed; NULL for public clients
    client_name     TEXT NOT NULL,
    client_type     TEXT NOT NULL,          -- 'confidential', 'public'
    redirect_uris   TEXT[] NOT NULL,
    grant_types     TEXT[] NOT NULL DEFAULT '{authorization_code}',
    response_types  TEXT[] NOT NULL DEFAULT '{code}',
    scopes          TEXT[] NOT NULL DEFAULT '{openid,profile,email}',
    token_endpoint_auth_method TEXT DEFAULT 'client_secret_basic',
    logo_uri        TEXT,
    tos_uri         TEXT,
    policy_uri      TEXT,
    metadata        JSONB,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,

    UNIQUE (tenant_id, client_id)
);

Table: `authorization_codes`

Short-lived authorization codes.

CREATE TABLE authorization_codes (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    code            TEXT NOT NULL,           -- opaque, high-entropy
    client_id       TEXT NOT NULL,
    user_id         TEXT NOT NULL,
    redirect_uri    TEXT NOT NULL,
    scopes          TEXT[] NOT NULL,
    code_challenge  TEXT,                    -- PKCE
    code_challenge_method TEXT,              -- 'S256'
    nonce           TEXT,                    -- OIDC nonce
    used            BOOLEAN DEFAULT FALSE,
    expires_at      TIMESTAMPTZ NOT NULL,    -- short: 30-60 seconds
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Table: `consents`

User consent grants per client.

CREATE TABLE consents (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    user_id         TEXT NOT NULL,
    client_id       TEXT NOT NULL,
    scopes          TEXT[] NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,

    UNIQUE (tenant_id, user_id, client_id)
);

Table: `signing_keys`

JWT signing key pairs (rotation support).

CREATE TABLE signing_keys (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    algorithm       TEXT NOT NULL DEFAULT 'RS256',
    public_key      TEXT NOT NULL,           -- PEM
    private_key     TEXT NOT NULL,           -- PEM, encrypted at rest
    kid             TEXT NOT NULL,            -- Key ID for JWKS
    status          TEXT NOT NULL DEFAULT 'active',  -- active, rotated, revoked
    rotated_at      TIMESTAMPTZ,
    expires_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (tenant_id, kid)
);

Session Service — Namespace: `koder_id_session`

Table: `sessions`

Stateful sessions (refresh token backing store).

CREATE TABLE sessions (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    user_id         TEXT NOT NULL,
    client_id       TEXT NOT NULL,
    refresh_token   TEXT NOT NULL,            -- opaque, high-entropy, hashed
    scopes          TEXT[] NOT NULL,
    ip_address      INET,
    user_agent      TEXT,
    last_active_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at      TIMESTAMPTZ NOT NULL,     -- refresh token expiry
    revoked         BOOLEAN DEFAULT FALSE,
    revoked_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Table: `access_tokens`

Access token metadata (for introspection/revocation, the JWT itself is stateless).

CREATE TABLE access_tokens (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    session_id      TEXT NOT NULL,
    user_id         TEXT NOT NULL,
    client_id       TEXT NOT NULL,
    jti             TEXT NOT NULL,            -- JWT ID, for revocation
    scopes          TEXT[] NOT NULL,
    expires_at      TIMESTAMPTZ NOT NULL,     -- short: 5-15 minutes
    revoked         BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (tenant_id, jti)
);

Admin Service — Namespace: `koder_id_admin`

Table: `tenants`

Tenant (organization) registry.

CREATE TABLE tenants (
    id              TEXT PRIMARY KEY,
    slug            TEXT NOT NULL UNIQUE,     -- URL-safe identifier
    name            TEXT NOT NULL,
    domain          TEXT,                     -- custom domain (e.g. 'acme.id.koder.dev')
    logo_url        TEXT,
    settings        JSONB NOT NULL DEFAULT '{}',  -- tenant-level config overrides
    status          TEXT NOT NULL DEFAULT 'active',  -- active, suspended
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

Table: `api_keys`

Machinetomachine API keys.

CREATE TABLE api_keys (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    name            TEXT NOT NULL,
    key_hash        TEXT NOT NULL,            -- hashed API key
    key_prefix      TEXT NOT NULL,            -- first 8 chars for identification
    scopes          TEXT[] NOT NULL,
    last_used_at    TIMESTAMPTZ,
    expires_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

Table: `audit_log`

Global audit trail for admin operations.

CREATE TABLE audit_log (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,
    actor_id        TEXT NOT NULL,            -- user or API key that performed the action
    actor_type      TEXT NOT NULL,            -- 'user', 'api_key', 'system'
    action          TEXT NOT NULL,            -- 'tenant.create', 'client.update', 'user.delete', etc.
    resource_type   TEXT NOT NULL,            -- 'tenant', 'client', 'user', 'session', etc.
    resource_id     TEXT NOT NULL,
    details         JSONB,                   -- before/after diff or additional context
    ip_address      INET,
    user_agent      TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Indexes

Key indexes for performance (per table):

-- users
CREATE INDEX idx_users_tenant_email ON users (tenant_id, email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_tenant_status ON users (tenant_id, status) WHERE deleted_at IS NULL;

-- credentials
CREATE INDEX idx_credentials_user ON credentials (tenant_id, user_id) WHERE deleted_at IS NULL;

-- mfa_devices
CREATE INDEX idx_mfa_user ON mfa_devices (tenant_id, user_id) WHERE deleted_at IS NULL;

-- auth_flows
CREATE INDEX idx_auth_flows_expires ON auth_flows (expires_at) WHERE status != 'completed';

-- auth_events
CREATE INDEX idx_auth_events_user ON auth_events (tenant_id, user_id, created_at DESC);
CREATE INDEX idx_auth_events_type ON auth_events (tenant_id, event_type, created_at DESC);

-- clients
CREATE INDEX idx_clients_tenant ON clients (tenant_id) WHERE deleted_at IS NULL;

-- authorization_codes
CREATE INDEX idx_auth_codes_code ON authorization_codes (tenant_id, code) WHERE used = FALSE;
CREATE INDEX idx_auth_codes_expires ON authorization_codes (expires_at) WHERE used = FALSE;

-- sessions
CREATE INDEX idx_sessions_user ON sessions (tenant_id, user_id) WHERE revoked = FALSE;
CREATE INDEX idx_sessions_refresh ON sessions (tenant_id, refresh_token) WHERE revoked = FALSE;
CREATE INDEX idx_sessions_expires ON sessions (expires_at) WHERE revoked = FALSE;

-- access_tokens
CREATE INDEX idx_access_tokens_jti ON access_tokens (tenant_id, jti) WHERE revoked = FALSE;

-- audit_log
CREATE INDEX idx_audit_tenant_time ON audit_log (tenant_id, created_at DESC);
CREATE INDEX idx_audit_actor ON audit_log (tenant_id, actor_id, created_at DESC);
CREATE INDEX idx_audit_resource ON audit_log (tenant_id, resource_type, resource_id);

Data Lifecycle

Data	Retention	Cleanup
Auth flows	1 hour after expiry	Background job
Authorization codes	5 minutes after expiry	Background job
Access token metadata	1 hour after expiry	Background job
Sessions	30 days after last activity	Background job
Audit log	1 year (configurable per tenant)	Background job
Soft-deleted records	90 days	Background job

KDB-Specific Considerations

*chema creation:*Each service creates its namespace and tables on first boot (auto-migration)
*LS enforcement:*KDB row-level security policies filter by tenant_id — services set the tenant context per connection/query
*SONB queries:*KDB's document model capabilities used for metadata and settings fields
*ncryption at rest:*KDB's built-in encryption covers all namespaces

Open Questions

Should signing_keys.private_key use KDB encryption at rest, or application-level envelope encryption?
What is the KDB Go client API for setting RLS context (tenant_id) per query?
Should we use KDB's time~~series model for auth_events and audit_log for better query performance on time~~range scans?

Amendment — 20260411 — Account Models, Invite Chain, Handle Allocation

This amendment extends the data model to support the decisions in *FC~~012 (Account Models)* *FC~~013 (Invite Chain & Trust Score)* and the meta/docs/stack/policies/username-allocation.kmd policy. All additions are backward-compatible with the v1 schema above; only one column is modified on an existing table.

Terminology reconciliation

RFC~~002 v1 used tenants as the top~~level organization concept. RFC~~012 introduces a three~~level hierarchy — *ndividual / Workspace / Tenant*— where "Tenant" is a sub-isolation scope inside a Workspace, not the KDB partition key.

To resolve the collision without breaking existing code:

The *DB partition key column*throughout the schema remains tenant_id. Its semantic is unchanged: it is the partition key for multi-tenant data isolation.
The *FC-002 v1 tenants table*in the koder_id_admin namespace is *enamed to workspaces*in a future migration. Until the migration, both names refer to the same concept.
The *FC~~012 "Tenant"*concept is a *ew*entity, represented by the new subtenants table below. It is a sub~~partition within a workspace.
Throughout the schema, the tenant_id column always holds the *orkspace ID*(not the subtenant ID). Subtenant scoping is enforced at the application layer via explicit subtenant_id columns on tables that need it.

Modified table: `users`

One column renamed, several columns added. The column username is renamed to handle (more explicit about its role as a global identifier) and gains the uniqueness properties required by RFC-012.

ALTER TABLE users RENAME COLUMN username TO handle;

ALTER TABLE users
    ADD COLUMN handle_changed_at   TIMESTAMPTZ,
    ADD COLUMN trust_score         INTEGER NOT NULL DEFAULT 0 CHECK (trust_score BETWEEN 0 AND 10000),
    ADD COLUMN kind                TEXT NOT NULL DEFAULT 'individual' CHECK (kind IN ('individual', 'service')),
    ADD COLUMN region              TEXT NOT NULL DEFAULT 'default',
    ADD COLUMN deceased_at         TIMESTAMPTZ;

-- Enforce that all individual users live under @koder.dev exactly
ALTER TABLE users
    ADD CONSTRAINT users_individual_email_domain
    CHECK (kind <> 'individual' OR email = handle || '@koder.dev');

-- Global handle uniqueness (enforced at the application layer via handle_registry)
-- The local uniqueness constraint from v1 is preserved but complemented:
--   UNIQUE (tenant_id, handle)  -- v1 constraint, stays
CREATE UNIQUE INDEX users_handle_global_unique ON users (lower(handle)) WHERE kind = 'individual' AND deleted_at IS NULL;

New namespace: `koder_id_identity` (extended)

Table: `handle_registry`

Global append~~only source of truth for handle uniqueness. Writes are routed to a single elected leader per letter~~prefix shard to achieve O(1) throughput without a global lock.

CREATE TABLE handle_registry (
    handle          TEXT PRIMARY KEY,           -- lowercase ASCII, no @koder.dev suffix
    handle_prefix   CHAR(1) NOT NULL,           -- first char (letter or digit) — used for writer sharding
    user_id         TEXT NOT NULL,              -- FK to users.id (the owner, or NULL if reserved-only)
    allocation_kind TEXT NOT NULL,              -- 'individual', 'service', 'reserved', 'frozen'
    allocated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    released_at     TIMESTAMPTZ,                -- set if the handle ever re-entered the pool (rare)
    previous_user_id TEXT                       -- for frozen handles that used to belong to someone
);

CREATE INDEX handle_registry_prefix ON handle_registry (handle_prefix);
CREATE INDEX handle_registry_user ON handle_registry (user_id) WHERE allocation_kind IN ('individual', 'service');

*nvariants:*

Inserts are CAS-guarded: two concurrent inserts of the same handle always lose one.
*o updates*to the handle, handle_prefix, or allocation_kind columns. The only update is setting released_at on the rare release path (dormancy expiry).
allocation_kind = 'frozen' marks handles that were previously allocated and are now held in reservation (after a user change or deletion where the handle cannot be reused).

Table: `reservation_dictionary`

The append~~only dictionary of reserved handles. Append~~only per policy — entries are never removed or retroactively applied.

CREATE TABLE reservation_dictionary (
    id              TEXT PRIMARY KEY,
    handle          TEXT NOT NULL,
    category        TEXT NOT NULL,              -- 'system', 'product', 'brand', 'profanity', 'ambiguous'
    reason          TEXT,
    added_by        TEXT NOT NULL,              -- staff user_id
    reviewed_by     TEXT NOT NULL,              -- second staff user_id (four-eyes principle)
    version         INTEGER NOT NULL,           -- dictionary version at addition
    added_at        TIMESTAMPTZ NOT NULL DEFAULT now(),

    UNIQUE (handle)
);

CREATE INDEX reservation_category ON reservation_dictionary (category);

Table: `workspaces`

RFC-012 workspaces: organizations that own one or more verified domains. Replaces the v1 tenants table in a future migration.

CREATE TABLE workspaces (
    id              TEXT PRIMARY KEY,           -- ULID; also used as `tenant_id` throughout the schema
    slug            TEXT NOT NULL UNIQUE,       -- URL-safe identifier
    name            TEXT NOT NULL,
    kind            TEXT NOT NULL DEFAULT 'organization',  -- 'organization', 'system'
    plan            TEXT NOT NULL DEFAULT 'free',
    status          TEXT NOT NULL DEFAULT 'active',        -- active, suspended, deleted
    region          TEXT NOT NULL DEFAULT 'default',
    settings        JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ
);

-- A well-known system workspace always exists for individual Koder IDs
-- INSERT INTO workspaces (id, slug, name, kind) VALUES ('ws_system_koder', 'koder', 'Koder', 'system');
-- The `Koder` workspace (the one that employs staff) is also an ordinary row.

Table: `workspace_domains`

Domains verified by the workspace via DNS TXT (or equivalent) challenge.

CREATE TABLE workspace_domains (
    workspace_id        TEXT NOT NULL REFERENCES workspaces(id),
    domain              TEXT NOT NULL UNIQUE,   -- lowercased
    verification_method TEXT NOT NULL,          -- 'dns_txt', 'http_file', 'email'
    verified_at         TIMESTAMPTZ,
    verification_token  TEXT NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY (workspace_id, domain)
);

-- koder.dev is always reserved for the Koder workspace; no other workspace may claim it.
-- Subdomains of koder.dev (e.g. id.koder.dev, flow.koder.dev) are not verifiable workspace domains.

Table: `workspace_members`

Membership of a user in a workspace with a role. Roles include staff, contractor, alumni, intern, board, external, and guest.

CREATE TABLE workspace_members (
    workspace_id    TEXT NOT NULL REFERENCES workspaces(id),
    user_id         TEXT NOT NULL REFERENCES users(id),
    role            TEXT NOT NULL,              -- staff, contractor, alumni, intern, board, external, guest
    joined_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    left_at         TIMESTAMPTZ,                -- set when role becomes alumni or guest expires
    role_changed_at TIMESTAMPTZ,                -- last role change
    guest_expires_at TIMESTAMPTZ,               -- only for role=guest; enforced by nightly job
    metadata        JSONB NOT NULL DEFAULT '{}',

    PRIMARY KEY (workspace_id, user_id)
);

CREATE INDEX workspace_members_role ON workspace_members (workspace_id, role);
CREATE INDEX workspace_members_guest_expiry ON workspace_members (guest_expires_at) WHERE role = 'guest';

Table: `subtenants`

The RFC~~012 "Tenant" concept: a resource~~isolated space within a workspace (e.g., crescer.com.br/producao, crescer.com.br/staging). This is not the KDB partition key — it is a secondary scoping level enforced by the application.

CREATE TABLE subtenants (
    id              TEXT PRIMARY KEY,
    workspace_id    TEXT NOT NULL REFERENCES workspaces(id),
    slug            TEXT NOT NULL,
    name            TEXT NOT NULL,
    environment     TEXT,                       -- 'production', 'staging', 'dev', 'test', NULL for single-env
    region          TEXT NOT NULL DEFAULT 'default',
    status          TEXT NOT NULL DEFAULT 'active',
    settings        JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,

    UNIQUE (workspace_id, slug)
);

-- Every workspace has at least one default subtenant auto-created at workspace creation.

Table: `subtenant_role_bindings`

Role assignments scoped to a subtenant. A user can have different roles in different subtenants of the same workspace.

CREATE TABLE subtenant_role_bindings (
    subtenant_id    TEXT NOT NULL REFERENCES subtenants(id),
    user_id         TEXT NOT NULL REFERENCES users(id),
    role            TEXT NOT NULL,              -- owner, admin, editor, viewer, or custom
    granted_by      TEXT NOT NULL REFERENCES users(id),
    granted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    revoked_at      TIMESTAMPTZ,

    PRIMARY KEY (subtenant_id, user_id, role)
);

CREATE INDEX subtenant_role_bindings_user ON subtenant_role_bindings (user_id) WHERE revoked_at IS NULL;

Table: `invites`

Outstanding and recently~~processed invites. See RFC~~013 for flow details.

CREATE TABLE invites (
    id              TEXT PRIMARY KEY,
    tenant_id       TEXT NOT NULL,              -- always the Koder system workspace ID
    inviter_id      TEXT NOT NULL REFERENCES users(id),
    token_hash      TEXT NOT NULL UNIQUE,       -- Argon2id hash of the raw token
    intended_email  TEXT,                       -- hint only, not enforced
    reason_code     TEXT,                       -- 'colleague', 'friend', 'project', 'community', 'other'
    reason_detail   TEXT,                       -- free text, <=500 chars
    public_verifiable BOOLEAN NOT NULL DEFAULT FALSE,  -- opt-in public invite proof
    status          TEXT NOT NULL DEFAULT 'open',-- open, redeemed, revoked, expired
    issued_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at      TIMESTAMPTZ NOT NULL,
    redeemed_at     TIMESTAMPTZ,
    revoked_at      TIMESTAMPTZ
);

CREATE INDEX invites_inviter_status ON invites (inviter_id, status);
CREATE INDEX invites_expires_open ON invites (expires_at) WHERE status = 'open';

Table: `invite_chain_edges`

The persistent, immutable lineage edge. One row per user admitted via invite.

CREATE TABLE invite_chain_edges (
    invitee_id      TEXT PRIMARY KEY REFERENCES users(id),
    inviter_id      TEXT NOT NULL REFERENCES users(id),
    invite_id       TEXT NOT NULL REFERENCES invites(id),
    depth           INTEGER NOT NULL,           -- distance from nearest staff root
    issued_at       TIMESTAMPTZ NOT NULL,
    redeemed_at     TIMESTAMPTZ NOT NULL
);

CREATE INDEX invite_chain_edges_inviter ON invite_chain_edges (inviter_id);
CREATE INDEX invite_chain_edges_depth ON invite_chain_edges (depth);

Table: `invite_revocations`

Revocation events. Append-only; does not mutate invite_chain_edges.

CREATE TABLE invite_revocations (
    id              TEXT PRIMARY KEY,
    invitee_id      TEXT NOT NULL REFERENCES users(id),
    reason          TEXT NOT NULL,              -- 'abuse', 'fraud', 'policy', 'inviter_compromised', etc
    reason_detail   TEXT,
    revoked_by      TEXT NOT NULL REFERENCES users(id),
    revoked_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    cascade         BOOLEAN NOT NULL DEFAULT FALSE,
    cascade_job_id  TEXT                        -- FK to cascade_jobs if cascade=true
);

CREATE INDEX invite_revocations_invitee ON invite_revocations (invitee_id);

Table: `invite_quotas`

Per~~user quota state. Updated on invite issuance, redemption, and trust~~score recomputation.

CREATE TABLE invite_quotas (
    user_id         TEXT PRIMARY KEY REFERENCES users(id),
    total_allowed   INTEGER NOT NULL,           -- lifetime cap
    total_issued    INTEGER NOT NULL DEFAULT 0,
    total_redeemed  INTEGER NOT NULL DEFAULT 0,
    period_allowed  INTEGER NOT NULL,           -- rolling 30-day cap (denormalized from trust_score)
    period_issued   INTEGER NOT NULL DEFAULT 0,
    period_reset_at TIMESTAMPTZ NOT NULL,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

Table: `cascade_jobs`

Background job tracker for cascade revocations. Allows the admin panel to show progress and supports the 14~~day un~~cascade window.

CREATE TABLE cascade_jobs (
    id              TEXT PRIMARY KEY,
    root_invitee_id TEXT NOT NULL REFERENCES users(id),
    initiated_by    TEXT NOT NULL REFERENCES users(id),
    initiated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at    TIMESTAMPTZ,
    status          TEXT NOT NULL DEFAULT 'pending',  -- pending, running, completed, reversed, failed
    affected_count  INTEGER NOT NULL DEFAULT 0,
    suspended_count INTEGER NOT NULL DEFAULT 0,
    reversible_until TIMESTAMPTZ NOT NULL,      -- initiated_at + 14 days
    notes           TEXT
);

Table: `badges`

Cryptographically signed attestations about a user (e.g., staff, verified, developer, alumni). Keys live in badge_issuer_keys.

CREATE TABLE badges (
    id              TEXT PRIMARY KEY,
    user_id         TEXT NOT NULL REFERENCES users(id),
    kind            TEXT NOT NULL,              -- 'staff', 'verified', 'developer', 'alumni', 'invited-by-staff', etc
    issuer_key_id   TEXT NOT NULL REFERENCES badge_issuer_keys(kid),
    payload         JSONB NOT NULL,             -- the signed claims (handle, role, scope, expiry, etc)
    signature       BYTEA NOT NULL,             -- detached signature over payload
    issued_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at      TIMESTAMPTZ NOT NULL,
    revoked_at      TIMESTAMPTZ,
    revocation_reason TEXT,

    UNIQUE (user_id, kind, issued_at)
);

CREATE INDEX badges_user_kind ON badges (user_id, kind) WHERE revoked_at IS NULL AND expires_at > now();

Table: `badge_issuer_keys`

Key material for badge signing. Private keys are encrypted at rest and mirror the signing_keys pattern.

CREATE TABLE badge_issuer_keys (
    kid             TEXT PRIMARY KEY,           -- key id, in badges and in verification
    kind            TEXT NOT NULL,              -- which badge kind this key signs: 'staff', 'verified', etc
    algorithm       TEXT NOT NULL DEFAULT 'Ed25519',
    public_key      BYTEA NOT NULL,
    private_key     BYTEA NOT NULL,             -- encrypted at rest
    status          TEXT NOT NULL DEFAULT 'active',  -- active, rotating, revoked
    rotated_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at      TIMESTAMPTZ
);

Table: `service_identities`

Machine identities (bots, service accounts). Separate namespace from human users per the policy. Handles follow the <name>.bot@koder.dev pattern.

CREATE TABLE service_identities (
    id              TEXT PRIMARY KEY,
    workspace_id    TEXT NOT NULL REFERENCES workspaces(id),  -- always owned by a workspace
    handle          TEXT NOT NULL,              -- e.g. 'crescer-sync' → email 'crescer-sync.bot@koder.dev'
    display_name    TEXT NOT NULL,
    description     TEXT,
    trust_class     TEXT NOT NULL DEFAULT 'third_party',  -- system, verified_org, third_party, untrusted
    service_attestation BYTEA,                  -- cryptographic attestation, optional
    created_by      TEXT NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at      TIMESTAMPTZ,

    UNIQUE (handle)
);

-- The .bot suffix is enforced at the application layer (handle gets '.bot@koder.dev' appended on email use)
CREATE INDEX service_identities_workspace ON service_identities (workspace_id) WHERE deleted_at IS NULL;

Indexes (amendment)

Additional indexes to support the new tables and hyperscale queries:

-- handle_registry hot paths
CREATE INDEX handle_registry_kind_prefix ON handle_registry (allocation_kind, handle_prefix);

-- users hyperscale partition lookups
CREATE INDEX users_region_kind ON users (region, kind) WHERE deleted_at IS NULL;
CREATE INDEX users_trust_score ON users (trust_score) WHERE kind = 'individual' AND deleted_at IS NULL;

-- invite_chain_edges for subtree walks (bounded by depth)
CREATE INDEX invite_chain_edges_depth_inviter ON invite_chain_edges (inviter_id, depth);

-- badges for hot badge verification
CREATE INDEX badges_active_issuer ON badges (issuer_key_id) WHERE revoked_at IS NULL AND expires_at > now();

Sharding strategy (hyperscale)

At 10⁸ identities, single-node KDB is not enough. The following partitioning applies:

Table	Partition key	Rationale
`users`	`hash(lower(handle)) mod N_regions`	Even distribution; handle-based queries are the hot path
`handle_registry`	`handle_prefix` (26 letters + 10 digits + fallback)	Single-writer leader election per prefix for strong uniqueness
`workspaces`	`hash(id)`	Uniform, no hot spot
`workspace_members`	`workspace_id`	Co~~locate members with their workspace for single~~workspace queries
`invite_chain_edges`	`hash(invitee_id)`	Reads: subtree walks are bounded; writes: one per redemption
`invites`	`inviter_id`	Most reads are "my outstanding invites"; writes are per-user
`badges`	`user_id`	Verification reads are always per-user
`service_identities`	`workspace_id`	Co-locate with owning workspace

KDB must support *refix-consistent hashing*for users and invite_chain_edges so that rebalancing across regions does not require global rewrites.

Data lifecycle (amendment)

Data	Retention	Cleanup
Open invites	`expires_at` + 1 year	Nightly sweep marks `expired`; soft-delete after 1y
Revoked invites	1 year	Soft-delete after
Cascade jobs	14 days reversible + 90 days read-only	Hard-delete after
Badges (expired)	90 days	Hard-delete after (revocations keep a tombstone)
Handle registry	Forever for `individual` and `frozen`; 90 days after `released`	Only `released_at` non~~null rows are ever deletable, and only for allocation_kind = 'individual' after the 182460~~month dormancy
Reservation dictionary	Forever	Append-only
Service identities	`deleted_at` + 30 days	Then hard-delete; handle is frozen in reservation dictionary
Invite chain edges	Forever (immutable lineage)	Never deleted; soft-delete of the invitee user preserves the edge

Migration path from v1 `tenants` to `workspaces`

The v1 tenants table (in koder_id_admin) is to be migrated to workspaces (in koder_id_identity) via a future migration script. Steps:

Add the workspaces table alongside tenants (this amendment)
Backfill: every row in v1 tenants gets a mirror row in workspaces with the same id
Dual-write for 30 days: writes to either table go to both
Switch reads to workspaces
Drop the v1 tenants table

The tenant_id column throughout all other tables keeps its name; only its resolution changes (points to workspaces(id) instead of tenants(id), same value).

Hyperscale invariants (binding)

*o cross~~region SELECT on the hot path*— authentication, login, handle lookup, and badge verification must be answerable from a single region's data + the global handle_registry (which is read~~mostly and cacheable)
*rust score is denormalized onto invite_quotas.period_allowed*— the hot path never recomputes the score
*ightly batch recomputation is sharded*— 256 shards for users, 36 shards for handle_registry, processed in parallel
*ascade revocation is async*— the initiating API call returns immediately with a cascade_job_id; completion is visible in the admin panel
*lobal uniqueness reads are cached*— the handle_registry is append~~heavy, read~~heavy; reads go through a read-through cache with short TTL (30 seconds) plus a pub/sub invalidation on writes

RFC-002 — Data Model & KDB Schema

Summary

Design Principles

Common Fields

Identity Service — Namespace: koder_id_identity

Table: users

Table: credentials

Table: mfa_devices

Auth Service — Namespace: koder_id_auth

Table: auth_flows

Table: auth_events

Table: lockouts

OAuth2/OIDC Service — Namespace: koder_id_oauth

Table: clients

Table: authorization_codes

Table: consents

Table: signing_keys

Session Service — Namespace: koder_id_session

Table: sessions

Table: access_tokens

Admin Service — Namespace: koder_id_admin

Table: tenants

Table: api_keys

Table: audit_log