CODITECT Database Schema

Version: 2.0.0 Generated: January 5, 2026 Database: PostgreSQL 16 (Cloud SQL) ORM: Django 5.2.9 with django-multitenant Total Models: 82 across 15 apps

---

Table of Contents

1. Overview
2. Entity Relationship Summary
3. Core Domain
   • tenants (4 models)
   • users (2 models)
   • organizations (4 models)
   • licenses (3 models)
4. Business Domain
   • subscriptions (3 models)
   • billing (8 models)
   • workstations (7 models)
   • repositories (6 models)
5. Operational Domain
   • container_sessions (2 models)
   • context (5 models)
   • issues (5 models)
   • workflows (17 models)
6. Reference Data Domain
   • reference_data (6 models)
7. Multi-Tenant Architecture
8. Migration Status
9. Index Reference

---

Schema Reference

Data Structure

field_name:
  type: string
  required: true
  description: Field description
  example: "example_value"

API Reference

Endpoint Overview

Method	Endpoint	Description
GET	/api/v1/resource	List resources
POST	/api/v1/resource	Create resource
PUT	/api/v1/resource/:id	Update resource
DELETE	/api/v1/resource/:id	Delete resource

Overview

App	Models	Status	ADR Reference
tenants	4	Operational	Core Multi-tenant
users	2	Operational	-
organizations	4	Operational	ADR-047
licenses	3	Operational	ADR-017
subscriptions	3	Operational	ADR-014
billing	8	Operational	ADR-014
workstations	7	Operational	ADR-005
repositories	6	Operational	ADR-006
container_sessions	2	Operational	ADR-055
context	5	Operational	ADR-052, ADR-053
issues	5	Operational	-
workflows	17	Operational	WF-107 to WF-119
reference_data	6	Operational	ISO 3166, 4217
permissions	1	Operational	RBAC
commerce	4	Operational	ADR-014
Total	82	100%	-

---

Entity Relationship Summary

┌─────────────────────────────────────────────────────────────────────────────┐
│                         CODITECT DATA MODEL (82 Models)                      │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│  CORE IDENTITY                    BUSINESS LAYER                             │
│  ┌──────────┐                    ┌──────────────┐                           │
│  │  Tenant  │◄───────────────────│ Subscription │                           │
│  └────┬─────┘                    └──────────────┘                           │
│       │                                 │                                    │
│       ▼                                 ▼                                    │
│  ┌──────────┐    ┌──────────┐    ┌──────────────┐    ┌──────────────┐       │
│  │   User   │◄──►│   Team   │    │   Invoice    │    │   Payment    │       │
│  └────┬─────┘    └────┬─────┘    └──────────────┘    └──────────────┘       │
│       │               │                                                      │
│       ▼               ▼                                                      │
│  ┌──────────┐    ┌──────────┐    ┌──────────────┐    ┌──────────────┐       │
│  │ License  │    │ Project  │    │ Workstation  │    │  Repository  │       │
│  └────┬─────┘    └──────────┘    └──────┬───────┘    └──────────────┘       │
│       │                                 │                                    │
│       ▼                                 ▼                                    │
│  ┌──────────┐                    ┌──────────────┐                           │
│  │ Session  │◄───────────────────│ContainerSess │                           │
│  └──────────┘                    └──────────────┘                           │
│                                                                              │
│  OPERATIONAL LAYER               REFERENCE DATA                              │
│  ┌──────────┐    ┌──────────┐    ┌──────────────┐    ┌──────────────┐       │
│  │ Context  │    │  Issue   │    │   Country    │    │   Currency   │       │
│  │ Message  │    │  Track   │    │    State     │    │   Timezone   │       │
│  └──────────┘    └──────────┘    └──────────────┘    └──────────────┘       │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

---

Core Domain

tenants

Multi-tenant isolation layer. All tenant-scoped models reference Tenant.

tenants.Tenant

Root entity for multi-tenant architecture.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Tenant identifier
name	VARCHAR(255)	NOT NULL	Organization name
slug	VARCHAR(100)	UNIQUE, NOT NULL	URL-safe identifier
email	VARCHAR(254)	UNIQUE, NOT NULL	Primary contact email
is_active	BOOLEAN	DEFAULT TRUE	Account status
trial_ends_at	TIMESTAMP	NULL	Trial expiration
settings	JSONB	DEFAULT {}	Tenant-specific settings
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: tenants Indexes:

• tenants_slug_idx on (slug)
• tenants_email_idx on (email)
• tenants_is_active_idx on (is_active)

---

tenants.Team

Team groupings within a tenant.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Team ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
name	VARCHAR(255)	NOT NULL	Team name
description	TEXT	DEFAULT ''	Team description
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: teams Constraints:

• unique_team_name_per_tenant UNIQUE (tenant_id, name)

---

tenants.TeamMembership

User membership in teams.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Membership ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
team_id	UUID	TenantFK → teams.id, CASCADE	Team
user_id	UUID	TenantFK → users.id, CASCADE	User
role	VARCHAR(20)	DEFAULT 'member'	Role in team
joined_at	TIMESTAMP	DEFAULT NOW()	Join timestamp

Table: team_memberships Constraints:

• unique_team_user_membership UNIQUE (team_id, user_id)

---

tenants.Project

Projects within a tenant with soft delete.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Project ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
name	VARCHAR(255)	NOT NULL	Project name
description	TEXT	DEFAULT ''	Description
metadata	JSONB	DEFAULT {}	Custom metadata
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp
deleted_at	TIMESTAMP	NULL	Soft delete timestamp

Table: projects Indexes:

• idx_projects_tenant_active on (tenant_id, created_at DESC) WHERE deleted_at IS NULL Constraints:
• unique_project_name_per_tenant UNIQUE (tenant_id, name) WHERE deleted_at IS NULL

---

users

User identity and tenant membership.

users.User

Extends AbstractUser with multi-tenant support.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	User identifier
email	VARCHAR(254)	UNIQUE, NOT NULL	User email
first_name	VARCHAR(150)		First name
last_name	VARCHAR(150)		Last name
password	VARCHAR(128)		Hashed password
is_verified	BOOLEAN	DEFAULT FALSE	Email verified
is_active	BOOLEAN	DEFAULT TRUE	Account active
is_staff	BOOLEAN	DEFAULT FALSE	Staff access
is_superuser	BOOLEAN	DEFAULT FALSE	Superuser access
phone	VARCHAR(20)	NULL	Phone number
avatar_url	VARCHAR(500)	NULL	Profile image URL
two_factor_enabled	BOOLEAN	DEFAULT FALSE	2FA status
two_factor_secret	VARCHAR(32)	NULL	TOTP secret (encrypted)
last_login	TIMESTAMP	NULL	Last login time
date_joined	TIMESTAMP	AUTO	Registration time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: users Indexes:

• users_email_idx on (email)
• users_is_active_idx on (is_active)

---

users.TenantMembership

