from __future__ import annotations
import uuid
from datetime import UTC, datetime
from typing import Any
from sqlalchemy import (
CheckConstraint,
DateTime,
ForeignKey,
Index,
Integer,
Text,
UniqueConstraint,
text,
)
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
def utcnow() -> datetime:
return datetime.now(UTC)
class Base(DeclarativeBase):
pass
class User(Base):
"""Mirror of a gnexus-auth principal. Real values are populated in Phase 4."""
__tablename__ = "users"
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
sub: Mapped[str] = mapped_column(Text, unique=True, nullable=False)
email: Mapped[str | None] = mapped_column(Text)
display_name: Mapped[str | None] = mapped_column(Text)
profile: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False, default=dict)
locale: Mapped[str | None] = mapped_column(Text)
system_role: Mapped[str | None] = mapped_column(Text)
status: Mapped[str | None] = mapped_column(Text)
client_roles: Mapped[list[str]] = mapped_column(
JSONB, nullable=False, default=list, server_default=text("'[]'::jsonb")
)
permissions: Mapped[list[str]] = mapped_column(
JSONB, nullable=False, default=list, server_default=text("'[]'::jsonb")
)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
updated_at: Mapped[datetime] = mapped_column(
DateTime(timezone=True), nullable=False, default=utcnow, onupdate=utcnow
)
blocked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
class UserSession(Base):
__tablename__ = "user_sessions"
__table_args__ = (Index("ix_user_sessions_user_id", "user_id"),)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
user_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False
)
token_hash: Mapped[str] = mapped_column(Text, unique=True, nullable=False)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
last_used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
user_agent: Mapped[str | None] = mapped_column(Text)
ip_address: Mapped[str | None] = mapped_column(Text)
class OAuthState(Base):
__tablename__ = "oauth_states"
state_hash: Mapped[str] = mapped_column(Text, primary_key=True)
code_verifier: Mapped[str | None] = mapped_column(Text)
return_to: Mapped[str | None] = mapped_column(Text)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
class WebhookEvent(Base):
__tablename__ = "webhook_events"
id: Mapped[str] = mapped_column(Text, primary_key=True)
event_type: Mapped[str] = mapped_column(Text, nullable=False)
received_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
payload: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False, default=dict)
class Blob(Base):
__tablename__ = "blobs"
hash: Mapped[str] = mapped_column(Text, primary_key=True)
content: Mapped[str] = mapped_column(Text, nullable=False)
size: Mapped[int] = mapped_column(Integer, nullable=False)
class Changeset(Base):
__tablename__ = "changesets"
__table_args__ = (
CheckConstraint(
"status IN ('draft', 'applied', 'rejected')",
name="ck_changesets_status",
),
)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
author_user_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("users.id"), nullable=True
)
summary: Mapped[str] = mapped_column(Text, nullable=False)
reason: Mapped[str] = mapped_column(Text, nullable=False, default="")
status: Mapped[str] = mapped_column(Text, nullable=False, default="draft")
actor_kind: Mapped[str] = mapped_column(
Text, nullable=False, default="system", server_default=text("'system'")
)
actor_name: Mapped[str | None] = mapped_column(Text)
via: Mapped[str] = mapped_column(
Text, nullable=False, default="import", server_default=text("'import'")
)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
applied_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
validation: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
class ChangeItem(Base):
__tablename__ = "change_items"
__table_args__ = (
CheckConstraint(
"kind IN ('doc', 'inventory', 'inventory-item')",
name="ck_change_items_kind",
),
CheckConstraint(
"status IN ('draft', 'applied', 'rejected')",
name="ck_change_items_status",
),
)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
changeset_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True), ForeignKey("changesets.id", ondelete="CASCADE"), nullable=False
)
kind: Mapped[str] = mapped_column(Text, nullable=False)
target: Mapped[str] = mapped_column(Text, nullable=False)
payload: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False, default=dict)
status: Mapped[str] = mapped_column(Text, nullable=False, default="draft")
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
class Document(Base):
__tablename__ = "documents"
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
slug: Mapped[str] = mapped_column(Text, unique=True, nullable=False)
title: Mapped[str] = mapped_column(Text, nullable=False)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
class DocVersion(Base):
__tablename__ = "doc_versions"
__table_args__ = (
UniqueConstraint("document_id", "version_no", name="uq_doc_versions_document_version"),
Index("ix_doc_versions_content_hash", "content_hash"),
)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
document_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True), ForeignKey("documents.id", ondelete="CASCADE"), nullable=False
)
version_no: Mapped[int] = mapped_column(Integer, nullable=False)
parent_version_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("doc_versions.id"), nullable=True
)
content_hash: Mapped[str] = mapped_column(Text, nullable=False)
raw: Mapped[str] = mapped_column(Text, nullable=False)
body: Mapped[str] = mapped_column(Text, nullable=False)
frontmatter: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False, default=dict)
author_user_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("users.id"), nullable=True
)
actor_kind: Mapped[str] = mapped_column(Text, nullable=False, default="system")
actor_name: Mapped[str | None] = mapped_column(Text)
via: Mapped[str] = mapped_column(Text, nullable=False, default="import")
changeset_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("changesets.id"), nullable=True
)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
class JsonSchema(Base):
__tablename__ = "schemas"
__table_args__ = (UniqueConstraint("name", "version", name="uq_schemas_name_version"),)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
name: Mapped[str] = mapped_column(Text, nullable=False)
version: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
body: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
class InventoryType(Base):
__tablename__ = "inventory_types"
name: Mapped[str] = mapped_column(Text, primary_key=True)
schema_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("schemas.id"), nullable=True
)
class InventoryItem(Base):
__tablename__ = "inventory_items"
__table_args__ = (
Index(
"uq_inventory_items_type_item_live",
"type",
"item_id",
unique=True,
postgresql_where=text("deleted_at IS NULL"),
),
Index("ix_inventory_items_type_position", "type", "position"),
)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
type: Mapped[str] = mapped_column(
Text, ForeignKey("inventory_types.name"), nullable=False
)
item_id: Mapped[str] = mapped_column(Text, nullable=False)
position: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
data: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False, default=dict)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
class InventoryItemVersion(Base):
__tablename__ = "inventory_item_versions"
__table_args__ = (
UniqueConstraint(
"inventory_item_id", "version_no", name="uq_inventory_item_versions_item_version"
),
)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
inventory_item_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True), ForeignKey("inventory_items.id", ondelete="CASCADE"), nullable=False
)
version_no: Mapped[int] = mapped_column(Integer, nullable=False)
data: Mapped[dict[str, Any]] = mapped_column(JSONB, nullable=False, default=dict)
author_user_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("users.id"), nullable=True
)
changeset_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("changesets.id"), nullable=True
)
actor_kind: Mapped[str] = mapped_column(
Text, nullable=False, default="system", server_default=text("'system'")
)
actor_name: Mapped[str | None] = mapped_column(Text)
via: Mapped[str] = mapped_column(
Text, nullable=False, default="import", server_default=text("'import'")
)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
class ValidationRun(Base):
__tablename__ = "validation_runs"
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
changeset_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("changesets.id"), nullable=True
)
status: Mapped[str] = mapped_column(Text, nullable=False)
issues: Mapped[list[dict[str, Any]]] = mapped_column(JSONB, nullable=False, default=list)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)
class AuditLog(Base):
__tablename__ = "audit_log"
__table_args__ = (Index("ix_audit_log_created_at", "created_at"),)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
actor_user_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True), ForeignKey("users.id"), nullable=True
)
actor_kind: Mapped[str] = mapped_column(Text, nullable=False, default="system")
actor_name: Mapped[str | None] = mapped_column(Text)
via: Mapped[str] = mapped_column(Text, nullable=False, default="import")
action: Mapped[str] = mapped_column(Text, nullable=False)
entity_type: Mapped[str] = mapped_column(Text, nullable=False)
entity_id: Mapped[str] = mapped_column(Text, nullable=False)
before: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
after: Mapped[dict[str, Any] | None] = mapped_column(JSONB)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow)