""" Seeds the full "Employee Leave Management System" application into an EXISTING tenant, following the Employee Leave Management System SRS (Employee_Leave_Management_Application_Requirement_Document.docx). This is a standalone script (same pattern as scripts/provision_tenant.py), not an API client: it inserts SQLAlchemy ORM rows directly through AsyncSessionLocal, bypassing the auth/JWT layer entirely, exactly the way provision_tenant.py bootstraps a tenant's first Administrator. Every id referenced across artifacts (a Master's own id used inside another Master's "reference" field_schema entry, a Form's id used in a Workflow step's target_id, a Workflow's id used in the Form's on_submit event, ...) is generated up front in Python with uuid.uuid4() so every cross-reference is resolvable on the very first insert, with no second "patch it up" pass needed. Idempotency: safe to re-run. Every top-level artifact is looked up by its natural key (tenant_id + name) first; if found, the whole script exits without creating a duplicate rather than trying to partially reconcile. Usage: backend/.venv/Scripts/python.exe backend/scripts/seed_leave_management_app.py """ import asyncio import sys import uuid from datetime import date, datetime, timedelta, timezone from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parent.parent)) from sqlalchemy import select # noqa: E402 from app.core.auth import hash_password # noqa: E402 from app.core.database import AsyncSessionLocal # noqa: E402 from app.models.automation import Task # noqa: E402 from app.models.app_bundle import App # noqa: E402 from app.models.identity import Role, Tenant, User, UserRole # noqa: E402 from app.models.master import Master, MasterRecord # noqa: E402 from app.models.navigation import Dashboard, DashboardRole, Menu, MenuRole, Widget # noqa: E402 from app.models.notification import Notification # noqa: E402 from app.models.reporting import Dataset # noqa: E402 from app.models.scheduling import ScheduledJob # noqa: E402 from app.models.workflow import Form, FormSubmission, StepHistory, Workflow, WorkflowInstance, WorkflowStepForm # noqa: E402 TENANT_ID = uuid.UUID("2f92482d-890b-4631-8320-661bcc1bc537") DEFAULT_PASSWORD = "Leave@123" APP_NAME = "Employee Leave Management System" def uid() -> uuid.UUID: return uuid.uuid4() def iso(d: date) -> str: return d.isoformat() def dt(y: int, m: int, d: int, hh: int = 9, mm: int = 0) -> datetime: return datetime(y, m, d, hh, mm, tzinfo=timezone.utc) async def main() -> None: async with AsyncSessionLocal() as db: async with db.begin(): tenant = await db.get(Tenant, TENANT_ID) if tenant is None: raise SystemExit(f"tenant {TENANT_ID} not found — create it first") existing_app = ( await db.execute(select(App).where(App.tenant_id == TENANT_ID, App.name == APP_NAME)) ).scalar_one_or_none() if existing_app is not None: print(f"'{APP_NAME}' already provisioned for this tenant (apps.id={existing_app.id}) — nothing to do.") print("Delete that 'apps' row first if you want to re-seed from scratch.") return admin_role = ( await db.execute( select(Role).where(Role.tenant_id == TENANT_ID, Role.name == "Administrator") ) ).scalar_one_or_none() admin_user = ( await db.execute(select(User).where(User.tenant_id == TENANT_ID, User.vendor_id.is_(None))) ).scalars().first() if admin_user is None: raise SystemExit("no internal user found on this tenant to attribute created_by to") admin_user_id = admin_user.id # --------------------------------------------------------------- # 1. Roles (FR: RBAC / SRS section 4 "User Roles") # --------------------------------------------------------------- role_ids = { "Employee": uid(), "Team Lead": uid(), "Department Manager": uid(), "HR Admin": uid(), } role_matrices = { "Employee": { "forms": ["view", "create"], "masters": ["view"], "dashboard": ["view"], "analytics": ["view"], }, "Team Lead": { "forms": ["view", "create"], "masters": ["view", "edit"], "dashboard": ["view"], "workflows": ["view"], "analytics": ["view"], "reports": ["view"], }, "Department Manager": { "forms": ["view", "create"], "masters": ["view", "edit"], "dashboard": ["view"], "workflows": ["view"], "analytics": ["view"], "reports": ["view"], }, "HR Admin": { "forms": ["view", "create", "edit", "delete", "publish"], "masters": ["view", "create", "edit", "delete"], "dashboard": ["view", "create", "edit"], "workflows": ["view", "edit", "publish"], "tasks": ["view", "create", "edit"], "analytics": ["view"], "reports": ["view"], "users_roles": ["view", "create", "edit"], "menus": ["view"], }, } for name, rid in role_ids.items(): db.add( Role( id=rid, tenant_id=TENANT_ID, name=name, is_system_role=False, scope="internal", action_matrix=role_matrices[name], ) ) # --------------------------------------------------------------- # 2. Users (default password DEFAULT_PASSWORD for every new user) # --------------------------------------------------------------- people = [ # key, name, email, role ("meera", "Meera Nair", "meera.nair@flowfabric-leave.demo", "HR Admin"), ("priya", "Priya Menon", "priya.menon@flowfabric-leave.demo", "Department Manager"), ("rahul", "Rahul Verma", "rahul.verma@flowfabric-leave.demo", "Team Lead"), ("arun", "Arun Kumar", "arun.kumar@flowfabric-leave.demo", "Employee"), ("anu", "Anu Thomas", "anu.thomas@flowfabric-leave.demo", "Employee"), ("ravi", "Ravi Das", "ravi.das@flowfabric-leave.demo", "Employee"), ("divya", "Divya Iyer", "divya.iyer@flowfabric-leave.demo", "Employee"), ("karthik", "Karthik Raj", "karthik.raj@flowfabric-leave.demo", "Employee"), ] user_ids: dict[str, uuid.UUID] = {} password_hash = hash_password(DEFAULT_PASSWORD) for key, name, email, role_name in people: new_id = uid() user_ids[key] = new_id db.add( User( id=new_id, tenant_id=TENANT_ID, name=name, email=email, password_hash=password_hash, status="active", ) ) db.add(UserRole(user_id=new_id, role_id=role_ids[role_name])) await db.flush() # --------------------------------------------------------------- # 3. Masters: Department, Leave Type, Employee, Leave Balance # --------------------------------------------------------------- department_master_id = uid() employee_master_id = uid() leave_type_master_id = uid() leave_balance_master_id = uid() db.add( Master( id=department_master_id, tenant_id=TENANT_ID, name="Departments", created_by=admin_user_id, field_schema=[ {"name": "code", "type": "varchar", "required": True, "max_length": 20}, {"name": "name", "type": "varchar", "required": True, "max_length": 100}, { "name": "manager", "type": "reference", "required": False, "reference_master_id": str(employee_master_id), "reference_display_field": "full_name", }, { "name": "status", "type": "single_select", "required": True, "options": ["Active", "Inactive"], }, ], ) ) db.add( Master( id=employee_master_id, tenant_id=TENANT_ID, name="Employees", created_by=admin_user_id, field_schema=[ {"name": "employee_code", "type": "varchar", "required": True, "max_length": 20}, {"name": "first_name", "type": "varchar", "required": True, "max_length": 100}, {"name": "last_name", "type": "varchar", "required": True, "max_length": 100}, {"name": "full_name", "type": "varchar", "required": True, "max_length": 200}, {"name": "email", "type": "varchar", "required": True, "max_length": 200}, { "name": "department", "type": "reference", "required": True, "reference_master_id": str(department_master_id), "reference_display_field": "name", }, { "name": "manager", "type": "reference", "required": False, "reference_master_id": str(employee_master_id), "reference_display_field": "full_name", }, {"name": "designation", "type": "varchar", "required": True, "max_length": 100}, {"name": "joining_date", "type": "date", "required": True}, { "name": "status", "type": "single_select", "required": True, "options": ["Active", "Inactive"], }, { "name": "user_account", "type": "user", "required": False, "user_type": "internal", }, ], ) ) db.add( Master( id=leave_type_master_id, tenant_id=TENANT_ID, name="Leave Types", created_by=admin_user_id, field_schema=[ {"name": "code", "type": "varchar", "required": True, "max_length": 20}, {"name": "name", "type": "varchar", "required": True, "max_length": 100}, {"name": "entitlement_days", "type": "number", "required": True}, {"name": "carry_forward_days", "type": "number", "required": True}, {"name": "eligibility_rule", "type": "text", "required": False}, { "name": "status", "type": "single_select", "required": True, "options": ["Active", "Inactive"], }, ], ) ) db.add( Master( id=leave_balance_master_id, tenant_id=TENANT_ID, name="Leave Balances", created_by=admin_user_id, field_schema=[ { "name": "employee", "type": "reference", "required": True, "reference_master_id": str(employee_master_id), "reference_display_field": "full_name", }, { "name": "leave_type", "type": "reference", "required": True, "reference_master_id": str(leave_type_master_id), "reference_display_field": "name", }, {"name": "year", "type": "integer", "required": True}, {"name": "opening", "type": "number", "required": True}, {"name": "accrued", "type": "number", "required": True}, {"name": "used", "type": "number", "required": True}, { "name": "available", "type": "formula", "formula_expression": "{opening} + {accrued} - {used}", }, ], ) ) await db.flush() # --------------------------------------------------------------- # 4. Department records # --------------------------------------------------------------- dept_ids = {"finance": uid(), "it": uid(), "hr": uid(), "ops": uid()} # Employee record ids reserved up front so Department.manager and # Employee.manager can point at each other on first insert. emp_ids = {k: uid() for k in ("arun", "anu", "ravi", "divya", "karthik", "meera", "priya", "rahul")} dept_rows = [ ("finance", "FIN", "Finance", emp_ids["priya"]), ("it", "IT", "Information Technology", emp_ids["rahul"]), ("hr", "HR", "Human Resources", emp_ids["meera"]), ("ops", "OPS", "Operations", None), ] for key, code, name, manager_emp_id in dept_rows: db.add( MasterRecord( id=dept_ids[key], tenant_id=TENANT_ID, master_id=department_master_id, created_by=admin_user_id, field_values={ "code": code, "name": name, "manager": str(manager_emp_id) if manager_emp_id else None, "status": "Active", }, ) ) # --------------------------------------------------------------- # 5. Employee records (one per non-Administrator user) # --------------------------------------------------------------- employee_rows = [ # key, code, first, last, dept, manager_key, designation, joined, user_key ("arun", "E001", "Arun", "Kumar", "finance", "priya", "Financial Analyst", date(2022, 4, 11), "arun"), ("anu", "E002", "Anu", "Thomas", "it", "rahul", "Software Engineer", date(2021, 8, 19), "anu"), ("ravi", "E003", "Ravi", "Das", "hr", "meera", "HR Executive", date(2023, 1, 5), "ravi"), ("divya", "E004", "Divya", "Iyer", "ops", None, "Operations Associate", date(2022, 11, 30), "divya"), ("karthik", "E005", "Karthik", "Raj", "it", "rahul", "QA Engineer", date(2023, 6, 14), "karthik"), ("meera", "E006", "Meera", "Nair", "hr", None, "HR Administrator", date(2019, 2, 1), "meera"), ("priya", "E007", "Priya", "Menon", "finance", None, "Department Manager - Finance", date(2018, 7, 23), "priya"), ("rahul", "E008", "Rahul", "Verma", "it", "priya", "Team Lead - IT", date(2020, 3, 10), "rahul"), ] email_by_key = {row[0]: next(p[2] for p in people if p[0] == row[0]) for row in employee_rows} for key, code, first, last, dept_key, manager_key, designation, joined, user_key in employee_rows: full_name = f"{first} {last}" db.add( MasterRecord( id=emp_ids[key], tenant_id=TENANT_ID, master_id=employee_master_id, created_by=admin_user_id, field_values={ "employee_code": code, "first_name": first, "last_name": last, "full_name": full_name, "email": email_by_key[key], "department": str(dept_ids[dept_key]), "manager": str(emp_ids[manager_key]) if manager_key else None, "designation": designation, "joining_date": iso(joined), "status": "Active", "user_account": str(user_ids[user_key]), }, ) ) full_name_by_key = {row[0]: f"{row[2]} {row[3]}" for row in employee_rows} # --------------------------------------------------------------- # 6. Leave Type records # --------------------------------------------------------------- leave_type_ids = {"annual": uid(), "sick": uid(), "casual": uid(), "maternity": uid()} leave_type_rows = [ ("annual", "AL", "Annual Leave", 18, 5, "All confirmed employees"), ("sick", "SL", "Sick Leave", 12, 0, "All employees"), ("casual", "CL", "Casual Leave", 8, 0, "All employees"), ("maternity", "ML", "Maternity / Paternity Leave", 180, 0, "As per statutory policy"), ] for key, code, name, entitlement, carry_fwd, eligibility in leave_type_rows: db.add( MasterRecord( id=leave_type_ids[key], tenant_id=TENANT_ID, master_id=leave_type_master_id, created_by=admin_user_id, field_values={ "code": code, "name": name, "entitlement_days": entitlement, "carry_forward_days": carry_fwd, "eligibility_rule": eligibility, "status": "Active", }, ) ) leave_type_name = {row[0]: row[2] for row in leave_type_rows} leave_type_entitlement = {row[0]: row[3] for row in leave_type_rows} # --------------------------------------------------------------- # 7. Form: Leave Application (SCR-11) — created before the # Workflow so the Workflow's steps can target this form, and # with events wired to the Workflow whose id we already know # (pre-generated) even though the Workflow row is inserted # right afterward. # --------------------------------------------------------------- form_id = uid() workflow_id = uid() db.add( Form( form_id=form_id, version=1, tenant_id=TENANT_ID, name="Leave Application", created_by=admin_user_id, status="published", is_current=True, field_definitions=[ {"name": "application_no", "type": "text", "label": "Application No.", "required": False}, { "name": "employee", "type": "lookup", "label": "Employee", "required": True, "master_id": str(employee_master_id), "master_display_field": "full_name", }, { "name": "leave_type", "type": "dropdown", "label": "Leave Type", "required": True, "master_id": str(leave_type_master_id), "master_display_field": "name", }, {"name": "from_date", "type": "date", "label": "From Date", "required": True}, {"name": "to_date", "type": "date", "label": "To Date", "required": True}, {"name": "half_day", "type": "checkbox", "label": "Half Day", "required": False}, {"name": "days", "type": "number", "label": "Duration (Days)", "required": True}, {"name": "reason", "type": "textarea", "label": "Reason", "required": True, "rows": 4}, {"name": "attachment", "type": "file_upload", "label": "Attachment", "required": False, "accept": "*/*"}, { "name": "status", "type": "dropdown", "label": "Status", "required": True, "options": ["Draft", "Submitted", "Under Review", "Sent Back", "Approved", "Rejected", "Cancelled"], }, { "name": "current_approver", "type": "user", "label": "Current Approver", "required": False, "user_type": "internal", }, {"name": "approver_comment", "type": "textarea", "label": "Approver Comment", "required": False, "rows": 3}, ], events=[ { "trigger": "on_submit", "action_type": "trigger_workflow", "workflow_id": str(workflow_id), } ], ) ) # --------------------------------------------------------------- # 8. Workflow: Leave Approval Workflow # Employee -> Manager/Team Lead -> HR -> Approved/Rejected # (SRS section 9) # --------------------------------------------------------------- db.add( Workflow( workflow_id=workflow_id, version=1, tenant_id=TENANT_ID, name="Leave Approval Workflow", created_by=admin_user_id, status="published", is_current=True, step_definitions=[ { "key": "start", "type": "start", "label": "Leave Application Submitted", "next_steps": [{"to": "manager_review"}], "target_type": "form", "target_id": str(form_id), "position_x": 40, "position_y": 200, }, { "key": "manager_review", "type": "approval", "label": "Manager / Team Lead Review", "approver_roles": [str(role_ids["Team Lead"]), str(role_ids["Department Manager"])], "sla_hours": 48, "next_steps": [ {"to": "hr_review", "condition": {"driver_field": "decision", "operator": "equals", "value": "approve"}}, {"to": "rejected_end", "condition": {"driver_field": "decision", "operator": "equals", "value": "reject"}}, {"to": "sent_back_end", "condition": {"driver_field": "decision", "operator": "equals", "value": "send_back"}}, ], "target_type": "form", "target_id": str(form_id), "position_x": 320, "position_y": 200, }, { "key": "hr_review", "type": "approval", "label": "HR Review", "approver_roles": [str(role_ids["HR Admin"])], "sla_hours": 48, "next_steps": [ {"to": "approved_end", "condition": {"driver_field": "decision", "operator": "equals", "value": "approve"}}, {"to": "rejected_end", "condition": {"driver_field": "decision", "operator": "equals", "value": "reject"}}, ], "target_type": "form", "target_id": str(form_id), "position_x": 600, "position_y": 200, }, {"key": "approved_end", "type": "end", "label": "Approved", "position_x": 900, "position_y": 60}, {"key": "rejected_end", "type": "end", "label": "Rejected", "position_x": 900, "position_y": 200}, {"key": "sent_back_end", "type": "end", "label": "Sent Back for Correction", "position_x": 900, "position_y": 340}, ], ) ) await db.flush() db.add( WorkflowStepForm( workflow_id=workflow_id, workflow_version=1, step_key="start", form_id=form_id, form_version=1, ) ) # --------------------------------------------------------------- # 9. Ten dummy Leave Applications (form_submissions), each paired # with a workflow_instance + step_history where the request has # actually moved past Draft (SRS section 10, Status Model). # --------------------------------------------------------------- def leave_payload(app_no, emp_key, lt_key, frm, to, days, half_day, reason, status, current_approver_key, approver_comment): return { "application_no": app_no, "employee": str(emp_ids[emp_key]), "leave_type": str(leave_type_ids[lt_key]), "from_date": iso(frm), "to_date": iso(to), "half_day": half_day, "days": days, "reason": reason, "attachment": None, "status": status, "current_approver": str(user_ids[current_approver_key]) if current_approver_key else None, "approver_comment": approver_comment, } applications = [ dict( app_no="LV-000101", emp="arun", lt="annual", frm=date(2026, 9, 15), to=date(2026, 9, 17), days=3, reason="Family function", status="Approved", submitted_at=dt(2026, 9, 11), manager_actor="priya", manager_action="approved", manager_comment="Approved — coverage arranged.", manager_at=dt(2026, 9, 12), hr_actor="meera", hr_action="approved", hr_comment="Balance sufficient. Approved.", hr_at=dt(2026, 9, 13), ), dict( app_no="LV-000102", emp="anu", lt="sick", frm=date(2026, 9, 16), to=date(2026, 9, 16), days=1, reason="Fever, advised rest by physician", status="Submitted", submitted_at=dt(2026, 9, 14), manager_actor=None, pending_approver="rahul", ), dict( app_no="LV-000103", emp="ravi", lt="casual", frm=date(2026, 9, 20), to=date(2026, 9, 21), days=2, reason="Personal work", status="Under Review", submitted_at=dt(2026, 9, 12), manager_actor="priya", manager_action="approved", manager_comment="Approved, forwarding to HR.", manager_at=dt(2026, 9, 13), hr_actor=None, pending_approver="meera", ), dict( app_no="LV-000104", emp="divya", lt="annual", frm=date(2026, 10, 1), to=date(2026, 10, 5), days=5, reason="Family travel", status="Approved", submitted_at=dt(2026, 9, 4), manager_actor="priya", manager_action="approved", manager_comment="Approved.", manager_at=dt(2026, 9, 5), hr_actor="meera", hr_action="approved", hr_comment="Approved.", hr_at=dt(2026, 9, 6), ), dict( app_no="LV-000105", emp="karthik", lt="sick", frm=date(2026, 9, 8), to=date(2026, 9, 9), days=2, reason="Viral fever", status="Rejected", submitted_at=dt(2026, 9, 6), manager_actor="rahul", manager_action="rejected", manager_comment="Critical release week, coverage unavailable — please resubmit for next week.", manager_at=dt(2026, 9, 7), hr_actor=None, ), dict( app_no="LV-000106", emp="arun", lt="casual", frm=date(2026, 8, 14), to=date(2026, 8, 14), days=1, reason="Personal errand", status="Approved", submitted_at=dt(2026, 8, 11), manager_actor="priya", manager_action="approved", manager_comment="Approved.", manager_at=dt(2026, 8, 12), hr_actor="meera", hr_action="approved", hr_comment="Approved.", hr_at=dt(2026, 8, 13), ), dict( app_no="LV-000107", emp="anu", lt="annual", frm=date(2026, 12, 22), to=date(2026, 12, 31), days=8, reason="Year-end family trip", status="Draft", submitted_at=None, ), dict( app_no="LV-000108", emp="ravi", lt="sick", frm=date(2026, 9, 5), to=date(2026, 9, 5), days=1, reason="Not feeling well", status="Sent Back", submitted_at=dt(2026, 9, 5), manager_actor="priya", manager_action="sent_back", manager_comment="Please attach a medical certificate before resubmitting.", manager_at=dt(2026, 9, 5, 15, 0), hr_actor=None, ), dict( app_no="LV-000109", emp="divya", lt="casual", frm=date(2026, 9, 25), to=date(2026, 9, 26), days=2, reason="Personal work", status="Submitted", submitted_at=dt(2026, 9, 14), manager_actor=None, pending_approver="priya", ), dict( app_no="LV-000110", emp="karthik", lt="maternity", frm=date(2026, 11, 1), to=date(2027, 1, 30), days=91, reason="Paternity leave", status="Approved", submitted_at=dt(2026, 8, 30), manager_actor="rahul", manager_action="approved", manager_comment="Approved.", manager_at=dt(2026, 9, 1), hr_actor="meera", hr_action="approved", hr_comment="Approved as per policy.", hr_at=dt(2026, 9, 2), ), ] step_key_by_status = { "Approved": "approved_end", "Rejected": "rejected_end", "Sent Back": "sent_back_end", "Under Review": "hr_review", "Submitted": "manager_review", } terminal_statuses = {"Approved", "Rejected", "Sent Back"} for app in applications: submission_id = uid() instance_id = None if app["status"] != "Draft": instance_id = uid() current_step = step_key_by_status[app["status"]] is_terminal = app["status"] in terminal_statuses instance = WorkflowInstance( id=instance_id, tenant_id=TENANT_ID, workflow_id=workflow_id, workflow_version=1, current_step=current_step, status="completed" if is_terminal else "in_progress", sla_due_at=app["submitted_at"] + timedelta(hours=48), started_by=user_ids[app["emp"]], started_at=app["submitted_at"], completed_at=(app.get("hr_at") or app.get("manager_at")) if is_terminal else None, context={ "application_no": app["app_no"], "employee_name": full_name_by_key[app["emp"]], "leave_type": leave_type_name[app["lt"]], "days": app["days"], }, ) db.add(instance) await db.flush() db.add( StepHistory( tenant_id=TENANT_ID, instance_id=instance_id, step="start", actor_id=user_ids[app["emp"]], action="submitted", comment=None, occurred_at=app["submitted_at"], ) ) if app.get("manager_actor"): db.add( StepHistory( tenant_id=TENANT_ID, instance_id=instance_id, step="manager_review", actor_id=user_ids[app["manager_actor"]], action=app["manager_action"], comment=app["manager_comment"], occurred_at=app["manager_at"], ) ) if app.get("hr_actor"): db.add( StepHistory( tenant_id=TENANT_ID, instance_id=instance_id, step="hr_review", actor_id=user_ids[app["hr_actor"]], action=app["hr_action"], comment=app["hr_comment"], occurred_at=app["hr_at"], ) ) # Flush now so the workflow_instances row physically # exists before the FormSubmission FK insert below — # SQLAlchemy's unit-of-work only infers cross-table # insert ordering from declared relationship()s, and # these two models are linked by a plain FK column only. await db.flush() approver_key = app.get("pending_approver") approver_comment = None if app["status"] == "Sent Back": approver_comment = app["manager_comment"] elif app["status"] == "Rejected": approver_comment = app["manager_comment"] db.add( FormSubmission( id=submission_id, tenant_id=TENANT_ID, form_id=form_id, form_version=1, workflow_instance_id=instance_id, submitted_by=user_ids[app["emp"]], updated_by=user_ids[app.get("hr_actor") or app.get("manager_actor") or app["emp"]], submitted_at=app["submitted_at"] or dt(2026, 9, 14), field_values=leave_payload( app["app_no"], app["emp"], app["lt"], app["frm"], app["to"], app["days"], False, app["reason"], app["status"], approver_key, approver_comment, ), ) ) # --------------------------------------------------------------- # 10. Leave Balances (2026, per employee x main leave types) # --------------------------------------------------------------- approved_days = { ("arun", "annual"): 3, ("arun", "casual"): 1, ("divya", "annual"): 5, ("karthik", "maternity"): 91, } for emp_key in emp_ids: for lt_key in ("annual", "sick", "casual"): opening = leave_type_entitlement[lt_key] used = approved_days.get((emp_key, lt_key), 0) db.add( MasterRecord( tenant_id=TENANT_ID, master_id=leave_balance_master_id, created_by=admin_user_id, field_values={ "employee": str(emp_ids[emp_key]), "leave_type": str(leave_type_ids[lt_key]), "year": 2026, "opening": opening, "accrued": 0, "used": used, "available": opening - used, }, ) ) # Karthik also carries a Maternity/Paternity balance since he used it. m_opening = leave_type_entitlement["maternity"] m_used = approved_days.get(("karthik", "maternity"), 0) db.add( MasterRecord( tenant_id=TENANT_ID, master_id=leave_balance_master_id, created_by=admin_user_id, field_values={ "employee": str(emp_ids["karthik"]), "leave_type": str(leave_type_ids["maternity"]), "year": 2026, "opening": m_opening, "accrued": 0, "used": m_used, "available": m_opening - m_used, }, ) ) await db.flush() # --------------------------------------------------------------- # 11. Datasets (saved filtered/grouped queries — Dashboard widgets # can only read a whole Master/Form or one of these, never an # ad hoc filter, so every "Pending"/"Approved"/"by type" KPI # below needs its own saved Dataset). # --------------------------------------------------------------- ds_pending = uid() ds_approved = uid() ds_rejected = uid() ds_by_leave_type = uid() ds_by_status = uid() ds_employees_by_dept = uid() db.add( Dataset( id=ds_pending, tenant_id=TENANT_ID, created_by=admin_user_id, name="Pending Leave Requests", description="Leave applications awaiting Manager or HR action.", source_type="form", source_id=form_id, filters=[{"field": "status", "operator": "in", "value": ["Submitted", "Under Review"]}], ) ) db.add( Dataset( id=ds_approved, tenant_id=TENANT_ID, created_by=admin_user_id, name="Approved Leave Requests", description="Leave applications fully approved.", source_type="form", source_id=form_id, filters=[{"field": "status", "operator": "equals", "value": "Approved"}], ) ) db.add( Dataset( id=ds_rejected, tenant_id=TENANT_ID, created_by=admin_user_id, name="Rejected Leave Requests", description="Leave applications rejected by an approver.", source_type="form", source_id=form_id, filters=[{"field": "status", "operator": "equals", "value": "Rejected"}], ) ) db.add( Dataset( id=ds_by_leave_type, tenant_id=TENANT_ID, created_by=admin_user_id, name="Leave Requests by Type", description="Count of leave applications grouped by leave type.", source_type="form", source_id=form_id, group_by="leave_type", aggregate_fn="count", ) ) db.add( Dataset( id=ds_by_status, tenant_id=TENANT_ID, created_by=admin_user_id, name="Leave Requests by Status", description="Count of leave applications grouped by status.", source_type="form", source_id=form_id, group_by="status", aggregate_fn="count", ) ) db.add( Dataset( id=ds_employees_by_dept, tenant_id=TENANT_ID, created_by=admin_user_id, name="Employees by Department", description="Headcount grouped by department.", source_type="master", source_id=employee_master_id, group_by="department", aggregate_fn="count", ) ) # --------------------------------------------------------------- # 12. Dashboards + widgets (SRS section 11, mockups) # --------------------------------------------------------------- dash_employee = uid() dash_manager = uid() dash_hr = uid() db.add(Dashboard(id=dash_employee, tenant_id=TENANT_ID, name="Employee Leave Dashboard", status="published", created_by=admin_user_id)) db.add(Dashboard(id=dash_manager, tenant_id=TENANT_ID, name="Manager Approval Dashboard", status="published", created_by=admin_user_id)) db.add(Dashboard(id=dash_hr, tenant_id=TENANT_ID, name="HR Admin Dashboard", status="published", created_by=admin_user_id)) await db.flush() db.add(DashboardRole(dashboard_id=dash_employee, role_id=role_ids["Employee"])) db.add(DashboardRole(dashboard_id=dash_employee, role_id=admin_role.id)) db.add(DashboardRole(dashboard_id=dash_manager, role_id=role_ids["Team Lead"])) db.add(DashboardRole(dashboard_id=dash_manager, role_id=role_ids["Department Manager"])) db.add(DashboardRole(dashboard_id=dash_manager, role_id=admin_role.id)) db.add(DashboardRole(dashboard_id=dash_hr, role_id=role_ids["HR Admin"])) db.add(DashboardRole(dashboard_id=dash_hr, role_id=admin_role.id)) def widget(dashboard_id, wtype, data_source, color, sort_order, title, columns=None, icon=None): config = {"title": title} if columns: config["columns"] = columns if icon: config["icon"] = icon return Widget( tenant_id=TENANT_ID, dashboard_id=dashboard_id, type=wtype, data_source=data_source, size="medium", color=color, refresh_interval="hourly", sort_order=sort_order, config=config, ) db.add_all([ widget(dash_employee, "kpi_card", f"master:{leave_type_master_id}", "blue", 0, "Leave Types Available", icon="Calendar"), widget(dash_employee, "kpi_card", f"dataset:{ds_pending}", "amber", 1, "Pending Leave Requests", icon="Clock"), widget(dash_employee, "kpi_card", f"master:{leave_balance_master_id}", "green", 2, "Leave Balance Records", icon="Wallet"), widget(dash_employee, "table", f"form:{form_id}", "slate", 3, "Recent Leave Applications", columns=["application_no", "employee", "leave_type", "from_date", "to_date", "days", "status"]), ]) db.add_all([ widget(dash_manager, "kpi_card", f"master:{employee_master_id}", "blue", 0, "Total Employees", icon="Users"), widget(dash_manager, "kpi_card", f"dataset:{ds_pending}", "amber", 1, "Pending Approvals", icon="Clock"), widget(dash_manager, "kpi_card", f"dataset:{ds_approved}", "green", 2, "Approved Requests", icon="CheckCircle"), widget(dash_manager, "bar_chart", f"dataset:{ds_employees_by_dept}", "indigo", 3, "Employees by Department"), widget(dash_manager, "table", f"form:{form_id}", "slate", 4, "Team Leave Requests", columns=["employee", "leave_type", "from_date", "to_date", "days", "status"]), ]) db.add_all([ widget(dash_hr, "kpi_card", f"master:{employee_master_id}", "blue", 0, "Total Employees", icon="Users"), widget(dash_hr, "kpi_card", f"dataset:{ds_pending}", "amber", 1, "Pending Approvals", icon="Clock"), widget(dash_hr, "kpi_card", f"dataset:{ds_approved}", "green", 2, "Approved", icon="CheckCircle"), widget(dash_hr, "kpi_card", f"dataset:{ds_rejected}", "red", 3, "Rejected", icon="XCircle"), widget(dash_hr, "bar_chart", f"dataset:{ds_by_leave_type}", "indigo", 4, "Requests by Leave Type"), widget(dash_hr, "pie_chart", f"dataset:{ds_by_status}", "violet", 5, "Requests by Status"), widget(dash_hr, "table", f"master:{leave_balance_master_id}", "slate", 6, "Leave Balances", columns=["employee", "leave_type", "year", "available"]), ]) # --------------------------------------------------------------- # 13. Tasks (automation) + Scheduled Jobs # --------------------------------------------------------------- task_notify = uid() task_balance_review = uid() db.add( Task( id=task_notify, tenant_id=TENANT_ID, name="Leave Status Notification", status="published", created_by=admin_user_id, nodes=[ {"key": "start", "type": "start", "label": "Start", "next_nodes": [{"to": "notify"}], "position_x": 40, "position_y": 120}, { "key": "notify", "type": "create_notification", "label": "Notify", "input_params": [ {"name": "employee_name", "type": "string", "source_field": "employee_name", "required": True}, {"name": "leave_status", "type": "string", "source_field": "leave_status", "required": True}, ], "subject": "Leave Request Update", "body": "The leave request for {employee_name} is now {leave_status}.", "next_nodes": [], "position_x": 320, "position_y": 120, }, ], ) ) db.add( Task( id=task_balance_review, tenant_id=TENANT_ID, name="Leave Balance Review", status="published", created_by=admin_user_id, nodes=[ {"key": "start", "type": "start", "label": "Start", "next_nodes": [{"to": "list_balances"}], "position_x": 40, "position_y": 120}, { "key": "list_balances", "type": "data_list", "label": "List Leave Balances", "master_id": str(leave_balance_master_id), "output_params": [{"name": "balances", "type": "list"}], "next_nodes": [{"to": "notify"}], "position_x": 320, "position_y": 120, }, { "key": "notify", "type": "create_notification", "label": "Notify HR", "subject": "Leave Balance Review", "body": "Scheduled review complete — check the Leave Balances datasource for employees nearing exhaustion.", "next_nodes": [], "position_x": 600, "position_y": 120, }, ], ) ) db.add( ScheduledJob( tenant_id=TENANT_ID, name="Leave Approval SLA Monitor", job_type="sla_overdue_scan", frequency="daily", status="active", created_by=admin_user_id, ) ) db.add( ScheduledJob( tenant_id=TENANT_ID, name="Monthly Leave Balance Review", job_type="run_task", task_id=task_balance_review, frequency="weekly", status="active", created_by=admin_user_id, ) ) # --------------------------------------------------------------- # 14. Menus (Menu Builder) — grouped "Leave Management" section # --------------------------------------------------------------- menu_defs = [ ("Apply Leave", "FileText", "form", form_id, 0), ("Employees", "Users", "master", employee_master_id, 1), ("Departments", "Building2", "master", department_master_id, 2), ("Leave Types", "CalendarDays", "master", leave_type_master_id, 3), ("Leave Balances", "Wallet", "master", leave_balance_master_id, 4), ] all_role_ids = [admin_role.id, *role_ids.values()] for label, icon, target_type, target_id, sort_order in menu_defs: menu_id = uid() db.add( Menu( id=menu_id, tenant_id=TENANT_ID, label=label, icon=icon, link_type="existing_feature", target_type=target_type, target_id=target_id, group_label="Leave Management", sort_order=sort_order, status="active", audience="internal", ) ) for rid in all_role_ids: db.add(MenuRole(menu_id=menu_id, role_id=rid, visible=True)) # --------------------------------------------------------------- # 15. Notifications (tenant-visible, demo events) # --------------------------------------------------------------- notif_defs = [ ("Leave Submitted", "Anu Thomas submitted a Sick Leave request (1 day) — awaiting Manager review."), ("Leave Approved", "Arun Kumar's Annual Leave request (15-17 Sep 2026) was approved."), ("Leave Rejected", "Karthik Raj's Sick Leave request was rejected by the Manager."), ("Sent Back for Correction", "Ravi Das's Sick Leave request was sent back — medical certificate required."), ("Leave Balance Alert", "Some employees are below their configured leave balance threshold. Review the Leave Balances datasource."), ] for title, message in notif_defs: db.add(Notification(tenant_id=TENANT_ID, title=title, message=message, source="leave_management")) # --------------------------------------------------------------- # 16. App bundle (organizational record of everything created) # --------------------------------------------------------------- items = ( [{"type": "master", "id": str(i)} for i in (department_master_id, employee_master_id, leave_type_master_id, leave_balance_master_id)] + [{"type": "form", "id": str(form_id)}] + [{"type": "workflow", "id": str(workflow_id)}] + [{"type": "dashboard", "id": str(i)} for i in (dash_employee, dash_manager, dash_hr)] + [{"type": "task", "id": str(i)} for i in (task_notify, task_balance_review)] ) db.add( App( tenant_id=TENANT_ID, name=APP_NAME, description="Role-based employee leave request, approval and reporting application, built from the Employee Leave Management System SRS.", items=items, created_by=admin_user_id, ) ) await db.flush() # --------------------------------------------------------------- # Summary # --------------------------------------------------------------- print("=" * 78) print(f"Employee Leave Management System provisioned under tenant {TENANT_ID}") print("=" * 78) print(f"\nMasters: Departments={department_master_id} Employees={employee_master_id}") print(f" Leave Types={leave_type_master_id} Leave Balances={leave_balance_master_id}") print(f"Form: Leave Application = {form_id}") print(f"Workflow: Leave Approval Workflow = {workflow_id}") print(f"Dashboards: Employee={dash_employee} Manager={dash_manager} HR={dash_hr}") print(f"Tasks: Leave Status Notification={task_notify} Leave Balance Review={task_balance_review}") print(f"\nRoles created:") for name, rid in role_ids.items(): print(f" {name:20s} {rid}") print(f"\nUsers created (default password for all: {DEFAULT_PASSWORD}):") for key, name, email, role_name in people: print(f" {name:15s} {email:38s} role={role_name:20s} id={user_ids[key]}") print("\nDone.") if __name__ == "__main__": asyncio.run(main())