User membership across multiple tenants.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Membership ID
user_id	UUID	FK → users.id, CASCADE	User
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
role	VARCHAR(20)	CHECK IN ('owner', 'admin', 'member', 'viewer')	Role level
is_default	BOOLEAN	DEFAULT FALSE	Default tenant for user
joined_at	TIMESTAMP	DEFAULT NOW()	Join timestamp
revoked_at	TIMESTAMP	NULL	Revocation timestamp

Table: tenant_memberships Indexes:

• idx_membership_user_tenant on (user_id, tenant_id)
• idx_membership_active on (tenant_id, role) WHERE revoked_at IS NULL Constraints:
• unique_user_tenant_membership UNIQUE (user_id, tenant_id)

---

organizations

ADR-047 organizational hierarchy (Platform → Organization → LegalEntity → OperatingUnit).

organizations.Platform

Platform-level organization (e.g., AZ1.AI).

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Platform ID
name	VARCHAR(255)	NOT NULL	Platform name
slug	VARCHAR(100)	UNIQUE, NOT NULL	URL slug
domain	VARCHAR(255)	UNIQUE, NULL	Custom domain
settings	JSONB	DEFAULT {}	Platform settings
is_active	BOOLEAN	DEFAULT TRUE	Active status
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: platforms

---

organizations.Organization

Top-level organization within platform.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Organization ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
platform_id	UUID	FK → platforms.id, CASCADE	Parent platform
name	VARCHAR(255)	NOT NULL	Organization name
slug	VARCHAR(100)	NOT NULL	URL slug
type	VARCHAR(50)	CHECK IN ('enterprise', 'agency', 'startup', 'individual')	Org type
settings	JSONB	DEFAULT {}	Organization settings
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: organizations Constraints:

• unique_org_slug_per_platform UNIQUE (platform_id, slug)

---

organizations.LegalEntity

Legal entities within organization (for billing/contracts).

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Entity ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
organization_id	UUID	TenantFK → organizations.id, CASCADE	Parent org
name	VARCHAR(255)	NOT NULL	Legal name
tax_id	VARCHAR(50)	NULL	Tax identifier
country_code	CHAR(2)	NOT NULL	ISO 3166-1 country
billing_email	VARCHAR(254)	NOT NULL	Billing contact
billing_address	JSONB	DEFAULT {}	Address JSON
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: legal_entities

---

organizations.OperatingUnit

Operating units within legal entities.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Unit ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
legal_entity_id	UUID	TenantFK → legal_entities.id, CASCADE	Parent entity
name	VARCHAR(255)	NOT NULL	Unit name
cost_center	VARCHAR(50)	NULL	Cost center code
settings	JSONB	DEFAULT {}	Unit settings
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: operating_units

---

licenses

License management with Docker registry support (ADR-017).

licenses.License

Software license assignments.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	License ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	UUID	TenantFK → users.id, CASCADE	License owner
license_key	VARCHAR(50)	UNIQUE, NOT NULL	License key
license_type	VARCHAR(20)	CHECK IN ('trial', 'pro', 'team', 'enterprise')	License tier
max_seats	INTEGER	DEFAULT 1	Concurrent seat limit
status	VARCHAR(20)	CHECK IN ('active', 'expired', 'revoked', 'suspended')	Status
expires_at	TIMESTAMP	NULL	Expiration date
metadata	JSONB	DEFAULT {}	Additional metadata
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: licenses Indexes:

• idx_licenses_tenant_status on (tenant_id, status) WHERE status = 'active'
• idx_licenses_license_key on (license_key)
• idx_licenses_user_id on (user_id)

---

licenses.Session

Active license sessions (with heartbeat TTL).

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Session ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
license_id	UUID	TenantFK → licenses.id, CASCADE	Parent license
hardware_id	VARCHAR(64)	NOT NULL	Device fingerprint
device_name	VARCHAR(255)	NULL	Friendly device name
ip_address	INET	NULL	Client IP
started_at	TIMESTAMP	DEFAULT NOW()	Session start
last_heartbeat	TIMESTAMP	AUTO	Last heartbeat
expires_at	TIMESTAMP	NOT NULL	TTL expiration (6 min)
is_active	BOOLEAN	DEFAULT TRUE	Active status

Table: license_sessions Indexes:

• idx_sessions_license_active on (license_id) WHERE is_active = TRUE
• idx_sessions_hardware_id on (hardware_id)
• idx_sessions_expires_at on (expires_at)

---

licenses.DockerPullToken (ADR-017)

Short-lived Docker registry pull tokens.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Token ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
license_id	UUID	TenantFK → licenses.id, CASCADE	Parent license
token_hash	VARCHAR(64)	UNIQUE, NOT NULL	SHA-256 hash of token
repository	VARCHAR(255)	NOT NULL	Target repository path
permissions	JSONB	DEFAULT ['pull']	Allowed actions
issued_at	TIMESTAMP	DEFAULT NOW()	Issue time
expires_at	TIMESTAMP	NOT NULL	Expiration (default 1 hour)
revoked_at	TIMESTAMP	NULL	Revocation time
last_used_at	TIMESTAMP	NULL	Last use time
use_count	INTEGER	DEFAULT 0	Pull count
client_ip	INET	NULL	Requesting IP
user_agent	VARCHAR(500)	NULL	Client user agent

Table: docker_pull_tokens Indexes:

• idx_docker_token_hash on (token_hash)
• idx_docker_token_license on (license_id)
• idx_docker_token_expires on (expires_at) WHERE revoked_at IS NULL

---

Business Domain

subscriptions

Stripe subscription management.

subscriptions.Subscription

Stripe subscription records.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Subscription ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
stripe_subscription_id	VARCHAR(255)	UNIQUE, NOT NULL	Stripe sub ID
stripe_customer_id	VARCHAR(255)	NOT NULL	Stripe customer
status	VARCHAR(20)	CHECK IN ('active', 'past_due', 'canceled', 'incomplete', 'trialing', 'unpaid')	Status
tier	VARCHAR(20)	CHECK IN ('free', 'pro', 'team', 'enterprise')	Tier
max_workstations	INTEGER	DEFAULT 1	Workstation limit
max_users	INTEGER	DEFAULT 1	User limit
max_storage_gb	INTEGER	DEFAULT 10	Storage limit
current_period_start	TIMESTAMP	NOT NULL	Period start
current_period_end	TIMESTAMP	NOT NULL	Period end
canceled_at	TIMESTAMP	NULL	Cancellation time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: subscriptions Indexes:

• idx_subscriptions_stripe_id on (stripe_subscription_id)
• idx_subscriptions_period on (current_period_end) WHERE status = 'active'

---

subscriptions.Invoice

Stripe invoice records.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Invoice ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
subscription_id	UUID	TenantFK → subscriptions.id, CASCADE	Subscription
stripe_invoice_id	VARCHAR(255)	UNIQUE, NOT NULL	Stripe invoice ID
amount_cents	INTEGER	NOT NULL	Invoice amount
currency	VARCHAR(3)	DEFAULT 'usd'	Currency
status	VARCHAR(20)	CHECK IN ('draft', 'open', 'paid', 'void', 'uncollectible')	Status
description	TEXT	DEFAULT ''	Description
invoice_pdf_url	VARCHAR(500)	NULL	PDF download
hosted_invoice_url	VARCHAR(500)	NULL	Hosted invoice
paid_at	TIMESTAMP	NULL	Payment time
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: invoices Indexes:

