"""Add module-wise RBAC tables (role_master, module_master, role_module_permission, user_permission_override) Revision ID: b4e7f1c9a6d2 Revises: 9c1d4b7e2a3f Create Date: 2026-07-16 15:05:00.000000 """ import uuid from typing import Sequence, Union from datetime import datetime, timezone from alembic import op import sqlalchemy as sa from sqlalchemy.dialects import postgresql # revision identifiers, used by Alembic. revision: str = 'b4e7f1c9a6d2' down_revision: Union[str, None] = '9c1d4b7e2a3f' branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None def _uuid(): return str(uuid.uuid4()) def upgrade() -> None: now = datetime.now(timezone.utc) role_master = op.create_table( 'role_master', sa.Column('role_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('role_code', sa.String(50), nullable=False, unique=True), sa.Column('role_name', sa.String(150), nullable=False), sa.Column('description', sa.String(500)), sa.Column('is_system_role', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_at', sa.DateTime(timezone=True)), ) module_master = op.create_table( 'module_master', sa.Column('module_id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('module_code', sa.String(50), nullable=False, unique=True), sa.Column('module_name', sa.String(150), nullable=False), sa.Column('description', sa.String(500)), sa.Column('is_active', sa.Boolean, nullable=False, server_default=sa.true()), sa.Column('created_at', sa.DateTime(timezone=True)), ) role_module_permission = op.create_table( 'role_module_permission', sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('role_code', sa.String(50), nullable=False), sa.Column('module_code', sa.String(50), nullable=False), sa.Column('can_view', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('can_create', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('can_edit', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('can_delete', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('can_approve', sa.Boolean, nullable=False, server_default=sa.false()), sa.Column('updated_at', sa.DateTime(timezone=True)), sa.UniqueConstraint('role_code', 'module_code', name='uq_role_module'), ) op.create_table( 'user_permission_override', sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True), sa.Column('user_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('login_users.user_id'), nullable=False), sa.Column('module_code', sa.String(50), nullable=False), sa.Column('can_view', sa.Boolean), sa.Column('can_create', sa.Boolean), sa.Column('can_edit', sa.Boolean), sa.Column('can_delete', sa.Boolean), sa.Column('can_approve', sa.Boolean), sa.Column('granted_by', postgresql.UUID(as_uuid=True)), sa.Column('granted_at', sa.DateTime(timezone=True)), sa.Column('note', sa.String(255)), sa.UniqueConstraint('user_id', 'module_code', name='uq_user_module_override'), ) # ── Seed: modules ────────────────────────────────────────────── modules = [ ('SALES', 'Sales', 'Customers, quotes, enquiries, service orders, payments'), ('PROCUREMENT', 'Procurement', 'Purchase orders, GRN, debit notes'), ('VENDOR', 'Vendors', 'Vendor master data'), ('INVENTORY', 'Inventory', 'Stock balance, consumption, ledger, store requests'), ('RM_MASTER', 'Raw Materials', 'Raw material master data'), ('BOM', 'Bill of Materials', 'Products and bills of materials'), ('STATION', 'Stations', 'Production station master data'), ('STORE', 'Stores', 'Store / warehouse master data'), ('MAINTENANCE', 'Maintenance', 'Machine assets and preventive maintenance'), ('VENDOR_PORTAL_ADMIN', 'Vendor Portal Admin', 'Manage vendor portal access'), ('USER_MANAGEMENT', 'User Management', 'Roles, users, and permissions'), ] op.bulk_insert(module_master, [ {'module_id': _uuid(), 'module_code': code, 'module_name': name, 'description': desc, 'is_active': True, 'created_at': now} for code, name, desc in modules ]) # ── Seed: roles ───────────────────────────────────────────────── roles = [ ('ADMIN', 'System Administrator', True), ('SALES_MGR', 'Sales Manager', False), ('SALES_EXEC', 'Sales Executive', False), ('PROCUREMENT_MGR', 'Procurement Manager', False), ('PROCUREMENT_EXEC', 'Procurement Executive', False), ('STORE_MGR', 'Store Manager', False), ('STORE_KEEPER', 'Store Keeper', False), ('INVENTORY_MGR', 'Inventory Manager', False), ('FINANCE_MGR', 'Finance Head', False), ('FINANCE_EXEC', 'Finance Executive', False), ('PRODUCTION_MGR', 'Production Manager', False), ('ENGINEERING_HEAD', 'Engineering Head', False), ('MAINT_MGR', 'Maintenance Manager', False), ('VIEWER', 'Read-Only Viewer', False), ] op.bulk_insert(role_master, [ {'role_id': _uuid(), 'role_code': code, 'role_name': name, 'description': None, 'is_system_role': is_sys, 'is_active': True, 'created_at': now} for code, name, is_sys in roles ]) # ── Seed: default permission matrix (role_code, module_code, view, create, edit, delete, approve) ── all_modules = [m[0] for m in modules] matrix = [ # ADMIN gets everything explicitly, even though app code also treats ADMIN as an # automatic super-role — having real rows keeps the matrix visible/editable in pgAdmin. *[('ADMIN', m, True, True, True, True, True) for m in all_modules], ('SALES_MGR', 'SALES', True, True, True, True, True), ('SALES_EXEC', 'SALES', True, True, True, False, False), ('PROCUREMENT_MGR', 'PROCUREMENT', True, True, True, False, True), ('PROCUREMENT_MGR', 'VENDOR', True, True, True, False, False), ('PROCUREMENT_EXEC', 'PROCUREMENT', True, True, True, False, False), ('PROCUREMENT_EXEC', 'VENDOR', True, True, True, False, False), ('STORE_MGR', 'STORE', True, True, True, False, False), ('STORE_MGR', 'INVENTORY', True, True, True, False, True), ('STORE_MGR', 'RM_MASTER', True, False, False, False, False), ('STORE_MGR', 'SALES', True, False, True, False, False), # can issue spare parts orders ('STORE_MGR', 'PROCUREMENT', True, False, True, False, True), # verify + approve GRN ('STORE_KEEPER', 'STORE', True, False, False, False, False), ('STORE_KEEPER', 'INVENTORY', True, True, False, False, False), ('STORE_KEEPER', 'SALES', True, False, True, False, False), # can issue spare parts orders ('STORE_KEEPER', 'PROCUREMENT', True, True, True, False, False), # create + verify GRN ('INVENTORY_MGR', 'INVENTORY', True, True, True, False, True), ('INVENTORY_MGR', 'RM_MASTER', True, True, True, False, False), ('INVENTORY_MGR', 'PROCUREMENT', True, False, False, False, True), # approve GRN ('FINANCE_MGR', 'SALES', True, False, False, False, True), ('FINANCE_MGR', 'PROCUREMENT', True, False, False, False, False), ('FINANCE_EXEC', 'SALES', True, True, False, False, False), # can record payments, not confirm them ('PRODUCTION_MGR', 'SALES', True, False, True, False, False), ('PRODUCTION_MGR', 'STATION', True, True, True, False, False), ('PRODUCTION_MGR', 'BOM', True, False, False, False, False), ('ENGINEERING_HEAD', 'BOM', True, True, True, False, False), ('ENGINEERING_HEAD', 'RM_MASTER', True, False, False, False, False), ('MAINT_MGR', 'MAINTENANCE', True, True, True, True, False), # VIEWER: read-only everywhere except the two admin-only modules *[('VIEWER', m, True, False, False, False, False) for m in all_modules if m not in ('VENDOR_PORTAL_ADMIN', 'USER_MANAGEMENT')], ] op.bulk_insert(role_module_permission, [ { 'id': _uuid(), 'role_code': r, 'module_code': m, 'can_view': v, 'can_create': c, 'can_edit': e, 'can_delete': d, 'can_approve': a, 'updated_at': now, } for (r, m, v, c, e, d, a) in matrix ]) def downgrade() -> None: op.drop_table('user_permission_override') op.drop_table('role_module_permission') op.drop_table('module_master') op.drop_table('role_master')