diff --git a/server/alembic/versions/b59666c46655_data_ownership.py b/server/alembic/versions/b59666c46655_data_ownership.py new file mode 100644 index 0000000..dbffbb6 --- /dev/null +++ b/server/alembic/versions/b59666c46655_data_ownership.py @@ -0,0 +1,60 @@ +"""data ownership + +Revision ID: b59666c46655 +Revises: 9d8e9f47a7b0 +Create Date: 2026-11-20 15:54:42.093475 + +""" +from __future__ import annotations + +from typing import Sequence, Union + +from alembic import op +import sqlalchemy as sa + + +# revision identifiers, used by Alembic. +revision: str = 'b59666c46655' +down_revision: Union[str, None] = '9d8e9f47a7b0' +branch_labels: Union[str, Sequence[str], None] = None +depends_on: Union[str, Sequence[str], None] = None + + +def upgrade() -> None: + op.add_column('documents', sa.Column('owner_user_id', sa.UUID(), nullable=True)) + op.create_index(op.f('ix_documents_owner_user_id'), 'documents', ['owner_user_id'], unique=False) + op.create_foreign_key( + 'fk_documents_owner_user_id_users', + 'documents', + 'users', + ['owner_user_id'], + ['id'], + ondelete='SET NULL', + ) + op.add_column('inventory_items', sa.Column('owner_user_id', sa.UUID(), nullable=True)) + op.create_index(op.f('ix_inventory_items_owner_user_id'), 'inventory_items', ['owner_user_id'], unique=False) + op.create_foreign_key( + 'fk_inventory_items_owner_user_id_users', + 'inventory_items', + 'users', + ['owner_user_id'], + ['id'], + ondelete='SET NULL', + ) + + +def downgrade() -> None: + op.drop_constraint( + 'fk_inventory_items_owner_user_id_users', + 'inventory_items', + type_='foreignkey', + ) + op.drop_index(op.f('ix_inventory_items_owner_user_id'), table_name='inventory_items') + op.drop_column('inventory_items', 'owner_user_id') + op.drop_constraint( + 'fk_documents_owner_user_id_users', + 'documents', + type_='foreignkey', + ) + op.drop_index(op.f('ix_documents_owner_user_id'), table_name='documents') + op.drop_column('documents', 'owner_user_id') diff --git a/server/app/storage/cli.py b/server/app/storage/cli.py index fa1f337..9b43166 100644 --- a/server/app/storage/cli.py +++ b/server/app/storage/cli.py @@ -13,6 +13,7 @@ import_file_repository, verify_import, ) +from .ownership import assign_owner_from_json, ownership_counts from .parity import compare_repositories @@ -83,6 +84,26 @@ counts_parser = subparsers.add_parser("counts", help="Print database row counts") _add_common(counts_parser) + ownership_parser = subparsers.add_parser( + "ownership", help="Print owned/unowned document and inventory counts" + ) + _add_common(ownership_parser) + + assign_parser = subparsers.add_parser( + "assign-owner", help="Create/update an owner and assign unowned data" + ) + _add_common(assign_parser) + assign_parser.add_argument( + "--user-json", + required=True, + help="JSON file with sub, email, system_role, status and profile", + ) + assign_parser.add_argument( + "--all", + action="store_true", + help="Reassign all data, not only rows without an owner", + ) + return parser @@ -140,6 +161,22 @@ print(json.dumps(database_counts(database), indent=2)) return 0 + if args.command == "ownership": + print(json.dumps(ownership_counts(database), indent=2)) + return 0 + + if args.command == "assign-owner": + result = assign_owner_from_json( + database, args.user_json, all_data=args.all + ) + print( + json.dumps( + {"assignment": result, "ownership": ownership_counts(database)}, + indent=2, + ) + ) + return 0 + if args.command == "parity": result = compare_repositories(settings, database, queries=args.query) print(json.dumps(result, indent=2, ensure_ascii=False)) diff --git a/server/app/storage/models.py b/server/app/storage/models.py index e48abf8..c59031e 100644 --- a/server/app/storage/models.py +++ b/server/app/storage/models.py @@ -185,6 +185,12 @@ 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) + owner_user_id: Mapped[uuid.UUID | None] = mapped_column( + UUID(as_uuid=True), + ForeignKey("users.id", ondelete="SET NULL"), + nullable=True, + index=True, + ) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow) deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) @@ -260,6 +266,12 @@ 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) + owner_user_id: Mapped[uuid.UUID | None] = mapped_column( + UUID(as_uuid=True), + ForeignKey("users.id", ondelete="SET NULL"), + nullable=True, + index=True, + ) created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, default=utcnow) deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True)) diff --git a/server/app/storage/ownership.py b/server/app/storage/ownership.py new file mode 100644 index 0000000..a0402f7 --- /dev/null +++ b/server/app/storage/ownership.py @@ -0,0 +1,141 @@ +from __future__ import annotations + +import json +from pathlib import Path +from typing import Any + +from sqlalchemy import func, select, update + +from .db import Database +from .models import AuditLog, Document, InventoryItem, User, utcnow + + +def assign_owner( + database: Database, + *, + sub: str, + email: str | None = None, + display_name: str | None = None, + system_role: str | None = None, + status: str | None = None, + profile: dict[str, Any] | None = None, + all_data: bool = False, +) -> dict[str, Any]: + """Create or update the owner mirror and assign unowned data to it.""" + if not sub: + raise ValueError("sub is required") + + with database.session() as session: + with session.begin(): + user = session.scalars(select(User).where(User.sub == sub)).first() + if user is None: + user = User( + sub=sub, + email=email, + display_name=display_name or email or sub, + profile=dict(profile or {}), + system_role=system_role, + status=status, + ) + session.add(user) + else: + if email: + user.email = email + if display_name: + user.display_name = display_name + if profile is not None: + user.profile = dict(profile) + if system_role: + user.system_role = system_role + if status: + user.status = status + user.updated_at = utcnow() + session.flush() + + document_query = update(Document).values(owner_user_id=user.id) + inventory_query = update(InventoryItem).values(owner_user_id=user.id) + if not all_data: + document_query = document_query.where(Document.owner_user_id.is_(None)) + inventory_query = inventory_query.where( + InventoryItem.owner_user_id.is_(None) + ) + + documents = session.execute(document_query).rowcount or 0 + inventory_items = session.execute(inventory_query).rowcount or 0 + counts = { + "documents": documents, + "inventory_items": inventory_items, + } + + if documents or inventory_items: + session.add( + AuditLog( + actor_user_id=user.id, + actor_kind="user", + actor_name=user.display_name or user.email or user.sub, + via="cli", + action="ownership.assigned", + entity_type="repository", + entity_id=sub, + after=counts, + ) + ) + + return { + "user_id": str(user.id), + "sub": user.sub, + **counts, + } + + +def assign_owner_from_json( + database: Database, path: str | Path, all_data: bool = False +) -> dict[str, Any]: + payload = json.loads(Path(path).read_text(encoding="utf-8")) + profile = payload.get("profile") or {} + return assign_owner( + database, + sub=str(payload["sub"]), + email=payload.get("email"), + display_name=profile.get("display_name") + or profile.get("username") + or payload.get("email"), + system_role=payload.get("system_role"), + status=payload.get("status"), + profile=profile, + all_data=all_data, + ) + + +def ownership_counts(database: Database) -> dict[str, dict[str, int]]: + with database.session() as session: + return { + "documents": { + "owned": session.scalar( + select(func.count()) + .select_from(Document) + .where(Document.owner_user_id.is_not(None)) + ) + or 0, + "unowned": session.scalar( + select(func.count()) + .select_from(Document) + .where(Document.owner_user_id.is_(None)) + ) + or 0, + }, + "inventory_items": { + "owned": session.scalar( + select(func.count()) + .select_from(InventoryItem) + .where(InventoryItem.owner_user_id.is_not(None)) + ) + or 0, + "unowned": session.scalar( + select(func.count()) + .select_from(InventoryItem) + .where(InventoryItem.owner_user_id.is_(None)) + ) + or 0, + }, + } diff --git a/server/tests/test_ownership.py b/server/tests/test_ownership.py new file mode 100644 index 0000000..6a7286f --- /dev/null +++ b/server/tests/test_ownership.py @@ -0,0 +1,91 @@ +from __future__ import annotations + +import json + +import pytest +from sqlalchemy import select + +from app.config import Settings +from app.storage.db import Database +from app.storage.models import AuditLog, User +from app.storage.ownership import ( + assign_owner, + assign_owner_from_json, + ownership_counts, +) + + +def test_assign_owner_backfills_all_current_data( + imported: tuple[Settings, Database] +) -> None: + _, database = imported + + before = ownership_counts(database) + assert before["documents"]["unowned"] > 0 + assert before["inventory_items"]["unowned"] > 0 + + result = assign_owner( + database, + sub="1", + email="admin@example.test", + display_name="Admin", + system_role="superadmin", + status="active", + profile={"display_name": "Admin", "locale": "en"}, + ) + + after = ownership_counts(database) + assert result["documents"] == before["documents"]["unowned"] + assert result["inventory_items"] == before["inventory_items"]["unowned"] + assert after["documents"]["unowned"] == 0 + assert after["inventory_items"]["unowned"] == 0 + + with database.session() as session: + user = session.scalars(select(User).where(User.sub == "1")).first() + assert user is not None + assert user.system_role == "superadmin" + assert user.display_name == "Admin" + audit = session.scalars( + select(AuditLog).where(AuditLog.action == "ownership.assigned") + ).first() + assert audit is not None + assert audit.actor_user_id == user.id + + idempotent = assign_owner(database, sub="1") + assert idempotent["documents"] == 0 + assert idempotent["inventory_items"] == 0 + + reassigned = assign_owner(database, sub="1", all_data=True) + assert reassigned["documents"] == after["documents"]["owned"] + assert reassigned["inventory_items"] == after["inventory_items"]["owned"] + + +def test_assign_owner_from_json( + imported: tuple[Settings, Database], tmp_path +) -> None: + _, database = imported + payload = { + "sub": "1", + "email": "admin@example.test", + "system_role": "superadmin", + "status": "active", + "profile": {"display_name": "Admin", "locale": "en"}, + } + path = tmp_path / "owner.json" + path.write_text(json.dumps(payload), encoding="utf-8") + + result = assign_owner_from_json(database, path) + + assert result["sub"] == "1" + assert result["documents"] > 0 + with database.session() as session: + user = session.scalars(select(User).where(User.sub == "1")).first() + assert user is not None + assert user.profile["display_name"] == "Admin" + + +def test_assign_owner_requires_sub(imported: tuple[Settings, Database]) -> None: + _, database = imported + + with pytest.raises(ValueError, match="sub is required"): + assign_owner(database, sub="")