• idx_invoices_subscription on (subscription_id)
• idx_invoices_stripe_id on (stripe_invoice_id)

---

subscriptions.UsageMetric

Usage tracking for metered billing.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Metric ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
metric_type	VARCHAR(50)	NOT NULL	Metric name
current_value	BIGINT	DEFAULT 0	Current usage
limit_value	BIGINT	NULL	Usage limit
period_start	TIMESTAMP	NOT NULL	Period start
period_end	TIMESTAMP	NOT NULL	Period end
recorded_at	TIMESTAMP	AUTO	Recording time
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: usage_metrics Indexes:

• idx_usage_tenant_type on (tenant_id, metric_type)
• idx_usage_period_end on (period_end) Constraints:
• unique_metric_per_period UNIQUE (tenant_id, metric_type, period_start)

---

billing

Comprehensive billing system (ADR-014).

billing.BillingCustomer

Stripe customer records with billing info.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Customer ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	UUID	TenantFK → users.id, CASCADE	User
stripe_customer_id	VARCHAR(255)	UNIQUE, NOT NULL	Stripe customer ID
email	VARCHAR(254)	NOT NULL	Billing email
name	VARCHAR(255)	NULL	Customer name
phone	VARCHAR(20)	NULL	Phone number
default_payment_method_id	VARCHAR(255)	NULL	Default payment method
currency	VARCHAR(3)	DEFAULT 'usd'	Preferred currency
tax_exempt	VARCHAR(20)	DEFAULT 'none'	Tax exemption status
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: billing_customers

---

billing.BillingDocument

Invoices, credit notes, receipts.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Document ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
customer_id	UUID	TenantFK → billing_customers.id, CASCADE	Customer
document_type	VARCHAR(20)	CHECK IN ('invoice', 'credit_note', 'receipt', 'quote')	Type
document_number	VARCHAR(50)	UNIQUE, NOT NULL	Document number
status	VARCHAR(20)	NOT NULL	Document status
subtotal_cents	INTEGER	DEFAULT 0	Subtotal
tax_cents	INTEGER	DEFAULT 0	Tax amount
total_cents	INTEGER	DEFAULT 0	Total amount
currency	VARCHAR(3)	DEFAULT 'usd'	Currency
due_date	DATE	NULL	Due date
paid_at	TIMESTAMP	NULL	Payment time
voided_at	TIMESTAMP	NULL	Void time
notes	TEXT	DEFAULT ''	Notes
metadata	JSONB	DEFAULT {}	Custom metadata
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: billing_documents

---

billing.CompanyProfile

Company billing profiles.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Profile ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
company_name	VARCHAR(255)	NOT NULL	Company name
tax_id	VARCHAR(50)	NULL	Tax ID
address_line1	VARCHAR(255)	NOT NULL	Address line 1
address_line2	VARCHAR(255)	NULL	Address line 2
city	VARCHAR(100)	NOT NULL	City
state	VARCHAR(100)	NULL	State/Province
postal_code	VARCHAR(20)	NOT NULL	Postal code
country_code	CHAR(2)	NOT NULL	ISO 3166-1 country
phone	VARCHAR(20)	NULL	Phone
email	VARCHAR(254)	NOT NULL	Contact email
logo_url	VARCHAR(500)	NULL	Company logo
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: company_profiles

---

billing.Credit

Account credits for billing.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Credit ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
customer_id	UUID	TenantFK → billing_customers.id, CASCADE	Customer
amount_cents	INTEGER	NOT NULL	Credit amount
currency	VARCHAR(3)	DEFAULT 'usd'	Currency
reason	VARCHAR(255)	NOT NULL	Credit reason
expires_at	TIMESTAMP	NULL	Expiration
used_cents	INTEGER	DEFAULT 0	Amount used
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: billing_credits

---

billing.DocumentAttachment

Attachments for billing documents.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Attachment ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
document_id	UUID	TenantFK → billing_documents.id, CASCADE	Document
filename	VARCHAR(255)	NOT NULL	File name
file_url	VARCHAR(500)	NOT NULL	File URL
file_size	INTEGER	NOT NULL	Size in bytes
mime_type	VARCHAR(100)	NOT NULL	MIME type
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: document_attachments

---

billing.EmailLog

Billing email audit log.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Log ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
document_id	UUID	TenantFK → billing_documents.id, SET NULL	Document
recipient_email	VARCHAR(254)	NOT NULL	Recipient
subject	VARCHAR(255)	NOT NULL	Email subject
template	VARCHAR(100)	NOT NULL	Template used
status	VARCHAR(20)	CHECK IN ('pending', 'sent', 'delivered', 'failed', 'bounced')	Status
sent_at	TIMESTAMP	NULL	Send time
error_message	TEXT	NULL	Error if failed
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: billing_email_logs

---

billing.LineItem

Line items for billing documents.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Item ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
document_id	UUID	TenantFK → billing_documents.id, CASCADE	Document
description	VARCHAR(500)	NOT NULL	Item description
quantity	DECIMAL(10,2)	DEFAULT 1	Quantity
unit_price_cents	INTEGER	NOT NULL	Unit price
amount_cents	INTEGER	NOT NULL	Total amount
tax_rate	DECIMAL(5,2)	DEFAULT 0	Tax rate %
product_code	VARCHAR(50)	NULL	Product code
period_start	TIMESTAMP	NULL	Service period start
period_end	TIMESTAMP	NULL	Service period end
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: billing_line_items

---

billing.Payment

Payment records.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Payment ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
document_id	UUID	TenantFK → billing_documents.id, CASCADE	Document
stripe_payment_intent_id	VARCHAR(255)	UNIQUE, NULL	Stripe PI
amount_cents	INTEGER	NOT NULL	Payment amount
currency	VARCHAR(3)	DEFAULT 'usd'	Currency
payment_method	VARCHAR(50)	NOT NULL	Payment method
status	VARCHAR(20)	CHECK IN ('pending', 'processing', 'succeeded', 'failed', 'refunded')	Status
failure_reason	TEXT	NULL	Failure reason
refunded_cents	INTEGER	DEFAULT 0	Refunded amount
paid_at	TIMESTAMP	NULL	Payment time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: billing_payments

---

workstations

Cloud Workstation management (ADR-005).

workstations.Workstation

Cloud workstation instances.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Workstation ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	UUID	TenantFK → users.id, CASCADE	Owner
name	VARCHAR(255)	NOT NULL	Workstation name
status	VARCHAR(20)	CHECK IN ('creating', 'starting', 'running', 'stopping', 'stopped', 'deleting', 'deleted', 'error')	Status
machine_type	VARCHAR(50)	DEFAULT 'n2-standard-4'	GCP machine type
region	VARCHAR(50)	DEFAULT 'us-central1'	GCP region
gcp_workstation_id	VARCHAR(255)	UNIQUE, NULL	GCP resource ID
gcp_config_name	VARCHAR(255)	NULL	Workstation config
boot_disk_size_gb	INTEGER	DEFAULT 200	Boot disk size
idle_timeout_minutes	INTEGER	DEFAULT 120	Idle timeout
last_activity_at	TIMESTAMP	NULL	Last activity
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: workstations

