Chris TonjesandClaude Opus 4.7
ba756586aa
Brings in a working generic-CRUD framework plus its dependencies from OWC
(../owc-system/src/owc/, snapshot 2026-04-20). Every vendored module carries
a header comment naming the source; the OWC → ypms rename is the only
mechanical change.
Vendored modules:
- framework/base.py TimestampMixin, SoftDeleteMixin, VersionedMixin
- framework/pagination.py Page[T] with to_dict()
- framework/version_history.py Snapshot table
- framework/crud.py CRUDService with audit emission
- framework/bulk.py BulkOps (BulkResult / BulkUpdateItem)
- framework/router.py make_router() factory (kept without
`from __future__ import annotations` for
FastAPI type introspection — see the module
docstring)
- audit/models.py AuditLog
- audit/service.py record()
- security/models.py Permission
- security/rbac.py can() + require_permission() dependency
(DOES_SYSTEM_ADMIN as the super-role)
- users/models.py User, Role, UserRole (YPMS adds
mfa_verified and assurance_level; drops
OWC-specific claimant_id)
- auth/deps.py get_current_user() placeholder — real JWT
verify lands in 0.3
YPMS-specific additions on top of the vendored framework:
- framework/pii_audit.py emit_pii_read() — NIST AU-14 / source RFP
C.5.6.9 #5. OWC doesn't need this because
its case-management surface is staff-only;
YPMS must audit reads of minors' PII, SSN,
banking, FERPA-covered OSSE records, and
CFSA foster/ward flags.
Bugfix vs. upstream OWC:
- framework/crud.py Pagination `next_cursor` was previously the
peek-ahead row's id, which combined with
`where id > cursor` on the next page
silently dropped one row per boundary.
Caught by test_list_pagination_forwards_cursor.
Fixed here; noted in the module header so
re-vendoring OWC surfaces it upstream.
Migrations:
- 0002_framework_tables.py Baseline for users, roles, user_roles,
permissions, audit_log, version_history.
Immutable once merged per CONTRIBUTING.md.
- migrations/env.py Imports the new model modules so
autogenerate compares against them.
Tests (26 passing, 93.10% coverage, floor 80%):
- tests/framework/test_crud.py CRUDService lifecycle + snapshots
+ audit emission (7 tests)
- tests/framework/test_bulk.py BulkOps happy paths + failure
reporting (4 tests)
- tests/framework/test_pagination.py Page.to_dict serializer contract
(2 tests)
- tests/framework/test_pii_audit.py emit_pii_read row shape,
anonymous case (2 tests)
- tests/security/test_rbac.py can() + admin bypass + role union
(4 tests)
- tests/test_readyz.py /readyz smoke (1 test)
- tests/test_logging.py sanitize_for_log contract (3 tests)
Green locally: ruff clean, mypy clean (with a targeted override on
framework/router.py and framework/crud.py for TypeVar limitations that
OWC also accepts).
Advances YPMS-POAM-040. Prepares milestone 0.3 (JWT auth) and 0.4 (RBAC
seed) to plug into an already-live framework.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
144 lines
5.7 KiB
Python
144 lines
5.7 KiB
Python
"""Framework tables — users, roles, user_roles, permissions, audit_log, version_history.
|
|
|
|
Revision ID: 0002_framework_tables
|
|
Revises: 0001_baseline
|
|
Create Date: 2026-09-30
|
|
|
|
Milestone 0.2 baseline. Creates the tables that every domain entity relies on
|
|
via the vendored OWC framework. Deliberately kept close to OWC's own baseline
|
|
so schema drift between the two systems stays reviewable.
|
|
|
|
Per repo rule (CONTRIBUTING.md): this file is immutable once merged. Every
|
|
subsequent change is a new revision, never an edit here.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
from collections.abc import Sequence
|
|
|
|
import sqlalchemy as sa
|
|
from alembic import op
|
|
|
|
revision: str = "0002_framework_tables"
|
|
down_revision: str | Sequence[str] | None = "0001_baseline"
|
|
branch_labels: str | Sequence[str] | None = None
|
|
depends_on: str | Sequence[str] | None = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
op.create_table(
|
|
"roles",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column("code", sa.String(length=64), nullable=False),
|
|
sa.Column("description", sa.String(length=255), nullable=True),
|
|
sa.UniqueConstraint("code", name="uq_roles_code"),
|
|
)
|
|
op.create_index("ix_roles_code", "roles", ["code"], unique=True)
|
|
|
|
op.create_table(
|
|
"users",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column("username", sa.String(length=64), nullable=False),
|
|
sa.Column("email", sa.String(length=255), nullable=True),
|
|
sa.Column("full_name", sa.String(length=255), nullable=True),
|
|
sa.Column("password_hash", sa.String(length=255), nullable=False),
|
|
sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("true")),
|
|
sa.Column("mfa_verified", sa.Boolean(), nullable=False, server_default=sa.text("false")),
|
|
sa.Column(
|
|
"assurance_level",
|
|
sa.String(length=32),
|
|
nullable=False,
|
|
server_default=sa.text("'standard'"),
|
|
),
|
|
sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
|
|
sa.UniqueConstraint("username", name="uq_users_username"),
|
|
sa.UniqueConstraint("email", name="uq_users_email"),
|
|
)
|
|
op.create_index("ix_users_username", "users", ["username"], unique=True)
|
|
op.create_index("ix_users_email", "users", ["email"], unique=True)
|
|
|
|
op.create_table(
|
|
"user_roles",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column(
|
|
"user_id", sa.Integer(),
|
|
sa.ForeignKey("users.id", ondelete="CASCADE"), nullable=False,
|
|
),
|
|
sa.Column(
|
|
"role_id", sa.Integer(),
|
|
sa.ForeignKey("roles.id", ondelete="CASCADE"), nullable=False,
|
|
),
|
|
sa.UniqueConstraint("user_id", "role_id", name="uq_user_role"),
|
|
)
|
|
op.create_index("ix_user_roles_user_id", "user_roles", ["user_id"])
|
|
op.create_index("ix_user_roles_role_id", "user_roles", ["role_id"])
|
|
|
|
op.create_table(
|
|
"permissions",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column(
|
|
"role_id", sa.Integer(),
|
|
sa.ForeignKey("roles.id", ondelete="CASCADE"), nullable=False,
|
|
),
|
|
sa.Column("entity", sa.String(length=64), nullable=False),
|
|
sa.Column("action", sa.String(length=64), nullable=False),
|
|
sa.Column("level", sa.String(length=4), nullable=False, server_default=sa.text("'C'")),
|
|
sa.UniqueConstraint(
|
|
"role_id", "entity", "action", name="uq_permission_role_entity_action"
|
|
),
|
|
)
|
|
op.create_index("ix_permissions_role_id", "permissions", ["role_id"])
|
|
op.create_index("ix_permissions_entity", "permissions", ["entity"])
|
|
op.create_index("ix_permissions_action", "permissions", ["action"])
|
|
|
|
op.create_table(
|
|
"audit_log",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column("ts", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column(
|
|
"actor_id", sa.Integer(),
|
|
sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True,
|
|
),
|
|
sa.Column("actor_username", sa.String(length=64), nullable=True),
|
|
sa.Column("action", sa.String(length=64), nullable=False),
|
|
sa.Column("entity", sa.String(length=64), nullable=False),
|
|
sa.Column("entity_id", sa.Integer(), nullable=True),
|
|
sa.Column("before", sa.JSON(), nullable=True),
|
|
sa.Column("after", sa.JSON(), nullable=True),
|
|
sa.Column("ip", sa.String(length=64), nullable=True),
|
|
)
|
|
op.create_index("ix_audit_log_ts", "audit_log", ["ts"])
|
|
op.create_index("ix_audit_log_actor_id", "audit_log", ["actor_id"])
|
|
op.create_index("ix_audit_log_action", "audit_log", ["action"])
|
|
op.create_index("ix_audit_log_entity", "audit_log", ["entity"])
|
|
op.create_index("ix_audit_log_entity_id", "audit_log", ["entity_id"])
|
|
|
|
op.create_table(
|
|
"version_history",
|
|
sa.Column("id", sa.Integer(), primary_key=True),
|
|
sa.Column("ts", sa.DateTime(timezone=True), nullable=False),
|
|
sa.Column("entity", sa.String(length=64), nullable=False),
|
|
sa.Column("entity_id", sa.Integer(), nullable=False),
|
|
sa.Column("version", sa.Integer(), nullable=False),
|
|
sa.Column(
|
|
"actor_id", sa.Integer(),
|
|
sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True,
|
|
),
|
|
sa.Column("snapshot", sa.JSON(), nullable=False),
|
|
)
|
|
op.create_index("ix_version_history_ts", "version_history", ["ts"])
|
|
op.create_index("ix_version_history_entity", "version_history", ["entity"])
|
|
op.create_index("ix_version_history_entity_id", "version_history", ["entity_id"])
|
|
|
|
|
|
def downgrade() -> None:
|
|
for name in (
|
|
"version_history",
|
|
"audit_log",
|
|
"permissions",
|
|
"user_roles",
|
|
"users",
|
|
"roles",
|
|
):
|
|
op.drop_table(name)
|