Database schema

This is the public contract for the database schema owned by the SDK. Every column listed here is stable from the version it first appears in. Removing or renaming any column requires a major version bump.

The SDK owns 13 tables. They live in a configurable Postgres schema (default: identsphere). Host applications may join their own tables to users.id and organizations.id via foreign keys and may read directly from any table here.

Table index

Table	Purpose
organizations	Tenant containers
users	Individual accounts
organization_memberships	(user, org) → role
member_invitations	Pending org invites
user_sessions	Refresh-token-based sessions
user_passkeys	WebAuthn credentials
passkey_challenges	In-flight WebAuthn ceremonies
user_recovery_codes	MFA backup codes
email_verification_tokens	Signup / email-change verifications
email_otp_challenges	One-time login codes
trusted_browsers	Remember-this-device
platform_admins	Optional control-plane admin tier
audit_logs	Append-only authorization audit
api_keys	Programmatic credentials (auth-only fields)

organizations

Top-level tenant container. Every user belongs to exactly one organization (v1 limitation; many-to-many lands in v2).

Column	Type	Notes
id	UUID	Primary key.
name	TEXT	Human-readable.
slug	VARCHAR(100)	Globally unique URL-safe identifier.
settings	JSONB	Free-form. SDK never reads this.
authz_version	BIGINT	Bumped on any change that affects authorization caches.
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ
deleted_at	TIMESTAMPTZ	Soft-delete; SDK never hard-deletes.

users

Individual accounts. The primary identity record.

Column	Type	Notes
id	UUID	Primary key. FK-able from host tables.
email	TEXT	Globally unique; case-insensitive lookups recommended.
password_hash	TEXT	Argon2id (preferred) or bcrypt (legacy).
organization_id	UUID	FK to organizations(id).
role	VARCHAR(20)	Default role; authoritative role in organization_memberships.
status	VARCHAR(20)	active, invited, disabled, deleted.
display_name	TEXT
job_title	TEXT
phone_number	TEXT
avatar_url	TEXT
bio	TEXT
preferences	JSONB	Free-form per-user settings.
mfa_enabled	BOOLEAN	TOTP enrollment.
mfa_secret	TEXT	Base32-encoded TOTP secret.
passkey_enabled	BOOLEAN	At least one passkey enrolled.
last_password_change	TIMESTAMPTZ	Used for forced-rotation policies.
email_verified	BOOLEAN
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

organization-memberships

Authoritative (user, organization) role mapping. Read this, not users.role, when checking permissions.

Column	Type	Notes
id	UUID
organization_id	UUID
user_id	UUID
role	VARCHAR(40)	Default: owner, admin, billing, member, viewer.
invited_by	UUID | null
joined_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ
updated_at	TIMESTAMPTZ

Unique on (organization_id, user_id).

member-invitations

Pending invites. Tokens stored as SHA-256 hashes.

Column	Type	Notes
id	UUID
organization_id	UUID
email	VARCHAR(255)	Invited address.
role	VARCHAR(40)	The role to grant on acceptance.
token_hash	VARCHAR(64)	SHA-256 hex.
invited_by	UUID
status	VARCHAR(20)	pending, accepted, revoked, expired.
expires_at	TIMESTAMPTZ
accepted_at	TIMESTAMPTZ
created_at	TIMESTAMPTZ

user-sessions

Refresh-token-rotated sessions.

Column	Type	Notes
id	UUID
user_id	UUID
session_token	TEXT	Opaque public identifier.
session_family_id	UUID	Family for theft detection.
refresh_token_hash	TEXT	SHA-256 of the current refresh token.
csrf_token_hash	TEXT
dpop_thumbprint	TEXT	Reserved for future DPoP binding.
revoked / revoked_at	BOOLEAN / TIMESTAMPTZ
user_agent / ip_address	TEXT	For session-list display.
expires_at / last_active_at / rotated_at / created_at	TIMESTAMPTZ

user-passkeys

WebAuthn credentials.

Column	Type	Notes
id	UUID
user_id	UUID
credential_id	TEXT	Base64url; globally unique.
public_key	TEXT	Base64-encoded serialized Passkey struct.
sign_count	BIGINT	Monotonic; clone-detection signal.
label	VARCHAR(100)	User-chosen device name.
aaguid	VARCHAR(64)
transport	VARCHAR(20)	usb, nfc, ble, internal, hybrid.
backup_eligible / backup_state	BOOLEAN	Per W3C spec.
created_at / last_used_at	TIMESTAMPTZ

passkey-challenges

Ephemeral state for in-flight WebAuthn ceremonies. 5-minute TTL. Internal — host apps should not read this directly.

user-recovery-codes

MFA backup codes. Hashes only; raw codes shown once at enrollment.

email-verification-tokens

Tokens issued on signup and email-change flows. SHA-256 hashed.

email-otp-challenges

One-time codes for passwordless login + password reset. purpose column discriminates: login, password_reset.

trusted-browsers

"Remember this device." Skips MFA on a matching fingerprint within TTL.

platform-admins

Optional. Only relevant for hosts operating multi-tenant platforms that need a control-plane admin tier.

Column	Type	Notes
id	UUID
user_id	UUID
platform_role	VARCHAR(20)	account_owner, admin, viewer.
is_bootstrap	BOOLEAN	True for the seed admin.
granted_by / granted_at / revoked_at	UUID, TIMESTAMPTZ

A unique partial index enforces "exactly one active account_owner".

audit-logs

Append-only authorization event log. No foreign keys on organization_id or actor_id so records survive deletion of referenced rows (intentional — audit trails must outlive their subjects for compliance reviews).

api-keys

Programmatic credentials. Auth-only. No billing, no quota, no rate limits — those belong in host-owned tables foreign-keyed to api_keys.id.

Column	Type	Notes
id	UUID
key_hash	TEXT	SHA-256 hex. Lookup column.
key_prefix	TEXT	First 16 chars; safe to log.
user_id	UUID	Owner.
organization_id / team_id / project_id	UUID	Scope.
name	TEXT	User label.
scopes	JSONB array	Permission strings.
allowed_ips / allowed_referrers	JSONB array	Network constraints.
environment	VARCHAR(40)	Free-form tag.
created_at / expires_at / last_used_at / revoked_at	TIMESTAMPTZ
is_active	BOOLEAN

Foreign-key integration from host apps

Any host table can reference SDK tables via standard Postgres FKs:

CREATE TABLE my_app.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES IdentSphere.users(id),
    org_id      UUID NOT NULL REFERENCES IdentSphere.organizations(id) ON DELETE CASCADE
);

This is the integration seam. Treat the IdentSphere schema as a stable library, the same way you'd treat the public schema of a Postgres extension.

Stability policy

• Stable: every column documented above. Removing or renaming a column requires a major version bump.
• Internal: columns starting with underscores (none currently) and the _identsphere_migrations tracking table.
• Additive changes are free: new columns appear in minor versions.