---

workstations.WorkstationSession

Active workstation sessions.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Session ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
workstation_id	UUID	TenantFK → workstations.id, CASCADE	Workstation
user_id	UUID	TenantFK → users.id, CASCADE	User
started_at	TIMESTAMP	DEFAULT NOW()	Session start
ended_at	TIMESTAMP	NULL	Session end
ip_address	INET	NULL	Client IP
user_agent	VARCHAR(500)	NULL	Client user agent

Table: workstation_sessions

---

workstations.WorkstationConfig

Workstation configuration templates.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Config ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
name	VARCHAR(255)	NOT NULL	Config name
machine_type	VARCHAR(50)	NOT NULL	Default machine type
container_image	VARCHAR(500)	NOT NULL	Container image
boot_disk_size_gb	INTEGER	DEFAULT 200	Boot disk
idle_timeout_minutes	INTEGER	DEFAULT 120	Idle timeout
environment_vars	JSONB	DEFAULT {}	Environment variables
is_default	BOOLEAN	DEFAULT FALSE	Default config
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: workstation_configs

---

workstations.WorkstationSnapshot

Workstation snapshots for backup/restore.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Snapshot ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
workstation_id	UUID	TenantFK → workstations.id, CASCADE	Workstation
name	VARCHAR(255)	NOT NULL	Snapshot name
description	TEXT	DEFAULT ''	Description
gcp_snapshot_id	VARCHAR(255)	UNIQUE, NULL	GCP snapshot ID
size_gb	INTEGER	NULL	Snapshot size
status	VARCHAR(20)	CHECK IN ('creating', 'ready', 'deleting', 'deleted', 'error')	Status
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: workstation_snapshots

---

workstations.WorkstationEvent

Workstation lifecycle events.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Event ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
workstation_id	UUID	TenantFK → workstations.id, CASCADE	Workstation
event_type	VARCHAR(50)	NOT NULL	Event type
status	VARCHAR(20)	NOT NULL	Status before/after
message	TEXT	NULL	Event message
metadata	JSONB	DEFAULT {}	Event metadata
created_at	TIMESTAMP	AUTO	Event time

Table: workstation_events

---

workstations.WorkstationPort

Port forwarding configuration.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Port ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
workstation_id	UUID	TenantFK → workstations.id, CASCADE	Workstation
port	INTEGER	NOT NULL	Container port
protocol	VARCHAR(10)	DEFAULT 'tcp'	Protocol
label	VARCHAR(100)	NULL	Port label
is_public	BOOLEAN	DEFAULT FALSE	Publicly accessible
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: workstation_ports

---

workstations.WorkstationEnvironment

Environment variable sets.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Environment ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
name	VARCHAR(255)	NOT NULL	Environment name
variables	JSONB	DEFAULT {}	Variable map
is_secret	BOOLEAN	DEFAULT FALSE	Contains secrets
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: workstation_environments

---

repositories

Git repository management (ADR-006).

repositories.GiteaOrganization

Gitea organization mapping.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Gitea org ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
gitea_id	INTEGER	UNIQUE, NOT NULL	Gitea org ID
name	VARCHAR(255)	NOT NULL	Org name
gitea_username	VARCHAR(255)	UNIQUE, NOT NULL	Gitea username
avatar_url	VARCHAR(500)	NULL	Avatar URL
description	TEXT	DEFAULT ''	Description
website	VARCHAR(500)	NULL	Website
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: gitea_organizations

---

repositories.Repository

Git repository records.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Repository ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
gitea_org_id	UUID	TenantFK → gitea_organizations.id, CASCADE	Gitea org
gitea_repo_id	INTEGER	UNIQUE, NOT NULL	Gitea repo ID
name	VARCHAR(255)	NOT NULL	Repository name
description	TEXT	DEFAULT ''	Description
default_branch	VARCHAR(100)	DEFAULT 'main'	Default branch
clone_url	VARCHAR(500)	NOT NULL	Clone URL
ssh_url	VARCHAR(500)	NULL	SSH URL
is_private	BOOLEAN	DEFAULT TRUE	Private repo
is_mirror	BOOLEAN	DEFAULT FALSE	Is mirror
size_kb	INTEGER	DEFAULT 0	Size in KB
last_pushed_at	TIMESTAMP	NULL	Last push time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: repositories

---

repositories.RepositoryAccessToken

Repository access tokens.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Token ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	UUID	TenantFK → users.id, CASCADE	User
repository_id	UUID	TenantFK → repositories.id, SET NULL	Repository (or all)
name	VARCHAR(255)	NOT NULL	Token name
token_hash	VARCHAR(64)	UNIQUE, NOT NULL	SHA-256 hash
permissions	JSONB	DEFAULT ['read']	Allowed permissions
expires_at	TIMESTAMP	NULL	Expiration
last_used_at	TIMESTAMP	NULL	Last use time
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: repository_access_tokens

---

repositories.RepositoryMirror

Mirror configuration for external repos.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Mirror ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
repository_id	UUID	TenantFK → repositories.id, CASCADE	Repository
source_url	VARCHAR(500)	NOT NULL	Source repository URL
sync_interval_hours	INTEGER	DEFAULT 24	Sync interval
last_synced_at	TIMESTAMP	NULL	Last sync time
last_sync_status	VARCHAR(20)	CHECK IN ('success', 'failed', 'syncing')	Last status
last_error	TEXT	NULL	Last error message
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: repository_mirrors

---

repositories.TenantGitQuota

Git storage quotas per tenant.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Quota ID
tenant_id	UUID	FK → tenants.id, CASCADE, UNIQUE	Tenant
max_repos	INTEGER	DEFAULT 100	Max repositories
max_storage_mb	INTEGER	DEFAULT 10240	Max storage MB
current_repos	INTEGER	DEFAULT 0	Current repo count
current_storage_mb	INTEGER	DEFAULT 0	Current storage MB
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: tenant_git_quotas

---

repositories.RepositoryWebhook

Webhooks for repository events.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Webhook ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
repository_id	UUID	TenantFK → repositories.id, CASCADE	Repository
url	VARCHAR(500)	NOT NULL	Webhook URL
secret_hash	VARCHAR(64)	NULL	Secret hash
events	JSONB	DEFAULT ['push']	Subscribed events
is_active	BOOLEAN	DEFAULT TRUE	Active status
last_triggered_at	TIMESTAMP	NULL	Last trigger time
last_response_code	INTEGER	NULL	Last HTTP response
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: repository_webhooks

---

Operational Domain

container_sessions

Multi-user container licensing (ADR-055).

container_sessions.ContainerSession

Container session for multi-user licensing.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Session ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
organization_id	UUID	TenantFK → organizations.id, SET NULL	Organization
license_id	UUID	TenantFK → licenses.id, CASCADE	License
container_id	VARCHAR(255)	NOT NULL	Container/Workstation ID
container_type	VARCHAR(20)	CHECK IN ('docker', 'workstation', 'kubernetes')	Type
container_name	VARCHAR(255)	DEFAULT ''	Friendly name
session_token	UUID	UNIQUE, DEFAULT uuid4	Bearer token
status	VARCHAR(20)	CHECK IN ('active', 'released', 'expired', 'terminated')	Status
max_users	INTEGER	DEFAULT 1	Max concurrent users
current_user_count	INTEGER	DEFAULT 0	Current users
last_heartbeat_at	TIMESTAMP	AUTO	Last heartbeat
heartbeat_count	INTEGER	DEFAULT 0	Total heartbeats
heartbeat_interval_seconds	INTEGER	DEFAULT 300	Expected interval
started_at	TIMESTAMP	AUTO	Session start
released_at	TIMESTAMP	NULL	Release time
expired_at	TIMESTAMP	NULL	Expiry time
ip_address	INET	NULL	Container IP
hostname	VARCHAR(255)	DEFAULT ''	Hostname
client_version	VARCHAR(50)	DEFAULT ''	Client version
metadata	JSONB	DEFAULT {}	Additional metadata
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: container_sessions Indexes:

• idx_container_session_tenant_status on (tenant_id, status)
• idx_container_session_license_status on (license_id, status)
• idx_container_session_container_id on (container_id)
• idx_container_session_token on (session_token)
• idx_container_session_heartbeat on (last_heartbeat_at)
• idx_container_session_status_heartbeat on (status, last_heartbeat_at) Constraints:
• unique_active_container_session UNIQUE (license_id, container_id) WHERE status = 'active'

---

container_sessions.ContainerUserSession

User session within a container.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	User session ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant (via container)
container_session_id	UUID	FK → container_sessions.id, CASCADE	Container
user_id	UUID	TenantFK → users.id, SET NULL	User (if authenticated)
user_token	UUID	UNIQUE, DEFAULT uuid4	User session token
external_user_id	VARCHAR(255)	DEFAULT ''	External user ID
user_email	VARCHAR(254)	DEFAULT ''	User email
is_active	BOOLEAN	DEFAULT TRUE	Active status
started_at	TIMESTAMP	AUTO	Session start
last_activity_at	TIMESTAMP	AUTO	Last activity
ended_at	TIMESTAMP	NULL	Session end
ip_address	INET	NULL	User IP
user_agent	TEXT	DEFAULT ''	User agent
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: container_user_sessions Indexes:

• idx_container_user_session_container on (container_session_id, is_active)
• idx_container_user_session_user on (user_id, is_active)
• idx_container_user_session_token on (user_token)
• idx_container_user_session_activity on (last_activity_at)

---

context

Claude Code context sync (ADR-052, ADR-053).

context.ContextMessage

Synced context messages across devices.

Column	Type	Constraints	Description
id	BIGINT	PK, SERIAL	Message ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	VARCHAR(63)	NOT NULL, INDEX	User identifier
content_hash	CHAR(64)	NOT NULL	SHA256 dedup hash
message_type	VARCHAR(50)	NOT NULL	Message type
role	VARCHAR(20)	NOT NULL	Message role
content	TEXT	NOT NULL	Message content
session_id	VARCHAR(255)	NULL, INDEX	Session identifier
source_device_id	VARCHAR(255)	NOT NULL	Origin device
sequence_number	BIGINT	NOT NULL	Ordering sequence
sync_cursor	BIGINT	UNIQUE, NULL	Monotonic cursor
synced_at	TIMESTAMP	DEFAULT NOW()	Sync timestamp
client_version	VARCHAR(50)	NULL	Client version
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: context_messages Indexes:

• context_messages_tenant_cursor_idx on (tenant_id, sync_cursor)
• context_messages_tenant_user_synced_idx on (tenant_id, user_id, synced_at)
• context_messages_session_synced_idx on (session_id, synced_at) Constraints:
• unique_user_content_hash UNIQUE (user_id, content_hash)

---

context.SyncCursor

Per-device sync position tracking.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Cursor ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	VARCHAR(63)	NOT NULL	User identifier
device_id	VARCHAR(255)	NOT NULL	Device identifier
last_sequence	BIGINT	DEFAULT 0	Last synced sequence
last_synced_at	TIMESTAMP	DEFAULT NOW()	Last sync time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: sync_cursors Constraints:

• unique_user_device_cursor UNIQUE (user_id, device_id)

---

context.SyncStats

Aggregated sync statistics.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Stats ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	VARCHAR(63)	NOT NULL	User identifier
total_messages	BIGINT	DEFAULT 0	Total messages
total_bytes	BIGINT	DEFAULT 0	Total bytes synced
last_push_at	TIMESTAMP	NULL	Last push time
last_pull_at	TIMESTAMP	NULL	Last pull time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: sync_stats Constraints:

• unique_user_sync_stats UNIQUE (user_id)

---

context.TaskTracking

Task execution tracking from Claude Code TodoWrite operations.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Task tracking ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	VARCHAR(63)	NOT NULL, INDEX	User identifier
task_id	VARCHAR(50)	NOT NULL, INDEX	Task identifier (e.g., A.9.1.1)
description	TEXT	NOT NULL	Task description from TodoWrite
active_form	VARCHAR(255)	NULL	Present continuous form
session_id	VARCHAR(255)	NULL, INDEX	Session identifier
project_id	VARCHAR(255)	NULL, INDEX	Project path or identifier
status	VARCHAR(20)	DEFAULT 'pending', INDEX	pending, in_progress, completed
outcome	VARCHAR(20)	NULL	success, error, partial, skipped
outcome_score	FLOAT	NULL	Quality score (0.0-1.0)
tool_success_count	INTEGER	DEFAULT 0	Successful tool calls
tool_error_count	INTEGER	DEFAULT 0	Failed tool calls
user_corrections	INTEGER	DEFAULT 0	User interventions
started_at	TIMESTAMP	NULL	When moved to in_progress
completed_at	TIMESTAMP	NULL	When moved to completed
synced_at	TIMESTAMP	AUTO, INDEX	Cloud sync timestamp
updated_at	TIMESTAMP	AUTO	Last update
content_hash	VARCHAR(64)	UNIQUE, INDEX	SHA256 dedup hash
sync_cursor	BIGINT	UNIQUE, NULL, INDEX	Monotonic sync cursor
client_version	VARCHAR(50)	NULL	Client version
hardware_id	VARCHAR(255)	NULL	Device fingerprint

Table: context_task_tracking Indexes:

• context_task_tracking_tenant_cursor_idx on (tenant_id, sync_cursor)
• context_task_tracking_tenant_user_status_idx on (tenant_id, user_id, status)
• context_task_tracking_tenant_project_status_idx on (tenant_id, project_id, status)
• context_task_tracking_session_synced_idx on (session_id, synced_at)
• context_task_tracking_task_id_status_idx on (task_id, status)

---

context.TaskMessage

