diff --git a/.gitignore b/.gitignore index 0bb07da..21d5c54 100644 --- a/.gitignore +++ b/.gitignore @@ -1,5 +1,8 @@ .venv/ server/.venv/ +.env +.env.* +!.env.example __pycache__/ *.py[cod] .pytest_cache/ diff --git a/server/.env.example b/server/.env.example new file mode 100644 index 0000000..d397aee --- /dev/null +++ b/server/.env.example @@ -0,0 +1,8 @@ +# Storage backend: file (default) or db +GNEXUS_BOOK_STORAGE_BACKEND=file +# PostgreSQL URL used when the backend is db +GNEXUS_BOOK_DATABASE_URL=postgresql+psycopg://gmikcon@127.0.0.1:5433/gnexus_book +# Repository root for the file backend and the importer +# GNEXUS_BOOK_REPO_ROOT=/path/to/gnexus-book +# MCP transport for development: stdio, sse, or streamable-http +# GNEXUS_BOOK_MCP_TRANSPORT=stdio diff --git a/server/alembic.ini b/server/alembic.ini new file mode 100644 index 0000000..5d18e1b --- /dev/null +++ b/server/alembic.ini @@ -0,0 +1,39 @@ +[alembic] +script_location = alembic +prepend_sys_path = . +path_separator = os +sqlalchemy.url = + +[loggers] +keys = root,sqlalchemy,alembic + +[handlers] +keys = console + +[formatters] +keys = generic + +[logger_root] +level = WARNING +handlers = console +qualname = + +[logger_sqlalchemy] +level = WARNING +handlers = +qualname = sqlalchemy.engine + +[logger_alembic] +level = INFO +handlers = +qualname = alembic + +[handler_console] +class = StreamHandler +args = (sys.stderr,) +level = NOTSET +formatter = generic + +[formatter_generic] +format = %(levelname)-5.5s [%(name)s] %(message)s +datefmt = %H:%M:%S diff --git a/server/alembic/env.py b/server/alembic/env.py new file mode 100644 index 0000000..38dc45a --- /dev/null +++ b/server/alembic/env.py @@ -0,0 +1,61 @@ +from __future__ import annotations + +import os +from logging.config import fileConfig + +from alembic import context +from sqlalchemy import engine_from_config, pool + +from app.storage.models import Base + +config = context.config + +if config.config_file_name is not None: + fileConfig(config.config_file_name) + +target_metadata = Base.metadata + + +def _database_url() -> str: + url = os.environ.get("GNEXUS_BOOK_DATABASE_URL") or config.get_main_option("sqlalchemy.url") + if not url: + raise RuntimeError( + "Set GNEXUS_BOOK_DATABASE_URL or sqlalchemy.url before running Alembic" + ) + return url + + +def run_migrations_offline() -> None: + context.configure( + url=_database_url(), + target_metadata=target_metadata, + literal_binds=True, + dialect_opts={"paramstyle": "named"}, + compare_type=True, + ) + with context.begin_transaction(): + context.run_migrations() + + +def run_migrations_online() -> None: + configuration = config.get_section(config.config_ini_section, {}) + configuration["sqlalchemy.url"] = _database_url() + connectable = engine_from_config( + configuration, + prefix="sqlalchemy.", + poolclass=pool.NullPool, + ) + with connectable.connect() as connection: + context.configure( + connection=connection, + target_metadata=target_metadata, + compare_type=True, + ) + with context.begin_transaction(): + context.run_migrations() + + +if context.is_offline_mode(): + run_migrations_offline() +else: + run_migrations_online() diff --git a/server/alembic/script.py.mako b/server/alembic/script.py.mako new file mode 100644 index 0000000..ee746cf --- /dev/null +++ b/server/alembic/script.py.mako @@ -0,0 +1,28 @@ +"""${message} + +Revision ID: ${up_revision} +Revises: ${down_revision | comma,n} +Create Date: ${create_date} + +""" +from __future__ import annotations + +from typing import Sequence, Union + +from alembic import op +import sqlalchemy as sa +${imports if imports else ""} + +# revision identifiers, used by Alembic. +revision: str = ${repr(up_revision)} +down_revision: Union[str, None] = ${repr(down_revision)} +branch_labels: Union[str, Sequence[str], None] = ${repr(branch_labels)} +depends_on: Union[str, Sequence[str], None] = ${repr(depends_on)} + + +def upgrade() -> None: + ${upgrades if upgrades else "pass"} + + +def downgrade() -> None: + ${downgrades if downgrades else "pass"} diff --git a/server/alembic/versions/ac5f4cd0d7bd_initial_schema.py b/server/alembic/versions/ac5f4cd0d7bd_initial_schema.py new file mode 100644 index 0000000..e459a52 --- /dev/null +++ b/server/alembic/versions/ac5f4cd0d7bd_initial_schema.py @@ -0,0 +1,190 @@ +"""initial schema + +Revision ID: ac5f4cd0d7bd +Revises: +Create Date: 2026-11-19 18:39:59.501658 + +""" +from __future__ import annotations + +from typing import Sequence, Union + +from alembic import op +import sqlalchemy as sa +from sqlalchemy.dialects import postgresql + +# revision identifiers, used by Alembic. +revision: str = 'ac5f4cd0d7bd' +down_revision: Union[str, None] = None +branch_labels: Union[str, Sequence[str], None] = None +depends_on: Union[str, Sequence[str], None] = None + + +def upgrade() -> None: + # ### commands auto generated by Alembic - please adjust! ### + op.create_table('blobs', + sa.Column('hash', sa.Text(), nullable=False), + sa.Column('content', sa.Text(), nullable=False), + sa.Column('size', sa.Integer(), nullable=False), + sa.PrimaryKeyConstraint('hash') + ) + op.create_table('documents', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('slug', sa.Text(), nullable=False), + sa.Column('title', sa.Text(), nullable=False), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('slug') + ) + op.create_table('schemas', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('name', sa.Text(), nullable=False), + sa.Column('version', sa.Integer(), nullable=False), + sa.Column('body', postgresql.JSONB(astext_type=sa.Text()), nullable=False), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('name', 'version', name='uq_schemas_name_version') + ) + op.create_table('users', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('sub', sa.Text(), nullable=False), + sa.Column('email', sa.Text(), nullable=True), + sa.Column('display_name', sa.Text(), nullable=True), + sa.Column('profile', postgresql.JSONB(astext_type=sa.Text()), nullable=False), + sa.Column('locale', sa.Text(), nullable=True), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False), + sa.Column('blocked_at', sa.DateTime(timezone=True), nullable=True), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('sub') + ) + op.create_table('audit_log', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('actor_user_id', sa.UUID(), nullable=True), + sa.Column('actor_kind', sa.Text(), nullable=False), + sa.Column('actor_name', sa.Text(), nullable=True), + sa.Column('via', sa.Text(), nullable=False), + sa.Column('action', sa.Text(), nullable=False), + sa.Column('entity_type', sa.Text(), nullable=False), + sa.Column('entity_id', sa.Text(), nullable=False), + sa.Column('before', postgresql.JSONB(astext_type=sa.Text()), nullable=True), + sa.Column('after', postgresql.JSONB(astext_type=sa.Text()), nullable=True), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.ForeignKeyConstraint(['actor_user_id'], ['users.id'], ), + sa.PrimaryKeyConstraint('id') + ) + op.create_index('ix_audit_log_created_at', 'audit_log', ['created_at'], unique=False) + op.create_table('changesets', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('author_user_id', sa.UUID(), nullable=True), + sa.Column('summary', sa.Text(), nullable=False), + sa.Column('reason', sa.Text(), nullable=False), + sa.Column('status', sa.Text(), nullable=False), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.Column('applied_at', sa.DateTime(timezone=True), nullable=True), + sa.Column('validation', postgresql.JSONB(astext_type=sa.Text()), nullable=True), + sa.CheckConstraint("status IN ('draft', 'applied', 'rejected')", name='ck_changesets_status'), + sa.ForeignKeyConstraint(['author_user_id'], ['users.id'], ), + sa.PrimaryKeyConstraint('id') + ) + op.create_table('inventory_types', + sa.Column('name', sa.Text(), nullable=False), + sa.Column('schema_id', sa.UUID(), nullable=True), + sa.ForeignKeyConstraint(['schema_id'], ['schemas.id'], ), + sa.PrimaryKeyConstraint('name') + ) + op.create_table('change_items', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('changeset_id', sa.UUID(), nullable=False), + sa.Column('kind', sa.Text(), nullable=False), + sa.Column('target', sa.Text(), nullable=False), + sa.Column('payload', postgresql.JSONB(astext_type=sa.Text()), nullable=False), + sa.Column('status', sa.Text(), nullable=False), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.CheckConstraint("kind IN ('doc', 'inventory', 'inventory-item')", name='ck_change_items_kind'), + sa.CheckConstraint("status IN ('draft', 'applied', 'rejected')", name='ck_change_items_status'), + sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ondelete='CASCADE'), + sa.PrimaryKeyConstraint('id') + ) + op.create_table('doc_versions', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('document_id', sa.UUID(), nullable=False), + sa.Column('version_no', sa.Integer(), nullable=False), + sa.Column('parent_version_id', sa.UUID(), nullable=True), + sa.Column('content_hash', sa.Text(), nullable=False), + sa.Column('raw', sa.Text(), nullable=False), + sa.Column('body', sa.Text(), nullable=False), + sa.Column('frontmatter', postgresql.JSONB(astext_type=sa.Text()), nullable=False), + sa.Column('author_user_id', sa.UUID(), nullable=True), + sa.Column('actor_kind', sa.Text(), nullable=False), + sa.Column('actor_name', sa.Text(), nullable=True), + sa.Column('via', sa.Text(), nullable=False), + sa.Column('changeset_id', sa.UUID(), nullable=True), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.ForeignKeyConstraint(['author_user_id'], ['users.id'], ), + sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ), + sa.ForeignKeyConstraint(['document_id'], ['documents.id'], ondelete='CASCADE'), + sa.ForeignKeyConstraint(['parent_version_id'], ['doc_versions.id'], ), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('document_id', 'version_no', name='uq_doc_versions_document_version') + ) + op.create_index('ix_doc_versions_content_hash', 'doc_versions', ['content_hash'], unique=False) + op.create_table('inventory_items', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('type', sa.Text(), nullable=False), + sa.Column('item_id', sa.Text(), nullable=False), + sa.Column('position', sa.Integer(), nullable=False), + sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=False), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.Column('deleted_at', sa.DateTime(timezone=True), nullable=True), + sa.ForeignKeyConstraint(['type'], ['inventory_types.name'], ), + sa.PrimaryKeyConstraint('id') + ) + op.create_index('ix_inventory_items_type_position', 'inventory_items', ['type', 'position'], unique=False) + op.create_index('uq_inventory_items_type_item_live', 'inventory_items', ['type', 'item_id'], unique=True, postgresql_where=sa.text('deleted_at IS NULL')) + op.create_table('validation_runs', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('changeset_id', sa.UUID(), nullable=True), + sa.Column('status', sa.Text(), nullable=False), + sa.Column('issues', postgresql.JSONB(astext_type=sa.Text()), nullable=False), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ), + sa.PrimaryKeyConstraint('id') + ) + op.create_table('inventory_item_versions', + sa.Column('id', sa.UUID(), nullable=False), + sa.Column('inventory_item_id', sa.UUID(), nullable=False), + sa.Column('version_no', sa.Integer(), nullable=False), + sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=False), + sa.Column('author_user_id', sa.UUID(), nullable=True), + sa.Column('changeset_id', sa.UUID(), nullable=True), + sa.Column('created_at', sa.DateTime(timezone=True), nullable=False), + sa.ForeignKeyConstraint(['author_user_id'], ['users.id'], ), + sa.ForeignKeyConstraint(['changeset_id'], ['changesets.id'], ), + sa.ForeignKeyConstraint(['inventory_item_id'], ['inventory_items.id'], ondelete='CASCADE'), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('inventory_item_id', 'version_no', name='uq_inventory_item_versions_item_version') + ) + # ### end Alembic commands ### + + +def downgrade() -> None: + # ### commands auto generated by Alembic - please adjust! ### + op.drop_table('inventory_item_versions') + op.drop_table('validation_runs') + op.drop_index('uq_inventory_items_type_item_live', table_name='inventory_items', postgresql_where=sa.text('deleted_at IS NULL')) + op.drop_index('ix_inventory_items_type_position', table_name='inventory_items') + op.drop_table('inventory_items') + op.drop_index('ix_doc_versions_content_hash', table_name='doc_versions') + op.drop_table('doc_versions') + op.drop_table('change_items') + op.drop_table('inventory_types') + op.drop_table('changesets') + op.drop_index('ix_audit_log_created_at', table_name='audit_log') + op.drop_table('audit_log') + op.drop_table('users') + op.drop_table('schemas') + op.drop_table('documents') + op.drop_table('blobs') + # ### end Alembic commands ### diff --git a/server/app/config.py b/server/app/config.py index 4d60ba5..810c402 100644 --- a/server/app/config.py +++ b/server/app/config.py @@ -1,16 +1,32 @@ from __future__ import annotations +import os from functools import lru_cache from pathlib import Path +STORAGE_BACKENDS = {"file", "db"} + class Settings: - def __init__(self, repo_root: Path | None = None) -> None: + def __init__( + self, + repo_root: Path | None = None, + database_url: str | None = None, + storage_backend: str | None = None, + ) -> None: self.repo_root = repo_root or Path(__file__).resolve().parents[2] self.docs_extensions = {".md"} self.inventory_dir = self.repo_root / "40-inventory" self.generated_dir = self.repo_root / "60-generated" + backend = (storage_backend or os.environ.get("GNEXUS_BOOK_STORAGE_BACKEND", "file")).lower() + if backend not in STORAGE_BACKENDS: + raise ValueError( + f"GNEXUS_BOOK_STORAGE_BACKEND must be one of {sorted(STORAGE_BACKENDS)}, got {backend!r}" + ) + self.storage_backend = backend + self.database_url = database_url or os.environ.get("GNEXUS_BOOK_DATABASE_URL") + @lru_cache def get_settings() -> Settings: diff --git a/server/app/storage/__init__.py b/server/app/storage/__init__.py new file mode 100644 index 0000000..4c6f332 --- /dev/null +++ b/server/app/storage/__init__.py @@ -0,0 +1,11 @@ +"""PostgreSQL storage layer for Gnexus Book.""" + +from .db import Database, DatabaseError +from .factory import make_docs_repository, make_inventory_repository + +__all__ = [ + "Database", + "DatabaseError", + "make_docs_repository", + "make_inventory_repository", +] diff --git a/server/app/storage/db.py b/server/app/storage/db.py new file mode 100644 index 0000000..1db95f6 --- /dev/null +++ b/server/app/storage/db.py @@ -0,0 +1,41 @@ +from __future__ import annotations + +from functools import lru_cache + +from sqlalchemy import Engine, create_engine +from sqlalchemy.orm import Session, sessionmaker + +from ..config import Settings + + +class DatabaseError(RuntimeError): + pass + + +@lru_cache(maxsize=8) +def _engine_for_url(url: str) -> Engine: + return create_engine(url, pool_pre_ping=True, future=True) + + +class Database: + def __init__(self, url: str) -> None: + self.url = url + self.engine = _engine_for_url(url) + self.session_factory = sessionmaker(bind=self.engine, expire_on_commit=False, future=True) + + @classmethod + def from_settings(cls, settings: Settings) -> "Database": + if not settings.database_url: + raise DatabaseError( + "GNEXUS_BOOK_DATABASE_URL is required when storage backend is 'db'" + ) + return cls(settings.database_url) + + def session(self) -> Session: + return self.session_factory() + + def ping(self) -> None: + from sqlalchemy import text + + with self.engine.connect() as connection: + connection.execute(text("SELECT 1")) diff --git a/server/app/storage/db_repositories.py b/server/app/storage/db_repositories.py new file mode 100644 index 0000000..76d4997 --- /dev/null +++ b/server/app/storage/db_repositories.py @@ -0,0 +1,151 @@ +from __future__ import annotations + +from typing import Any + +import yaml +from sqlalchemy import and_, func, select +from sqlalchemy.orm import Session + +from ..docs_repository import RepositoryError +from ..inventory import InventoryError +from .db import Database +from .models import DocVersion, Document, InventoryItem, InventoryType + + +def _latest_doc_versions(): + return ( + select( + DocVersion.document_id.label("document_id"), + func.max(DocVersion.version_no).label("version_no"), + ) + .group_by(DocVersion.document_id) + .subquery() + ) + + +def _latest_doc_rows(session: Session): + latest = _latest_doc_versions() + return ( + select(Document, DocVersion) + .join(latest, latest.c.document_id == Document.id) + .join( + DocVersion, + and_( + DocVersion.document_id == Document.id, + DocVersion.version_no == latest.c.version_no, + ), + ) + .where(Document.deleted_at.is_(None)) + .order_by(Document.slug) + ) + + +class PostgresDocsRepository: + def __init__(self, database: Database) -> None: + self.database = database + + def list_docs(self) -> list[dict[str, object]]: + with self.database.session() as session: + rows = session.execute(_latest_doc_rows(session)).all() + return [ + { + "path": document.slug, + "title": document.title, + "frontmatter": dict(version.frontmatter or {}), + } + for document, version in rows + ] + + def read_doc(self, path: str) -> dict[str, object]: + with self.database.session() as session: + row = session.execute( + _latest_doc_rows(session) + .where(Document.slug == path) + .limit(1) + ).first() + if row is None: + raise RepositoryError("Document not found") + document, version = row + return { + "path": document.slug, + "frontmatter": dict(version.frontmatter or {}), + "body": version.body, + "raw": version.raw, + } + + def search(self, query: str) -> list[dict[str, object]]: + normalized = query.strip().lower() + if not normalized: + return [] + + results: list[dict[str, object]] = [] + with self.database.session() as session: + rows = session.execute(_latest_doc_rows(session)).all() + for document, version in rows: + matches = [] + for index, line in enumerate(version.raw.splitlines(), start=1): + if normalized in line.lower(): + matches.append({"line": index, "text": line.strip()}) + if len(matches) >= 5: + break + if matches: + results.append( + { + "path": document.slug, + "title": document.title, + "matches": matches, + } + ) + return results + + +class PostgresInventoryRepository: + def __init__(self, database: Database) -> None: + self.database = database + + def list_types(self) -> list[str]: + with self.database.session() as session: + return list(session.scalars(select(InventoryType.name).order_by(InventoryType.name))) + + def read_raw(self, inventory_type: str) -> str: + data = self.read_parsed(inventory_type) + return "---\n" + yaml.safe_dump(data, sort_keys=False, allow_unicode=True) + + def read_parsed(self, inventory_type: str) -> Any: + self._require_type(inventory_type) + with self.database.session() as session: + items = session.scalars( + select(InventoryItem) + .where( + InventoryItem.type == inventory_type, + InventoryItem.deleted_at.is_(None), + ) + .order_by(InventoryItem.position, InventoryItem.item_id) + ).all() + return [dict(item.data or {}) for item in items] + + def read_item(self, inventory_type: str, item_id: str) -> dict[str, Any]: + self._require_type(inventory_type) + with self.database.session() as session: + item = session.scalars( + select(InventoryItem) + .where( + InventoryItem.type == inventory_type, + InventoryItem.item_id == item_id, + InventoryItem.deleted_at.is_(None), + ) + .limit(1) + ).first() + if item is None: + raise InventoryError("Inventory item not found") + return dict(item.data or {}) + + def _require_type(self, inventory_type: str) -> None: + if "/" in inventory_type or "\\" in inventory_type or inventory_type.startswith("."): + raise InventoryError("Invalid inventory type") + with self.database.session() as session: + exists = session.scalar( + select(InventoryType.name).where(InventoryType.name == inventory_type) + ) + if exists is None: + raise InventoryError("Inventory type not found") diff --git a/server/app/storage/factory.py b/server/app/storage/factory.py new file mode 100644 index 0000000..cd7118d --- /dev/null +++ b/server/app/storage/factory.py @@ -0,0 +1,19 @@ +from __future__ import annotations + +from ..config import Settings +from ..docs_repository import DocsRepository +from ..inventory import InventoryRepository +from .db import Database +from .db_repositories import PostgresDocsRepository, PostgresInventoryRepository + + +def make_docs_repository(settings: Settings): + if settings.storage_backend == "db": + return PostgresDocsRepository(Database.from_settings(settings)) + return DocsRepository(settings) + + +def make_inventory_repository(settings: Settings): + if settings.storage_backend == "db": + return PostgresInventoryRepository(Database.from_settings(settings)) + return InventoryRepository(settings) diff --git a/server/app/storage/models.py b/server/app/storage/models.py new file mode 100644 index 0000000..4f8c68b --- /dev/null +++ b/server/app/storage/models.py @@ -0,0 +1,236 @@ +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) + 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 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") + 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 + ) + 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) diff --git a/server/app/storage/protocols.py b/server/app/storage/protocols.py new file mode 100644 index 0000000..5ddd074 --- /dev/null +++ b/server/app/storage/protocols.py @@ -0,0 +1,21 @@ +from __future__ import annotations + +from typing import Any, Protocol + + +class DocsRepositoryProtocol(Protocol): + def list_docs(self) -> list[dict[str, object]]: ... + + def read_doc(self, path: str) -> dict[str, object]: ... + + def search(self, query: str) -> list[dict[str, object]]: ... + + +class InventoryRepositoryProtocol(Protocol): + def list_types(self) -> list[str]: ... + + def read_raw(self, inventory_type: str) -> str: ... + + def read_parsed(self, inventory_type: str) -> Any: ... + + def read_item(self, inventory_type: str, item_id: str) -> dict[str, Any]: ... diff --git a/server/pyproject.toml b/server/pyproject.toml index a47d1b4..ce04fcb 100644 --- a/server/pyproject.toml +++ b/server/pyproject.toml @@ -9,6 +9,9 @@ "mcp>=1.27.0", "uvicorn[standard]>=0.30.0", "pyyaml>=6.0.0", + "sqlalchemy>=2.0.36", + "alembic>=1.14.0", + "psycopg[binary]>=3.2.0", ] [project.optional-dependencies]