Links TaskTracking records to related ContextMessages.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Relationship ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
task_id	UUID	TenantFK → task_tracking.id, CASCADE	Parent task
message_id	BIGINT	TenantFK → context_messages.id, CASCADE	Related message
sequence	INTEGER	DEFAULT 0	Order within task
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: context_task_messages Indexes:

• context_task_messages_tenant_task_idx on (tenant_id, task_id)
• context_task_messages_tenant_message_idx on (tenant_id, message_id) Constraints:
• unique_task_message UNIQUE (tenant_id, task_id, message_id)

---

issues

Issue tracking system.

issues.Issue

Issue/ticket records.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Issue ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
project_id	UUID	TenantFK → projects.id, SET NULL	Project
reporter_id	UUID	TenantFK → users.id, SET NULL	Reporter
assignee_id	UUID	TenantFK → users.id, SET NULL	Assignee
title	VARCHAR(500)	NOT NULL	Issue title
description	TEXT	DEFAULT ''	Description
issue_type	VARCHAR(20)	CHECK IN ('bug', 'feature', 'task', 'improvement')	Type
priority	VARCHAR(20)	CHECK IN ('critical', 'high', 'medium', 'low')	Priority
status	VARCHAR(20)	CHECK IN ('open', 'in_progress', 'review', 'resolved', 'closed')	Status
labels	JSONB	DEFAULT []	Labels array
due_date	DATE	NULL	Due date
resolved_at	TIMESTAMP	NULL	Resolution time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: issues

---

issues.IssueComment

Comments on issues.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Comment ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
issue_id	UUID	TenantFK → issues.id, CASCADE	Issue
author_id	UUID	TenantFK → users.id, SET NULL	Author
content	TEXT	NOT NULL	Comment content
is_internal	BOOLEAN	DEFAULT FALSE	Internal only
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: issue_comments

---

issues.IssueAttachment

File attachments for issues.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Attachment ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
issue_id	UUID	TenantFK → issues.id, CASCADE	Issue
uploaded_by_id	UUID	TenantFK → users.id, SET NULL	Uploader
filename	VARCHAR(255)	NOT NULL	File name
file_url	VARCHAR(500)	NOT NULL	File URL
file_size	INTEGER	NOT NULL	Size in bytes
mime_type	VARCHAR(100)	NOT NULL	MIME type
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: issue_attachments

---

issues.IssueNotification

Notifications for issue updates.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Notification ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
issue_id	UUID	TenantFK → issues.id, CASCADE	Issue
user_id	UUID	TenantFK → users.id, CASCADE	Recipient
notification_type	VARCHAR(50)	NOT NULL	Notification type
is_read	BOOLEAN	DEFAULT FALSE	Read status
read_at	TIMESTAMP	NULL	Read time
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: issue_notifications

---

issues.IssueWatcher

Users watching issues.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Watcher ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
issue_id	UUID	TenantFK → issues.id, CASCADE	Issue
user_id	UUID	TenantFK → users.id, CASCADE	Watcher
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: issue_watchers Constraints:

• unique_issue_watcher UNIQUE (issue_id, user_id)

---

workflows

Workflow implementations for WF-107 to WF-119.

workflows.WorkflowDefinition

Workflow template definitions.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Definition ID
code	VARCHAR(20)	UNIQUE, NOT NULL	Workflow code (e.g., WF-107)
name	VARCHAR(255)	NOT NULL	Workflow name
description	TEXT	DEFAULT ''	Description
category	VARCHAR(50)	NOT NULL	Category
steps	JSONB	DEFAULT []	Step definitions
triggers	JSONB	DEFAULT []	Trigger conditions
is_active	BOOLEAN	DEFAULT TRUE	Active status
version	INTEGER	DEFAULT 1	Definition version
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: workflow_definitions

---

workflows.WorkflowInstance

Running workflow instances.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Instance ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
definition_id	UUID	FK → workflow_definitions.id, PROTECT	Definition
initiated_by_id	UUID	TenantFK → users.id, SET NULL	Initiator
status	VARCHAR(20)	CHECK IN ('pending', 'running', 'paused', 'completed', 'failed', 'cancelled')	Status
current_step	INTEGER	DEFAULT 0	Current step index
context_data	JSONB	DEFAULT {}	Workflow context
started_at	TIMESTAMP	NULL	Start time
completed_at	TIMESTAMP	NULL	Completion time
error_message	TEXT	NULL	Error if failed
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: workflow_instances

---

workflows.WorkflowStep

Step execution records.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Step ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
instance_id	UUID	TenantFK → workflow_instances.id, CASCADE	Instance
step_index	INTEGER	NOT NULL	Step number
step_name	VARCHAR(255)	NOT NULL	Step name
status	VARCHAR(20)	CHECK IN ('pending', 'running', 'completed', 'failed', 'skipped')	Status
input_data	JSONB	DEFAULT {}	Step input
output_data	JSONB	DEFAULT {}	Step output
started_at	TIMESTAMP	NULL	Start time
completed_at	TIMESTAMP	NULL	Completion time
error_message	TEXT	NULL	Error if failed
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: workflow_steps

---

workflows.WorkflowApproval

Approval requests for workflow steps.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Approval ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
step_id	UUID	TenantFK → workflow_steps.id, CASCADE	Step
approver_id	UUID	TenantFK → users.id, SET NULL	Approver
status	VARCHAR(20)	CHECK IN ('pending', 'approved', 'rejected', 'expired')	Status
decision_at	TIMESTAMP	NULL	Decision time
comments	TEXT	DEFAULT ''	Comments
expires_at	TIMESTAMP	NULL	Expiration
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: workflow_approvals

---

workflows.WorkflowNotification

Workflow notifications.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Notification ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
instance_id	UUID	TenantFK → workflow_instances.id, CASCADE	Instance
user_id	UUID	TenantFK → users.id, CASCADE	Recipient
notification_type	VARCHAR(50)	NOT NULL	Type
title	VARCHAR(255)	NOT NULL	Title
message	TEXT	NOT NULL	Message
is_read	BOOLEAN	DEFAULT FALSE	Read status
read_at	TIMESTAMP	NULL	Read time
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: workflow_notifications

---

workflows.OrganizationSettings (WF-107)

Organization settings management.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Settings ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
organization_id	UUID	TenantFK → organizations.id, CASCADE	Organization
settings_key	VARCHAR(100)	NOT NULL	Setting key
settings_value	JSONB	NOT NULL	Setting value
changed_by_id	UUID	TenantFK → users.id, SET NULL	Changed by
effective_at	TIMESTAMP	DEFAULT NOW()	Effective time
created_at	TIMESTAMP	AUTO	Creation timestamp

Table: organization_settings Constraints:

• unique_org_setting UNIQUE (organization_id, settings_key)

---

workflows.TeamRoleChange (WF-108)

Team role change requests.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Change ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
team_id	UUID	TenantFK → teams.id, CASCADE	Team
user_id	UUID	TenantFK → users.id, CASCADE	User
old_role	VARCHAR(50)	NOT NULL	Previous role
new_role	VARCHAR(50)	NOT NULL	New role
requested_by_id	UUID	TenantFK → users.id, SET NULL	Requester
approved_by_id	UUID	TenantFK → users.id, SET NULL	Approver
status	VARCHAR(20)	CHECK IN ('pending', 'approved', 'rejected', 'applied')	Status
reason	TEXT	DEFAULT ''	Reason
effective_at	TIMESTAMP	NULL	Effective time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: team_role_changes

---

workflows.LicenseSeatReallocation (WF-109)

License seat reallocation tracking.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Reallocation ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
license_id	UUID	TenantFK → licenses.id, CASCADE	License
from_user_id	UUID	TenantFK → users.id, SET NULL	From user
to_user_id	UUID	TenantFK → users.id, SET NULL	To user
requested_by_id	UUID	TenantFK → users.id, SET NULL	Requester
status	VARCHAR(20)	CHECK IN ('pending', 'approved', 'completed', 'rejected')	Status
reason	TEXT	DEFAULT ''	Reason
completed_at	TIMESTAMP	NULL	Completion time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: license_seat_reallocations

---

workflows.ContractorAccess (WF-110)

Contractor access with expiration.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Access ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
user_id	UUID	TenantFK → users.id, CASCADE	Contractor user
granted_by_id	UUID	TenantFK → users.id, SET NULL	Granter
access_level	VARCHAR(50)	NOT NULL	Access level
project_ids	JSONB	DEFAULT []	Accessible projects
starts_at	TIMESTAMP	DEFAULT NOW()	Start time
expires_at	TIMESTAMP	NOT NULL	Expiration
extended_count	INTEGER	DEFAULT 0	Extension count
last_extended_by_id	UUID	TenantFK → users.id, SET NULL	Last extender
is_active	BOOLEAN	DEFAULT TRUE	Active status
revoked_at	TIMESTAMP	NULL	Revocation time
revoked_by_id	UUID	TenantFK → users.id, SET NULL	Revoker
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: contractor_access

---

workflows.AgencyConsolidatedBilling (WF-111)

Agency consolidated billing records.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Billing ID
tenant_id	UUID	FK → tenants.id, CASCADE	Agency tenant
billing_period_start	DATE	NOT NULL	Period start
billing_period_end	DATE	NOT NULL	Period end
client_tenant_ids	JSONB	DEFAULT []	Client tenants
total_amount_cents	INTEGER	DEFAULT 0	Total amount
currency	VARCHAR(3)	DEFAULT 'usd'	Currency
status	VARCHAR(20)	CHECK IN ('draft', 'sent', 'paid', 'overdue')	Status
invoice_number	VARCHAR(50)	UNIQUE, NULL	Invoice number
sent_at	TIMESTAMP	NULL	Sent time
paid_at	TIMESTAMP	NULL	Paid time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: agency_consolidated_billing

---

workflows.SubscriptionDowngrade (WF-112)

Subscription downgrade requests.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Downgrade ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
subscription_id	UUID	TenantFK → subscriptions.id, CASCADE	Subscription
current_tier	VARCHAR(50)	NOT NULL	Current tier
target_tier	VARCHAR(50)	NOT NULL	Target tier
requested_by_id	UUID	TenantFK → users.id, SET NULL	Requester
reason	TEXT	DEFAULT ''	Reason
effective_date	DATE	NOT NULL	Effective date
status	VARCHAR(20)	CHECK IN ('pending', 'scheduled', 'completed', 'cancelled')	Status
data_actions	JSONB	DEFAULT {}	Required data actions
completed_at	TIMESTAMP	NULL	Completion time
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: subscription_downgrades

---

workflows.AuditorAccess (WF-113)

Auditor access with automatic revocation.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Access ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
auditor_email	VARCHAR(254)	NOT NULL	Auditor email
auditor_name	VARCHAR(255)	NOT NULL	Auditor name
audit_firm	VARCHAR(255)	NULL	Audit firm
granted_by_id	UUID	TenantFK → users.id, SET NULL	Granter
access_scope	JSONB	DEFAULT {}	Access scope
starts_at	TIMESTAMP	DEFAULT NOW()	Start time
expires_at	TIMESTAMP	NOT NULL	Expiration
is_active	BOOLEAN	DEFAULT TRUE	Active status
revoked_at	TIMESTAMP	NULL	Revocation time
revoked_by_id	UUID	TenantFK → users.id, SET NULL	Revoker
audit_log_url	VARCHAR(500)	NULL	Audit log export
created_at	TIMESTAMP	AUTO	Creation timestamp
updated_at	TIMESTAMP	AUTO	Last update timestamp

Table: auditor_access

---

workflows.WorkflowAuditLog

Comprehensive audit log for workflows.

Column	Type	Constraints	Description
id	UUID	PK, DEFAULT uuid4	Log ID
tenant_id	UUID	FK → tenants.id, CASCADE	Tenant
instance_id	UUID	TenantFK → workflow_instances.id, SET NULL	Instance
action	VARCHAR(100)	NOT NULL	Action performed
actor_id	UUID	TenantFK → users.id, SET NULL	Actor
actor_ip	INET	NULL	Actor IP
target_type	VARCHAR(100)	NULL	Target entity type
target_id	UUID	NULL	Target entity ID
before_state	JSONB	NULL	State before
after_state	JSONB	NULL	State after
metadata	JSONB	DEFAULT {}	Additional metadata
created_at	TIMESTAMP	AUTO	Event time

Table: workflow_audit_logs

---

Reference Data Domain

reference_data

ISO standard reference data (ISO 3166, 4217, E.164).

reference_data.Country

ISO 3166-1 countries.

Column	Type	Constraints	Description
id	INTEGER	PK, SERIAL	Country ID
code	CHAR(2)	UNIQUE, NOT NULL	ISO 3166-1 alpha-2
code_alpha3	CHAR(3)	UNIQUE, NOT NULL	ISO 3166-1 alpha-3
name	VARCHAR(100)	NOT NULL	Country name
numeric_code	CHAR(3)	UNIQUE, NOT NULL	ISO 3166-1 numeric
is_active	BOOLEAN	DEFAULT TRUE	Active status

Table: ref_countries

---

reference_data.State

ISO 3166-2 subdivisions.

Column	Type	Constraints	Description
id	INTEGER	PK, SERIAL	State ID
country_id	INTEGER	FK → ref_countries.id, CASCADE	Country
code	VARCHAR(6)	NOT NULL	ISO 3166-2 code
name	VARCHAR(100)	NOT NULL	State/Province name
type	VARCHAR(50)	NULL	Type (state, province, etc.)
is_active	BOOLEAN	DEFAULT TRUE	Active status

Table: ref_states Constraints:

• unique_state_per_country UNIQUE (country_id, code)

---

reference_data.City

Cities (major cities only).

Column	Type	Constraints	Description
id	INTEGER	PK, SERIAL	City ID
state_id	INTEGER	FK → ref_states.id, CASCADE	State
name	VARCHAR(100)	NOT NULL	City name
latitude	DECIMAL(9,6)	NULL	Latitude
longitude	DECIMAL(9,6)	NULL	Longitude
population	INTEGER	NULL	Population
timezone	VARCHAR(50)	NULL	Timezone
is_active	BOOLEAN	DEFAULT TRUE	Active status

Table: ref_cities

---

reference_data.PostalCode

Postal/ZIP codes.

Column	Type	Constraints	Description
id	INTEGER	PK, SERIAL	Postal code ID
country_id	INTEGER	FK → ref_countries.id, CASCADE	Country
code	VARCHAR(20)	NOT NULL	Postal code
city_name	VARCHAR(100)	NULL	City name
state_code	VARCHAR(10)	NULL	State code
latitude	DECIMAL(9,6)	NULL	Latitude
longitude	DECIMAL(9,6)	NULL	Longitude

Table: ref_postal_codes Indexes:

• idx_postal_code_country on (country_id, code)

---

reference_data.Currency

ISO 4217 currencies.

Column	Type	Constraints	Description
id	INTEGER	PK, SERIAL	Currency ID
code	CHAR(3)	UNIQUE, NOT NULL	ISO 4217 code
name	VARCHAR(100)	NOT NULL	Currency name
symbol	VARCHAR(10)	NULL	Currency symbol
decimal_places	INTEGER	DEFAULT 2	Decimal places
is_active	BOOLEAN	DEFAULT TRUE	Active status

Table: ref_currencies

---

reference_data.PhoneCountryCode

ITU E.164 phone country codes.

Column	Type	Constraints	Description
id	INTEGER	PK, SERIAL	Code ID
country_id	INTEGER	FK → ref_countries.id, CASCADE	Country
dialing_code	VARCHAR(10)	NOT NULL	Dialing code
trunk_prefix	VARCHAR(5)	NULL	Trunk prefix
format_pattern	VARCHAR(100)	NULL	Format regex

Table: ref_phone_country_codes

---

Multi-Tenant Architecture

All tenant-scoped models inherit from TenantModel and use:

from django_multitenant.models import TenantModel
from django_multitenant.fields import TenantForeignKey

class MyModel(TenantModel):
    tenant_id = 'tenant_id'

    tenant = models.ForeignKey(
        'tenants.Tenant',
        on_delete=models.CASCADE,
        db_column='tenant_id'
    )

    # Use TenantForeignKey for related tenant-scoped models
    related = TenantForeignKey(
        OtherTenantModel,
        on_delete=models.CASCADE
    )

    class Meta:
        # Enables automatic tenant filtering
        pass

# Usage with automatic filtering
from django_multitenant.utils import set_current_tenant

set_current_tenant(tenant)
MyModel.objects.all()  # Automatically filtered by tenant

Key Patterns:

1. TenantModel inheritance - All tenant-scoped models inherit from TenantModel
2. tenant_id column - Every tenant-scoped table has tenant_id FK
3. TenantForeignKey - Used for relationships between tenant-scoped models
4. TenantManager - Automatic query filtering by current tenant
5. set_current_tenant() - Context manager for tenant scope

---

Migration Status

App	Latest Migration	Status
tenants	0003_team_teammembership	Applied
users	0004_tenantmembership	Applied
organizations	0002_platform_organization_hierarchy	Applied
licenses	0005_dockerpulltoken	Applied
subscriptions	0003_usagemetric	Applied
billing	0004_billing_email_log	Applied
workstations	0006_workstation_environment	Applied
repositories	0005_repository_webhook	Applied
container_sessions	0001_initial	Applied
context	0004_tasktracking_taskmessage	Applied
issues	0003_issue_notification_watcher	Applied
workflows	0008_auditor_access	Applied
reference_data	0002_phone_country_code	Applied

Total Migrations: 45 All Applied: Yes

---

Index Reference

Performance-Critical Indexes

Table	Index	Purpose
container_sessions	idx_container_session_status_heartbeat	Session cleanup queries
context_messages	idx_context_messages_tenant_cursor	Sync pagination
context_task_tracking	idx_task_tracking_task_status	Task status queries
licenses	idx_licenses_tenant_status	Active license lookup
subscriptions	idx_subscriptions_period	Billing period queries
docker_pull_tokens	idx_docker_token_hash	Token validation

Composite Indexes

Table	Index	Columns
tenant_memberships	idx_membership_active	(tenant_id, role) WHERE revoked_at IS NULL
projects	idx_projects_tenant_active	(tenant_id, created_at DESC) WHERE deleted_at IS NULL
workflow_instances	idx_workflow_tenant_status	(tenant_id, status, started_at)

---

SQL Generation Commands

Generate SQL DDL on-demand using these commands. This keeps the documentation maintainable while providing exact SQL when needed.

Generate SQL for Specific Migration

# SQL for a single migration
python manage.py sqlmigrate licenses 0005_dockerpulltoken

# SQL for container_sessions initial migration
python manage.py sqlmigrate container_sessions 0001_initial

# SQL for context app task tracking
python manage.py sqlmigrate context 0004_tasktracking_taskmessage

Generate SQL for All Migrations in an App

# All migrations for an app (run sequentially)
for migration in $(python manage.py showmigrations licenses --plan | grep licenses | awk '{print $2}'); do
    echo "-- Migration: $migration"
    python manage.py sqlmigrate licenses $migration
    echo ""
done

Dump Current Schema from Database

# Full schema dump (recommended for production reference)
pg_dump --schema-only --no-owner --no-privileges \
    -h <host> -U <user> -d coditect_db > schema.sql

# Schema for specific tables
pg_dump --schema-only --table=container_sessions --table=container_user_sessions \
    -h <host> -U <user> -d coditect_db

# Local development (using environment variables)
DATABASE_URL=$DATABASE_URL pg_dump --schema-only > schema.sql

Inspect Database Schema

# List all tables
python manage.py inspectdb | grep "class "

# Generate Django models from existing database (reverse engineering)
python manage.py inspectdb > generated_models.py

# Show table structure in psql
psql -d coditect_db -c "\d+ container_sessions"

# List all indexes
psql -d coditect_db -c "\di"

# List foreign keys for a table
psql -d coditect_db -c "
SELECT
    tc.constraint_name,
    kcu.column_name,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
    AND tc.table_name = 'container_sessions';
"

Quick Reference Commands

Task	Command
Show migration SQL	python manage.py sqlmigrate <app> <migration>
Show all migrations	python manage.py showmigrations
Check migration status	python manage.py showmigrations --plan
Dump schema	pg_dump --schema-only -d coditect_db
Table structure	psql -c "\d+ <table_name>"
List indexes	psql -c "\di"
List constraints	psql -c "\d+ <table_name>"

---

Related Documents

Document	Purpose
database-architecture.md	Architecture overview
database-integration-guide.md	Integration patterns
ADR-017	Docker registry auth
ADR-047	Org hierarchy
ADR-052	Intent-aware context
ADR-053	Cloud sync architecture
ADR-055	Container sessions

---

Document Version: 2.1.0 Last Updated: January 5, 2026 Total Models: 82 across 15 apps Author: CODITECT Engineering Team