""" Seeds a comprehensive Oil & Gas GRC domain build-out into tenant d6dac324-d5dc-48b0-aef6-bf5be837e3c2: 26 Masters (260 sample records total, 10 per Master), 3 Forms, 3 Tasks, and 3 Pages — modeled on the GRC/ compliance-audit console HTML mockups the user supplied (see the approved plan for the full field-by-field mapping and scope-consolidation notes: one unified Industry vocabulary, one normalized Asset model, the two meta-schema-builder sub-screens dropped as redundant with FlowFabric's own Master/Form field-schema builder, and the duplicate "Control Point" concept consolidated into one Master). Standalone script (same pattern as scripts/seed_sample_page.py / scripts/seed_leave_management_app.py): inserts rows directly through AsyncSessionLocal, bypassing the API/auth layer — field_schema/field_values are built as plain dicts matching MasterFieldDef/FormFieldDef/PageBlockDef's JSON shape, not API calls. Idempotency: looked up by tenant_id + Master name "Currency" first; if found, exits without creating anything. Usage: backend/.venv/Scripts/python.exe backend/scripts/seed_oil_gas_grc.py """ import asyncio import sys import uuid from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parent.parent)) from sqlalchemy import select # noqa: E402 from app.core.database import AsyncSessionLocal # noqa: E402 from app.models.automation import Task # noqa: E402 from app.models.identity import Tenant, User # noqa: E402 from app.models.master import Master, MasterRecord # noqa: E402 from app.models.navigation import Page # noqa: E402 from app.models.workflow import Form # noqa: E402 TENANT_ID = uuid.UUID("d6dac324-d5dc-48b0-aef6-bf5be837e3c2") def uid() -> str: return str(uuid.uuid4()) # --------------------------------------------------------------------------- # Master field_schema builders (MasterFieldDef-shaped dicts) # --------------------------------------------------------------------------- def f_text(name, required=False): return {"name": name, "type": "text", "required": required} def f_number(name, required=False): return {"name": name, "type": "number", "required": required} def f_boolean(name, required=False): return {"name": name, "type": "boolean", "required": required} def f_date(name, required=False): return {"name": name, "type": "date", "required": required} def f_select(name, options, required=False): return {"name": name, "type": "single_select", "required": required, "options": options} def f_status(options=None): return f_select("status", list(options or ["Active", "Inactive"]), required=True) def f_ref(name, target_master_id, display_field="name", multi=False, required=False): return { "name": name, "type": "reference", "required": required, "reference_master_id": str(target_master_id), "reference_display_field": display_field, "multi": multi, } # --------------------------------------------------------------------------- # Generic Master / MasterRecord helpers # --------------------------------------------------------------------------- async def create_master(db, name: str, field_schema: list[dict], admin_id) -> Master: m = Master(tenant_id=TENANT_ID, name=name, field_schema=field_schema, created_by=admin_id) db.add(m) await db.flush() return m async def insert_records(db, master: Master, rows: list[dict], admin_id) -> dict[str, MasterRecord]: """Each row may carry '_key' (a local id other rows can point at for self-references, e.g. a Unit Type hierarchy) and '_parent' (the '_key' of this row's parent record, resolved into the real record id's string form as `field_values["parent"]` once every row in this batch has an id). Returns {local_key: MasterRecord} for rows that declared '_key'.""" objs: list[tuple[MasterRecord, str | None, str | None]] = [] for row in rows: row = dict(row) local_key = row.pop("_key", None) parent_key = row.pop("_parent", None) rec = MasterRecord(tenant_id=TENANT_ID, master_id=master.id, field_values=row, created_by=admin_id) db.add(rec) objs.append((rec, local_key, parent_key)) await db.flush() keyed = {lk: rec for rec, lk, _ in objs if lk} for rec, _lk, pk in objs: if pk is not None: rec.field_values = {**rec.field_values, "parent": str(keyed[pk].id)} return keyed async def main() -> None: async with AsyncSessionLocal() as db: tenant = await db.get(Tenant, TENANT_ID) if tenant is None: raise SystemExit(f"tenant {TENANT_ID} not found") existing = ( await db.execute(select(Master).where(Master.tenant_id == TENANT_ID, Master.name == "Currency")) ).scalar_one_or_none() if existing is not None: print("'Currency' master already exists for this tenant — nothing to do.") print("Delete the seeded rows first if you want to reseed from scratch.") return admin: User | None = ( await db.execute(select(User).where(User.tenant_id == TENANT_ID).order_by(User.created_at).limit(1)) ).scalar_one_or_none() admin_id = admin.id if admin else None master_ids: dict[str, uuid.UUID] = {} records: dict[str, dict[str, MasterRecord]] = {} def mid(key: str) -> uuid.UUID: return master_ids[key] def rid(master_key: str, local_key: str) -> str: return str(records[master_key][local_key].id) # ------------------------------------------------------------------ # A. Reference / Configuration # ------------------------------------------------------------------ currency = await create_master( db, "Currency", [f_text("code", True), f_text("name", True), f_text("symbol", True), f_status()], admin_id, ) master_ids["Currency"] = currency.id records["Currency"] = await insert_records(db, currency, [ {"_key": "INR", "code": "INR", "name": "Indian Rupee", "symbol": "₹", "status": "Active"}, {"_key": "USD", "code": "USD", "name": "US Dollar", "symbol": "$", "status": "Active"}, {"_key": "EUR", "code": "EUR", "name": "Euro", "symbol": "€", "status": "Active"}, {"_key": "GBP", "code": "GBP", "name": "British Pound", "symbol": "£", "status": "Active"}, {"_key": "AED", "code": "AED", "name": "UAE Dirham", "symbol": "د.إ", "status": "Active"}, {"_key": "SAR", "code": "SAR", "name": "Saudi Riyal", "symbol": "﷼", "status": "Active"}, {"_key": "QAR", "code": "QAR", "name": "Qatari Riyal", "symbol": "﷼", "status": "Active"}, {"_key": "NOK", "code": "NOK", "name": "Norwegian Krone", "symbol": "kr", "status": "Active"}, {"_key": "NGN", "code": "NGN", "name": "Nigerian Naira", "symbol": "₦", "status": "Active"}, {"_key": "JPY", "code": "JPY", "name": "Japanese Yen", "symbol": "¥", "status": "Inactive"}, ], admin_id) sector = await create_master( db, "Sector", [f_text("code", True), f_text("name", True), f_text("description"), f_status()], admin_id, ) master_ids["Sector"] = sector.id records["Sector"] = await insert_records(db, sector, [ {"_key": "ENERGY", "code": "ENERGY", "name": "Energy", "description": "Energy production, transmission, and related operations.", "status": "Active"}, {"_key": "POWER", "code": "POWER", "name": "Power", "description": "Electricity generation, distribution, and utility operations.", "status": "Active"}, {"_key": "OILGAS", "code": "OILGAS", "name": "Oil & Gas", "description": "Upstream, midstream, and downstream oil and gas activities.", "status": "Active"}, {"_key": "MARITIME", "code": "MARITIME", "name": "Maritime", "description": "Shipping, ports, and offshore marine operations.", "status": "Active"}, {"_key": "AVIATION", "code": "AVIATION", "name": "Aviation", "description": "Aircraft operations, airports, and aviation support services.", "status": "Inactive"}, {"_key": "MINING", "code": "MINING", "name": "Mining", "description": "Surface and sub-surface extraction operations.", "status": "Active"}, {"_key": "UTILITIES", "code": "UTILITIES", "name": "Utilities", "description": "Water, gas, and electric utility operations.", "status": "Active"}, {"_key": "PETROCHEM", "code": "PETROCHEM", "name": "Petrochemicals", "description": "Refining and chemical processing operations.", "status": "Active"}, {"_key": "LOGISTICS", "code": "LOGISTICS", "name": "Logistics", "description": "Bulk transport, storage, and terminal operations.", "status": "Active"}, {"_key": "CONSTRUCTION", "code": "CONSTRUCTION", "name": "Construction", "description": "Engineering, procurement, and construction operations.", "status": "Active"}, ], admin_id) timezone = await create_master( db, "Timezone", [f_text("code", True), f_text("name", True), f_text("utc_offset", True), f_status()], admin_id, ) master_ids["Timezone"] = timezone.id records["Timezone"] = await insert_records(db, timezone, [ {"_key": "KOLKATA", "code": "Asia/Kolkata", "name": "India Standard Time", "utc_offset": "UTC +05:30", "status": "Active"}, {"_key": "NEWYORK", "code": "America/New_York", "name": "Eastern Time", "utc_offset": "UTC -05:00", "status": "Active"}, {"_key": "LONDON", "code": "Europe/London", "name": "Greenwich Mean Time", "utc_offset": "UTC +00:00", "status": "Active"}, {"_key": "BERLIN", "code": "Europe/Berlin", "name": "Central European Time", "utc_offset": "UTC +01:00", "status": "Active"}, {"_key": "DUBAI", "code": "Asia/Dubai", "name": "Gulf Standard Time", "utc_offset": "UTC +04:00", "status": "Active"}, {"_key": "TOKYO", "code": "Asia/Tokyo", "name": "Japan Standard Time", "utc_offset": "UTC +09:00", "status": "Inactive"}, {"_key": "RIYADH", "code": "Asia/Riyadh", "name": "Arabia Standard Time", "utc_offset": "UTC +03:00", "status": "Active"}, {"_key": "QATAR", "code": "Asia/Qatar", "name": "Arabia Standard Time (Qatar)", "utc_offset": "UTC +03:00", "status": "Active"}, {"_key": "OSLO", "code": "Europe/Oslo", "name": "Central European Time (Norway)", "utc_offset": "UTC +01:00", "status": "Active"}, {"_key": "LAGOS", "code": "Africa/Lagos", "name": "West Africa Time", "utc_offset": "UTC +01:00", "status": "Active"}, ], admin_id) country = await create_master( db, "Country", [f_text("name", True), f_text("code", True), f_ref("currency", mid("Currency"), "name"), f_ref("timezone", mid("Timezone"), "name"), f_status()], admin_id, ) master_ids["Country"] = country.id records["Country"] = await insert_records(db, country, [ {"name": "India", "code": "IN", "currency": rid("Currency", "INR"), "timezone": rid("Timezone", "KOLKATA"), "status": "Active"}, {"name": "United States", "code": "US", "currency": rid("Currency", "USD"), "timezone": rid("Timezone", "NEWYORK"), "status": "Active"}, {"name": "United Kingdom", "code": "GB", "currency": rid("Currency", "GBP"), "timezone": rid("Timezone", "LONDON"), "status": "Active"}, {"name": "Germany", "code": "DE", "currency": rid("Currency", "EUR"), "timezone": rid("Timezone", "BERLIN"), "status": "Active"}, {"name": "United Arab Emirates", "code": "AE", "currency": rid("Currency", "AED"), "timezone": rid("Timezone", "DUBAI"), "status": "Active"}, {"name": "Singapore", "code": "SG", "currency": rid("Currency", "USD"), "timezone": rid("Timezone", "DUBAI"), "status": "Inactive"}, {"name": "Saudi Arabia", "code": "SA", "currency": rid("Currency", "SAR"), "timezone": rid("Timezone", "RIYADH"), "status": "Active"}, {"name": "Qatar", "code": "QA", "currency": rid("Currency", "QAR"), "timezone": rid("Timezone", "QATAR"), "status": "Active"}, {"name": "Norway", "code": "NO", "currency": rid("Currency", "NOK"), "timezone": rid("Timezone", "OSLO"), "status": "Active"}, {"name": "Nigeria", "code": "NG", "currency": rid("Currency", "NGN"), "timezone": rid("Timezone", "LAGOS"), "status": "Active"}, ], admin_id) industry = await create_master( db, "Industry", [f_text("name", True), f_text("code", True), f_ref("sector", mid("Sector"), "name"), f_text("description"), f_status(["Active", "Planned", "Inactive"])], admin_id, ) master_ids["Industry"] = industry.id records["Industry"] = await insert_records(db, industry, [ {"_key": "OFFSHORE_DRILLING", "name": "Offshore Drilling", "code": "OG-DRL", "sector": rid("Sector", "OILGAS"), "description": "Offshore drilling contractors operating rigs, barges and support fleets.", "status": "Active"}, {"_key": "ONSHORE_PRODUCTION", "name": "Onshore Production", "code": "OG-ONP", "sector": rid("Sector", "OILGAS"), "description": "Onshore exploration and production operations.", "status": "Active"}, {"_key": "MIDSTREAM_PIPELINES", "name": "Midstream Pipelines", "code": "OG-MID", "sector": rid("Sector", "OILGAS"), "description": "Pipeline transport, storage and terminal operations.", "status": "Active"}, {"_key": "LNG_REFINING", "name": "LNG & Downstream Refining", "code": "OG-REF", "sector": rid("Sector", "PETROCHEM"), "description": "Refining, LNG processing, and petrochemical manufacturing.", "status": "Active"}, {"_key": "OILFIELD_SERVICES", "name": "Oilfield Services", "code": "OG-SVC", "sector": rid("Sector", "OILGAS"), "description": "Equipment, well services and technical contractors.", "status": "Active"}, {"_key": "OFFSHORE_SUPPORT_VESSELS", "name": "Offshore Support Vessels", "code": "MAR-OSV", "sector": rid("Sector", "MARITIME"), "description": "OSV operators supporting offshore energy logistics.", "status": "Active"}, {"_key": "POWER_TRANSMISSION", "name": "Power Transmission & Distribution", "code": "PWR-TRN", "sector": rid("Sector", "POWER"), "description": "Utilities focused on transmission and distribution assets.", "status": "Planned"}, {"_key": "SURFACE_MINING", "name": "Surface Mining", "code": "MIN-SFE", "sector": rid("Sector", "MINING"), "description": "Surface mining extraction operators.", "status": "Active"}, {"_key": "PETROCHEM_MFG", "name": "Petrochemical Manufacturing", "code": "PCH-MFG", "sector": rid("Sector", "PETROCHEM"), "description": "Chemical and petrochemical production facilities.", "status": "Active"}, {"_key": "ENERGY_LOGISTICS", "name": "Energy Logistics & Storage", "code": "LOG-STR", "sector": rid("Sector", "LOGISTICS"), "description": "Bulk storage, terminals and energy logistics.", "status": "Inactive"}, ], admin_id) unit_type = await create_master( db, "Unit Type", [f_text("name", True), f_text("code", True), f_ref("industry", mid("Industry"), "name"), f_ref("parent", industry.id, "name"), f_status()], # placeholder overwritten below (true self-ref) admin_id, ) master_ids["Unit Type"] = unit_type.id # Rebuild the "parent" field as a genuine self-reference now that unit_type.id is known. unit_type.field_schema = [ f_text("name", True), f_text("code", True), f_ref("industry", mid("Industry"), "name"), f_ref("parent", unit_type.id, "name"), f_status(), ] await db.flush() records["Unit Type"] = await insert_records(db, unit_type, [ {"_key": "RIG", "name": "Rig", "code": "RIG", "industry": rid("Industry", "OFFSHORE_DRILLING"), "status": "Active"}, {"_key": "FLEET", "_parent": "RIG", "name": "Fleet", "code": "FLEET", "industry": rid("Industry", "OFFSHORE_DRILLING"), "status": "Active"}, {"_key": "ZONE", "_parent": "FLEET", "name": "Zone", "code": "ZONE", "industry": rid("Industry", "OFFSHORE_DRILLING"), "status": "Active"}, {"_key": "PLATFORM", "name": "Platform", "code": "PLT", "industry": rid("Industry", "OFFSHORE_DRILLING"), "status": "Active"}, {"_key": "VESSEL", "name": "Vessel", "code": "VESSEL", "industry": rid("Industry", "OFFSHORE_SUPPORT_VESSELS"), "status": "Active"}, {"_key": "FACILITY", "name": "Facility", "code": "FAC", "industry": rid("Industry", "ONSHORE_PRODUCTION"), "status": "Active"}, {"_key": "EQUIPMENT", "name": "Equipment", "code": "EQP", "industry": rid("Industry", "OILFIELD_SERVICES"), "status": "Active"}, {"_key": "TERMINAL", "name": "Terminal", "code": "TERM", "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "status": "Active"}, {"_key": "WAREHOUSE", "name": "Warehouse", "code": "WH", "industry": rid("Industry", "OILFIELD_SERVICES"), "status": "Active"}, {"_key": "PIPELINE_SEGMENT", "name": "Pipeline Segment", "code": "PIPE", "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "status": "Active"}, ], admin_id) section = await create_master( db, "Section", [f_text("name", True), f_text("code", True), f_ref("unit_type", mid("Unit Type"), "name", required=True), f_number("display_order"), f_status()], admin_id, ) master_ids["Section"] = section.id records["Section"] = await insert_records(db, section, [ {"name": "General Information", "code": "GENERAL", "unit_type": rid("Unit Type", "RIG"), "display_order": 1, "status": "Active"}, {"name": "Operational Information", "code": "OPERATIONAL", "unit_type": rid("Unit Type", "RIG"), "display_order": 2, "status": "Active"}, {"name": "Technical Information", "code": "TECHNICAL", "unit_type": rid("Unit Type", "RIG"), "display_order": 3, "status": "Active"}, {"name": "Location Information", "code": "LOCATION", "unit_type": rid("Unit Type", "RIG"), "display_order": 4, "status": "Active"}, {"name": "General Information", "code": "GENERAL-PLT", "unit_type": rid("Unit Type", "PLATFORM"), "display_order": 1, "status": "Active"}, {"name": "Safety Information", "code": "SAFETY-PLT", "unit_type": rid("Unit Type", "PLATFORM"), "display_order": 2, "status": "Active"}, {"name": "General Information", "code": "GENERAL-VSL", "unit_type": rid("Unit Type", "VESSEL"), "display_order": 1, "status": "Active"}, {"name": "Crew Information", "code": "CREW-VSL", "unit_type": rid("Unit Type", "VESSEL"), "display_order": 2, "status": "Active"}, {"name": "Maintenance Information", "code": "MAINT", "unit_type": rid("Unit Type", "EQUIPMENT"), "display_order": 1, "status": "Active"}, {"name": "Capacity Information", "code": "CAPACITY", "unit_type": rid("Unit Type", "TERMINAL"), "display_order": 1, "status": "Active"}, ], admin_id) # ------------------------------------------------------------------ # B. GRC Core # ------------------------------------------------------------------ control_category = await create_master( db, "Control Category", [f_text("code", True), f_text("name", True), f_text("description"), f_status()], admin_id, ) master_ids["Control Category"] = control_category.id records["Control Category"] = await insert_records(db, control_category, [ {"_key": "ACCESS", "code": "CAT-001", "name": "Access Control", "description": "Controls related to user and system access management.", "status": "Active"}, {"_key": "DATASEC", "code": "CAT-002", "name": "Data Security", "description": "Controls focused on protecting data confidentiality and integrity.", "status": "Active"}, {"_key": "OPS", "code": "CAT-003", "name": "Operations", "description": "Controls supporting operational processes and activities.", "status": "Active"}, {"_key": "COMPLIANCE", "code": "CAT-004", "name": "Compliance", "description": "Controls supporting regulatory and policy compliance.", "status": "Inactive"}, {"_key": "BCM", "code": "CAT-005", "name": "Business Continuity", "description": "Controls supporting continuity and disaster recovery.", "status": "Active"}, {"_key": "INCIDENT", "code": "CAT-006", "name": "Incident Management", "description": "Controls governing incident detection and response.", "status": "Active"}, {"_key": "PHYSICAL", "code": "CAT-007", "name": "Physical Security", "description": "Controls protecting physical sites and equipment.", "status": "Active"}, {"_key": "ASSETMGMT", "code": "CAT-008", "name": "Asset Management", "description": "Controls governing asset registration and integrity.", "status": "Active"}, {"_key": "VENDOR", "code": "CAT-009", "name": "Vendor Management", "description": "Controls governing third-party and vendor oversight.", "status": "Active"}, {"_key": "ENVSAFETY", "code": "CAT-010", "name": "Environmental & Safety", "description": "Controls governing HSE compliance.", "status": "Active"}, ], admin_id) control_type = await create_master( db, "Control Type", [f_text("code", True), f_text("name", True), f_text("description"), f_status()], admin_id, ) master_ids["Control Type"] = control_type.id records["Control Type"] = await insert_records(db, control_type, [ {"_key": "PREVENTIVE", "code": "TYPE-001", "name": "Preventive Control", "description": "Control designed to prevent an unwanted event or condition.", "status": "Active"}, {"_key": "DETECTIVE", "code": "TYPE-002", "name": "Detective Control", "description": "Control designed to identify an unwanted event or condition.", "status": "Active"}, {"_key": "CORRECTIVE", "code": "TYPE-003", "name": "Corrective Control", "description": "Control designed to correct an identified issue or deficiency.", "status": "Active"}, {"_key": "DIRECTIVE", "code": "TYPE-004", "name": "Directive Control", "description": "Control that provides guidance, direction, or expectations.", "status": "Inactive"}, {"_key": "COMPENSATING", "code": "TYPE-005", "name": "Compensating Control", "description": "Control that substitutes for a primary control that cannot be applied.", "status": "Active"}, {"_key": "ADMINISTRATIVE", "code": "TYPE-006", "name": "Administrative Control", "description": "Policy- and procedure-based control.", "status": "Active"}, {"_key": "TECHNICAL", "code": "TYPE-007", "name": "Technical Control", "description": "System- or technology-enforced control.", "status": "Active"}, {"_key": "PHYSICAL", "code": "TYPE-008", "name": "Physical Control", "description": "Physical barrier or safeguard control.", "status": "Active"}, {"_key": "RECOVERY", "code": "TYPE-009", "name": "Recovery Control", "description": "Control that restores normal operations after an event.", "status": "Active"}, {"_key": "DETERRENT", "code": "TYPE-010", "name": "Deterrent Control", "description": "Control designed to discourage a violation.", "status": "Active"}, ], admin_id) result_type = await create_master( db, "Result Type", [f_text("code", True), f_text("name", True), f_status()], admin_id, ) master_ids["Result Type"] = result_type.id records["Result Type"] = await insert_records(db, result_type, [ {"_key": "OBJ", "code": "RT-OBJ", "name": "Objective", "status": "Active"}, {"_key": "BIN", "code": "RT-BIN", "name": "Binary (Pass/Fail)", "status": "Active"}, {"_key": "NUM", "code": "RT-NUM", "name": "Numeric with Unit", "status": "Active"}, {"_key": "SUB", "code": "RT-SUB", "name": "Subjective Rating", "status": "Active"}, {"_key": "EQ", "code": "RT-EQ", "name": "Equation", "status": "Active"}, {"_key": "PCT", "code": "RT-PCT", "name": "Percentage", "status": "Active"}, {"_key": "DUR", "code": "RT-DUR", "name": "Duration (Hours)", "status": "Active"}, {"_key": "DURM", "code": "RT-DUR-M", "name": "Duration (Months)", "status": "Active"}, {"_key": "TXT", "code": "RT-TXT", "name": "Text Attestation", "status": "Active"}, {"_key": "CHK", "code": "RT-CHK", "name": "Checklist Completion", "status": "Inactive"}, ], admin_id) control = await create_master( db, "Control", [f_text("code", True), f_text("title", True), f_text("description"), f_ref("category", mid("Control Category"), "name", required=True), f_ref("control_type", mid("Control Type"), "name", required=True), f_status(["Draft", "Published", "Retired"])], admin_id, ) master_ids["Control"] = control.id records["Control"] = await insert_records(db, control, [ {"_key": "CTL001", "code": "CTL-001", "title": "Access Review and Approval", "description": "Periodic review and approval of user access rights.", "category": rid("Control Category", "ACCESS"), "control_type": rid("Control Type", "PREVENTIVE"), "status": "Published"}, {"_key": "CTL002", "code": "CTL-002", "title": "Backup and Recovery", "description": "Regular backup and recovery testing of critical systems.", "category": rid("Control Category", "BCM"), "control_type": rid("Control Type", "CORRECTIVE"), "status": "Published"}, {"_key": "CTL003", "code": "CTL-003", "title": "Security Incident Response", "description": "Structured response process for security incidents.", "category": rid("Control Category", "INCIDENT"), "control_type": rid("Control Type", "DETECTIVE"), "status": "Draft"}, {"_key": "CTL004", "code": "CTL-004", "title": "Well Control Barrier Verification", "description": "Verification of primary and secondary well barriers before operations.", "category": rid("Control Category", "OPS"), "control_type": rid("Control Type", "PREVENTIVE"), "status": "Published"}, {"_key": "CTL005", "code": "CTL-005", "title": "Permit to Work Compliance", "description": "Ensures hazardous work is authorized via a valid permit-to-work.", "category": rid("Control Category", "PHYSICAL"), "control_type": rid("Control Type", "PREVENTIVE"), "status": "Published"}, {"_key": "CTL006", "code": "CTL-006", "title": "Pipeline Integrity Inspection", "description": "Scheduled inspection of pipeline integrity and corrosion monitoring.", "category": rid("Control Category", "ASSETMGMT"), "control_type": rid("Control Type", "DETECTIVE"), "status": "Published"}, {"_key": "CTL007", "code": "CTL-007", "title": "Emergency Shutdown System Test", "description": "Periodic testing of ESD systems on offshore platforms.", "category": rid("Control Category", "ENVSAFETY"), "control_type": rid("Control Type", "DETECTIVE"), "status": "Published"}, {"_key": "CTL008", "code": "CTL-008", "title": "Vendor HSE Qualification", "description": "Verifies contractor HSE qualification before site mobilization.", "category": rid("Control Category", "VENDOR"), "control_type": rid("Control Type", "PREVENTIVE"), "status": "Published"}, {"_key": "CTL009", "code": "CTL-009", "title": "Cryptographic Key Management", "description": "Lifecycle management of cryptographic keys and certificates.", "category": rid("Control Category", "DATASEC"), "control_type": rid("Control Type", "TECHNICAL"), "status": "Draft"}, {"_key": "CTL010", "code": "CTL-010", "title": "Environmental Discharge Monitoring", "description": "Monitors regulated discharge limits at production facilities.", "category": rid("Control Category", "ENVSAFETY"), "control_type": rid("Control Type", "DETECTIVE"), "status": "Published"}, ], admin_id) control_point = await create_master( db, "Control Point", [f_text("code", True), f_text("title", True), f_ref("controls", mid("Control"), "title", multi=True, required=True), f_ref("result_type", mid("Result Type"), "name"), f_boolean("required"), f_status()], admin_id, ) master_ids["Control Point"] = control_point.id records["Control Point"] = await insert_records(db, control_point, [ {"code": "CP-001", "title": "Access review completed at defined frequency.", "controls": [rid("Control", "CTL001")], "result_type": rid("Result Type", "BIN"), "required": True, "status": "Active"}, {"code": "CP-002", "title": "Review evidence identifies reviewer and approval date.", "controls": [rid("Control", "CTL001")], "result_type": rid("Result Type", "BIN"), "required": True, "status": "Active"}, {"code": "CP-003", "title": "Recovery testing demonstrates required recovery objective.", "controls": [rid("Control", "CTL002")], "result_type": rid("Result Type", "PCT"), "required": False, "status": "Active"}, {"code": "CP-004", "title": "Evidence retained for defined retention period.", "controls": [rid("Control", "CTL002"), rid("Control", "CTL009")], "result_type": rid("Result Type", "OBJ"), "required": True, "status": "Active"}, {"code": "CP-005", "title": "Owner acknowledgement recorded for control activity.", "controls": [rid("Control", "CTL003")], "result_type": rid("Result Type", "SUB"), "required": False, "status": "Active"}, {"code": "CP-006", "title": "Primary well barrier verified and logged.", "controls": [rid("Control", "CTL004")], "result_type": rid("Result Type", "BIN"), "required": True, "status": "Active"}, {"code": "CP-007", "title": "Permit-to-work signed by authorized approver.", "controls": [rid("Control", "CTL005")], "result_type": rid("Result Type", "BIN"), "required": True, "status": "Active"}, {"code": "CP-008", "title": "Pipeline inspection completed within schedule.", "controls": [rid("Control", "CTL006")], "result_type": rid("Result Type", "DURM"), "required": True, "status": "Active"}, {"code": "CP-009", "title": "ESD system test passed within SLA.", "controls": [rid("Control", "CTL007")], "result_type": rid("Result Type", "DUR"), "required": True, "status": "Active"}, {"code": "CP-010", "title": "Contractor HSE qualification current and on file.", "controls": [rid("Control", "CTL008")], "result_type": rid("Result Type", "BIN"), "required": True, "status": "Active"}, ], admin_id) framework = await create_master( db, "Framework", [f_text("code", True), f_text("name", True), f_select("type", ["Regulatory", "Industry Standard", "Internal"], required=True), f_ref("industries", mid("Industry"), "name", multi=True), f_text("description"), f_status()], admin_id, ) master_ids["Framework"] = framework.id records["Framework"] = await insert_records(db, framework, [ {"_key": "ISO27001", "code": "ISO 27001", "name": "Information Security Management System", "type": "Industry Standard", "industries": [rid("Industry", "OFFSHORE_DRILLING"), rid("Industry", "POWER_TRANSMISSION")], "description": "Information security management system requirements.", "status": "Active"}, {"_key": "NISTCSF", "code": "NIST CSF", "name": "Cybersecurity Framework", "type": "Industry Standard", "industries": [rid("Industry", "OFFSHORE_SUPPORT_VESSELS")], "description": "Voluntary cybersecurity risk management framework.", "status": "Active"}, {"_key": "ISO14001", "code": "ISO 14001", "name": "Environmental Management System", "type": "Industry Standard", "industries": [rid("Industry", "ONSHORE_PRODUCTION")], "description": "Environmental management system requirements.", "status": "Active"}, {"_key": "ISO45001", "code": "ISO 45001", "name": "Occupational Health & Safety", "type": "Industry Standard", "industries": [rid("Industry", "OFFSHORE_DRILLING")], "description": "Occupational health and safety management systems.", "status": "Inactive"}, {"_key": "ISA62443", "code": "ISA/IEC 62443-3-3", "name": "System Security Requirements & SLs", "type": "Industry Standard", "industries": [rid("Industry", "MIDSTREAM_PIPELINES")], "description": "OT/ICS system security requirements and security levels.", "status": "Active"}, {"_key": "SOC2", "code": "SOC 2", "name": "Service Organization Control 2", "type": "Regulatory", "industries": [], "description": "Trust services criteria for service organizations.", "status": "Active"}, {"_key": "APIRP75", "code": "API RP 75", "name": "Safety and Environmental Management Systems", "type": "Industry Standard", "industries": [rid("Industry", "OFFSHORE_DRILLING")], "description": "SEMS for offshore operations.", "status": "Active"}, {"_key": "OSHAPSM", "code": "OSHA PSM", "name": "Process Safety Management", "type": "Regulatory", "industries": [rid("Industry", "ONSHORE_PRODUCTION"), rid("Industry", "OILFIELD_SERVICES")], "description": "Process safety management of highly hazardous chemicals.", "status": "Active"}, {"_key": "IMOISM", "code": "IMO ISM Code", "name": "International Safety Management Code", "type": "Regulatory", "industries": [rid("Industry", "OFFSHORE_SUPPORT_VESSELS")], "description": "International management code for the safe operation of ships.", "status": "Active"}, {"_key": "INTHSE", "code": "INT-HSE", "name": "Internal HSE Policy Framework", "type": "Internal", "industries": [rid("Industry", "OFFSHORE_DRILLING"), rid("Industry", "ONSHORE_PRODUCTION")], "description": "Internal health, safety and environment policy suite.", "status": "Active"}, ], admin_id) framework_version = await create_master( db, "Framework Version", [f_ref("framework", mid("Framework"), "name", required=True), f_text("version", True), f_date("from_date"), f_date("to_date"), f_status(["ACTIVE", "SUPERSEDED", "DRAFT"]), f_text("changelog")], admin_id, ) master_ids["Framework Version"] = framework_version.id records["Framework Version"] = await insert_records(db, framework_version, [ {"_key": "ISO27001_2022", "framework": rid("Framework", "ISO27001"), "version": "2022-10", "from_date": "2022-10-01", "to_date": None, "status": "ACTIVE", "changelog": "Annex A restructure vs 2013-10; 93 controls; new threat intelligence and cloud themes."}, {"_key": "ISO27001_2013", "framework": rid("Framework", "ISO27001"), "version": "2013-10", "from_date": "2013-10-01", "to_date": "2022-09-30", "status": "SUPERSEDED", "changelog": "Initial ISO 27001:2013 publication."}, {"_key": "NISTCSF_2024", "framework": rid("Framework", "NISTCSF"), "version": "2024-02", "from_date": "2024-02-26", "to_date": None, "status": "ACTIVE", "changelog": "CSF 2.0 publication with Govern function added."}, {"_key": "ISO14001_2015", "framework": rid("Framework", "ISO14001"), "version": "2015-09", "from_date": "2015-09-15", "to_date": None, "status": "ACTIVE", "changelog": "ISO 14001:2015 current edition."}, {"_key": "ISO45001_2018", "framework": rid("Framework", "ISO45001"), "version": "2018-03", "from_date": "2018-03-12", "to_date": None, "status": "ACTIVE", "changelog": "Initial ISO 45001:2018 publication."}, {"_key": "ISA62443_2013", "framework": rid("Framework", "ISA62443"), "version": "2013-08", "from_date": "2013-08-01", "to_date": None, "status": "ACTIVE", "changelog": "System security requirements and security levels."}, {"_key": "SOC2_2017", "framework": rid("Framework", "SOC2"), "version": "2017-05", "from_date": "2017-05-01", "to_date": None, "status": "ACTIVE", "changelog": "Trust Services Criteria 2017."}, {"_key": "APIRP75_2019", "framework": rid("Framework", "APIRP75"), "version": "2019-05", "from_date": "2019-05-01", "to_date": None, "status": "ACTIVE", "changelog": "4th edition SEMS requirements."}, {"_key": "OSHAPSM_1992", "framework": rid("Framework", "OSHAPSM"), "version": "1992-02", "from_date": "1992-02-24", "to_date": None, "status": "ACTIVE", "changelog": "29 CFR 1910.119 baseline."}, {"_key": "IMOISM_2018", "framework": rid("Framework", "IMOISM"), "version": "2018-01", "from_date": "2018-01-01", "to_date": None, "status": "ACTIVE", "changelog": "2018 consolidated edition."}, ], admin_id) clause = await create_master( db, "Clause", [f_ref("framework_version", mid("Framework Version"), "version", required=True), f_ref("parent", None, "title"), # placeholder, rebuilt below (self-ref) f_text("clause_number", True), f_text("title", True), f_text("description"), f_status()], admin_id, ) master_ids["Clause"] = clause.id clause.field_schema = [ f_ref("framework_version", mid("Framework Version"), "version", required=True), f_ref("parent", clause.id, "title"), f_text("clause_number", True), f_text("title", True), f_text("description"), f_status(), ] await db.flush() records["Clause"] = await insert_records(db, clause, [ {"_key": "A5", "framework_version": rid("Framework Version", "ISO27001_2022"), "clause_number": "A.5", "title": "Organizational Controls", "description": "", "status": "Active"}, {"_key": "A51", "_parent": "A5", "framework_version": rid("Framework Version", "ISO27001_2022"), "clause_number": "A.5.1", "title": "Policies for information security", "description": "", "status": "Active"}, {"_key": "A57", "_parent": "A5", "framework_version": rid("Framework Version", "ISO27001_2022"), "clause_number": "A.5.7", "title": "Threat intelligence", "description": "", "status": "Active"}, {"_key": "A6", "framework_version": rid("Framework Version", "ISO27001_2022"), "clause_number": "A.6", "title": "People Controls", "description": "", "status": "Active"}, {"_key": "A61", "_parent": "A6", "framework_version": rid("Framework Version", "ISO27001_2022"), "clause_number": "A.6.1", "title": "Screening", "description": "", "status": "Active"}, {"_key": "A8", "framework_version": rid("Framework Version", "ISO27001_2022"), "clause_number": "A.8", "title": "Technological Controls", "description": "", "status": "Active"}, {"_key": "ID", "framework_version": rid("Framework Version", "NISTCSF_2024"), "clause_number": "ID", "title": "Identify", "description": "", "status": "Active"}, {"_key": "ID_AM", "_parent": "ID", "framework_version": rid("Framework Version", "NISTCSF_2024"), "clause_number": "ID.AM", "title": "Asset Management", "description": "", "status": "Active"}, {"_key": "PR", "framework_version": rid("Framework Version", "NISTCSF_2024"), "clause_number": "PR", "title": "Protect", "description": "", "status": "Active"}, {"_key": "CLAUSE5", "framework_version": rid("Framework Version", "ISO45001_2018"), "clause_number": "Clause 5", "title": "Leadership and worker participation", "description": "", "status": "Active"}, ], admin_id) objective = await create_master( db, "Objective", [f_ref("clause", mid("Clause"), "title", required=True), f_ref("parent", None, "title"), f_text("code", True), f_text("title", True), f_text("description"), f_status()], admin_id, ) master_ids["Objective"] = objective.id objective.field_schema = [ f_ref("clause", mid("Clause"), "title", required=True), f_ref("parent", objective.id, "title"), f_text("code", True), f_text("title", True), f_text("description"), f_status(), ] await db.flush() records["Objective"] = await insert_records(db, objective, [ {"_key": "OBJ511", "clause": rid("Clause", "A51"), "code": "OBJ-5.1.1", "title": "Policy framework defined", "description": "", "status": "Active"}, {"_key": "A511", "_parent": "OBJ511", "clause": rid("Clause", "A51"), "code": "A.5.1.1", "title": "Policy framework documented", "description": "", "status": "Active"}, {"_key": "A512", "_parent": "OBJ511", "clause": rid("Clause", "A51"), "code": "A.5.1.2", "title": "Policy review cadence defined", "description": "", "status": "Active"}, {"_key": "OBJ512", "clause": rid("Clause", "A51"), "code": "OBJ-5.1.2", "title": "Policy review & maintenance", "description": "", "status": "Active"}, {"_key": "OBJ571", "clause": rid("Clause", "A57"), "code": "OBJ-5.7.1", "title": "Threat intel feed operational", "description": "", "status": "Active"}, {"_key": "OBJ611", "clause": rid("Clause", "A61"), "code": "OBJ-6.1.1", "title": "Background screening completed", "description": "", "status": "Active"}, {"_key": "OBJIDAM1", "clause": rid("Clause", "ID_AM"), "code": "OBJ-ID.AM.1", "title": "Asset inventory maintained", "description": "", "status": "Active"}, {"_key": "OBJIDAM2", "clause": rid("Clause", "ID_AM"), "code": "OBJ-ID.AM.2", "title": "Asset ownership assigned", "description": "", "status": "Active"}, {"_key": "OBJLEAD1", "clause": rid("Clause", "CLAUSE5"), "code": "OBJ-5.LEAD.1", "title": "Leadership commitment demonstrated", "description": "", "status": "Active"}, {"_key": "OBJLEAD2", "clause": rid("Clause", "CLAUSE5"), "code": "OBJ-5.LEAD.2", "title": "Worker participation process established", "description": "", "status": "Active"}, ], admin_id) # ------------------------------------------------------------------ # C. Risk & Evidence # ------------------------------------------------------------------ risk_category = await create_master( db, "Risk Category", [f_text("code", True), f_text("name", True), f_ref("industry", mid("Industry"), "name"), f_ref("parent", None, "name"), f_text("description"), f_status()], admin_id, ) master_ids["Risk Category"] = risk_category.id risk_category.field_schema = [ f_text("code", True), f_text("name", True), f_ref("industry", mid("Industry"), "name"), f_ref("parent", risk_category.id, "name"), f_text("description"), f_status(), ] await db.flush() records["Risk Category"] = await insert_records(db, risk_category, [ {"_key": "RC001", "code": "RC-001", "name": "Operational Risk", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Risks related to internal operations and processes.", "status": "Active"}, {"_key": "RC00101", "_parent": "RC001", "code": "RC-001-01", "name": "Process Risk", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Risks related to business process execution.", "status": "Active"}, {"_key": "RC0010101", "_parent": "RC00101", "code": "RC-001-01-01", "name": "Well Control Risk", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Risks in well control barrier failures.", "status": "Active"}, {"_key": "RC002", "code": "RC-002", "name": "HSE Risk", "industry": rid("Industry", "ONSHORE_PRODUCTION"), "description": "Health, safety and environmental risk exposure.", "status": "Active"}, {"_key": "RC00201", "_parent": "RC002", "code": "RC-002-01", "name": "Process Safety Risk", "industry": rid("Industry", "ONSHORE_PRODUCTION"), "description": "Risks from loss of primary containment.", "status": "Active"}, {"_key": "RC003", "code": "RC-003", "name": "Compliance Risk", "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "description": "Risks related to regulatory compliance.", "status": "Active"}, {"_key": "RC00301", "_parent": "RC003", "code": "RC-003-01", "name": "Regulatory Risk", "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "description": "Risks from changing pipeline regulations.", "status": "Active"}, {"_key": "RC004", "code": "RC-004", "name": "Market Risk", "industry": rid("Industry", "POWER_TRANSMISSION"), "description": "Risks related to market fluctuations.", "status": "Inactive"}, {"_key": "RC005", "code": "RC-005", "name": "Third-Party Risk", "industry": rid("Industry", "OILFIELD_SERVICES"), "description": "Risks arising from contractors and vendors.", "status": "Active"}, {"_key": "RC006", "code": "RC-006", "name": "Environmental Risk", "industry": rid("Industry", "ONSHORE_PRODUCTION"), "description": "Risks of environmental incidents and spills.", "status": "Active"}, ], admin_id) evidence_type = await create_master( db, "Evidence Type", [f_text("code", True), f_text("name", True), f_text("description"), f_status()], admin_id, ) master_ids["Evidence Type"] = evidence_type.id records["Evidence Type"] = await insert_records(db, evidence_type, [ {"code": "CODE-001", "name": "Document Upload", "description": "Physical or digital documents as evidence.", "status": "Active"}, {"code": "CODE-002", "name": "Video/Audio Recording", "description": "Video or audio recordings for evidence.", "status": "Active"}, {"code": "CODE-003", "name": "Inspection Report", "description": "Formal inspection reports and findings.", "status": "Active"}, {"code": "CODE-004", "name": "Certification", "description": "Official certifications and credentials.", "status": "Active"}, {"code": "CODE-005", "name": "Interview Notes", "description": "Interview records and witness statements.", "status": "Inactive"}, {"code": "CODE-006", "name": "Photographic Evidence", "description": "Photographs documenting site conditions.", "status": "Active"}, {"code": "CODE-007", "name": "System Log Export", "description": "Exported logs from monitored systems.", "status": "Active"}, {"code": "CODE-008", "name": "Test Result Report", "description": "Results from technical or safety testing.", "status": "Active"}, {"code": "CODE-009", "name": "Site Survey Record", "description": "Records from physical site surveys.", "status": "Active"}, {"code": "CODE-010", "name": "Third-Party Audit Report", "description": "Audit reports from external assessors.", "status": "Active"}, ], admin_id) # ------------------------------------------------------------------ # D. Asset # ------------------------------------------------------------------ asset_type = await create_master( db, "Asset Type", [f_text("code", True), f_text("name", True), f_ref("industry", mid("Industry"), "name", multi=True), f_text("description"), f_status()], admin_id, ) master_ids["Asset Type"] = asset_type.id records["Asset Type"] = await insert_records(db, asset_type, [ {"_key": "AT001", "code": "AT-001", "name": "Offshore Rig", "industry": [rid("Industry", "OFFSHORE_DRILLING")], "description": "Drilling and production rigs.", "status": "Active"}, {"_key": "AT002", "code": "AT-002", "name": "Support Vessel", "industry": [rid("Industry", "OFFSHORE_SUPPORT_VESSELS")], "description": "Platform support vessels.", "status": "Active"}, {"_key": "AT003", "code": "AT-003", "name": "Production Wellhead", "industry": [rid("Industry", "ONSHORE_PRODUCTION")], "description": "Onshore production wellheads.", "status": "Active"}, {"_key": "AT004", "code": "AT-004", "name": "Pipeline Segment", "industry": [rid("Industry", "MIDSTREAM_PIPELINES")], "description": "Transport pipeline segments.", "status": "Active"}, {"_key": "AT005", "code": "AT-005", "name": "Storage Tank", "industry": [rid("Industry", "MIDSTREAM_PIPELINES")], "description": "Bulk storage tanks and terminals.", "status": "Active"}, {"_key": "AT006", "code": "AT-006", "name": "Compressor Unit", "industry": [rid("Industry", "OILFIELD_SERVICES")], "description": "Gas compression units.", "status": "Active"}, {"_key": "AT007", "code": "AT-007", "name": "Refinery Unit", "industry": [rid("Industry", "LNG_REFINING")], "description": "Process units within a refinery.", "status": "Active"}, {"_key": "AT008", "code": "AT-008", "name": "Transmission Substation", "industry": [rid("Industry", "POWER_TRANSMISSION")], "description": "Electrical transmission substation.", "status": "Active"}, {"_key": "AT009", "code": "AT-009", "name": "Mobile Drilling Equipment", "industry": [rid("Industry", "OILFIELD_SERVICES")], "description": "Portable drilling support equipment.", "status": "Active"}, {"_key": "AT010", "code": "AT-010", "name": "LNG Terminal", "industry": [rid("Industry", "LNG_REFINING")], "description": "LNG import/export terminal.", "status": "Inactive"}, ], admin_id) asset_category = await create_master( db, "Asset Category", [f_text("code", True), f_text("name", True), f_ref("industry", mid("Industry"), "name"), f_ref("parent", None, "name"), f_text("description"), f_status()], admin_id, ) master_ids["Asset Category"] = asset_category.id asset_category.field_schema = [ f_text("code", True), f_text("name", True), f_ref("industry", mid("Industry"), "name"), f_ref("parent", asset_category.id, "name"), f_text("description"), f_status(), ] await db.flush() records["Asset Category"] = await insert_records(db, asset_category, [ {"_key": "AC001", "code": "AC-001", "name": "Surface Equipment", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Topside equipment.", "status": "Active"}, {"_key": "AC00101", "_parent": "AC001", "code": "AC-001-01", "name": "Rotating Equipment", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Pumps, compressors, turbines.", "status": "Active"}, {"_key": "AC00102", "_parent": "AC001", "code": "AC-001-02", "name": "Static Equipment", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Vessels, tanks, exchangers.", "status": "Active"}, {"_key": "AC002", "code": "AC-002", "name": "Safety Systems", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Safety-critical equipment.", "status": "Active"}, {"_key": "AC00201", "_parent": "AC002", "code": "AC-002-01", "name": "Fire & Gas Systems", "industry": rid("Industry", "OFFSHORE_DRILLING"), "description": "Fire and gas detection.", "status": "Active"}, {"_key": "AC003", "code": "AC-003", "name": "Pipeline Assets", "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "description": "Pipeline and transport assets.", "status": "Active"}, {"_key": "AC004", "code": "AC-004", "name": "Storage Assets", "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "description": "Storage and terminal assets.", "status": "Active"}, {"_key": "AC005", "code": "AC-005", "name": "Navigation Systems", "industry": rid("Industry", "OFFSHORE_SUPPORT_VESSELS"), "description": "Navigation and positioning systems.", "status": "Active"}, {"_key": "AC006", "code": "AC-006", "name": "Environmental Controls", "industry": rid("Industry", "ONSHORE_PRODUCTION"), "description": "Environmental monitoring and controls.", "status": "Inactive"}, {"_key": "AC007", "code": "AC-007", "name": "Electrical Assets", "industry": rid("Industry", "POWER_TRANSMISSION"), "description": "Transmission and distribution electrical assets.", "status": "Active"}, ], admin_id) asset_category_relation = await create_master( db, "Asset Category Relation", [f_ref("parent_category", mid("Asset Category"), "name", required=True), f_ref("child_category", mid("Asset Category"), "name", required=True), f_status()], admin_id, ) master_ids["Asset Category Relation"] = asset_category_relation.id records["Asset Category Relation"] = await insert_records(db, asset_category_relation, [ {"parent_category": rid("Asset Category", "AC001"), "child_category": rid("Asset Category", "AC00101"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC001"), "child_category": rid("Asset Category", "AC00102"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC002"), "child_category": rid("Asset Category", "AC00201"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC003"), "child_category": rid("Asset Category", "AC004"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC001"), "child_category": rid("Asset Category", "AC002"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC004"), "child_category": rid("Asset Category", "AC003"), "status": "Inactive"}, {"parent_category": rid("Asset Category", "AC005"), "child_category": rid("Asset Category", "AC001"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC006"), "child_category": rid("Asset Category", "AC002"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC007"), "child_category": rid("Asset Category", "AC003"), "status": "Active"}, {"parent_category": rid("Asset Category", "AC00201"), "child_category": rid("Asset Category", "AC00101"), "status": "Inactive"}, ], admin_id) asset_type_category_mapping = await create_master( db, "Asset Type-Category Mapping", [f_ref("asset_type", mid("Asset Type"), "name", required=True), f_ref("asset_category", mid("Asset Category"), "name", required=True), f_status()], admin_id, ) master_ids["Asset Type-Category Mapping"] = asset_type_category_mapping.id records["Asset Type-Category Mapping"] = await insert_records(db, asset_type_category_mapping, [ {"asset_type": rid("Asset Type", "AT001"), "asset_category": rid("Asset Category", "AC001"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT001"), "asset_category": rid("Asset Category", "AC00101"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT002"), "asset_category": rid("Asset Category", "AC005"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT003"), "asset_category": rid("Asset Category", "AC006"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT004"), "asset_category": rid("Asset Category", "AC003"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT005"), "asset_category": rid("Asset Category", "AC004"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT006"), "asset_category": rid("Asset Category", "AC00101"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT007"), "asset_category": rid("Asset Category", "AC00102"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT008"), "asset_category": rid("Asset Category", "AC007"), "status": "Active"}, {"asset_type": rid("Asset Type", "AT009"), "asset_category": rid("Asset Category", "AC00201"), "status": "Inactive"}, ], admin_id) # ------------------------------------------------------------------ # E. Commercial # ------------------------------------------------------------------ package = await create_master( db, "Package", [f_text("code", True), f_text("name", True), f_text("description"), f_number("monthly_price"), f_number("annual_price"), f_ref("currency", mid("Currency"), "name"), f_number("max_users"), f_status()], admin_id, ) master_ids["Package"] = package.id records["Package"] = await insert_records(db, package, [ {"_key": "RISK_PACKAGE", "code": "RISK_PACKAGE", "name": "Risk Management", "description": "Risk management package.", "monthly_price": 25000, "annual_price": 250000, "currency": rid("Currency", "USD"), "max_users": 50, "status": "Active"}, {"_key": "COMPLIANCE_PACKAGE", "code": "COMPLIANCE_PACKAGE", "name": "Compliance Suite", "description": "Compliance and control framework package.", "monthly_price": 18000, "annual_price": 180000, "currency": rid("Currency", "USD"), "max_users": 25, "status": "Active"}, {"_key": "OPS_PACKAGE", "code": "OPS_PACKAGE", "name": "Operations Dashboard", "description": "Operational oversight package.", "monthly_price": 22000, "annual_price": 220000, "currency": rid("Currency", "USD"), "max_users": 100, "status": "Inactive"}, {"_key": "HSE_PACKAGE", "code": "HSE_PACKAGE", "name": "HSE Management", "description": "Health, safety and environmental management package.", "monthly_price": 20000, "annual_price": 200000, "currency": rid("Currency", "USD"), "max_users": 75, "status": "Active"}, {"_key": "ASSET_PACKAGE", "code": "ASSET_PACKAGE", "name": "Asset Integrity", "description": "Asset integrity and inspection management package.", "monthly_price": 19000, "annual_price": 190000, "currency": rid("Currency", "USD"), "max_users": 60, "status": "Active"}, {"_key": "AUDIT_PACKAGE", "code": "AUDIT_PACKAGE", "name": "Audit Management", "description": "Internal and external audit management package.", "monthly_price": 15000, "annual_price": 150000, "currency": rid("Currency", "USD"), "max_users": 30, "status": "Active"}, {"_key": "VENDOR_PACKAGE", "code": "VENDOR_PACKAGE", "name": "Vendor Assurance", "description": "Third-party and vendor assurance package.", "monthly_price": 12000, "annual_price": 120000, "currency": rid("Currency", "USD"), "max_users": 40, "status": "Active"}, {"_key": "INCIDENT_PACKAGE", "code": "INCIDENT_PACKAGE", "name": "Incident Management", "description": "Incident reporting and investigation package.", "monthly_price": 14000, "annual_price": 140000, "currency": rid("Currency", "USD"), "max_users": 35, "status": "Active"}, {"_key": "ENTERPRISE_PACKAGE", "code": "ENTERPRISE_PACKAGE", "name": "Enterprise GRC", "description": "Full enterprise GRC suite.", "monthly_price": 45000, "annual_price": 450000, "currency": rid("Currency", "USD"), "max_users": 250, "status": "Active"}, {"_key": "STARTER_PACKAGE", "code": "STARTER_PACKAGE", "name": "Starter", "description": "Entry-level compliance starter package.", "monthly_price": 6000, "annual_price": 60000, "currency": rid("Currency", "USD"), "max_users": 10, "status": "Active"}, ], admin_id) module = await create_master( db, "Module", [f_text("code", True), f_text("name", True), f_ref("package", mid("Package"), "name", required=True), f_text("description"), f_number("monthly_price"), f_number("annual_price"), f_ref("currency", mid("Currency"), "name"), f_status()], admin_id, ) master_ids["Module"] = module.id records["Module"] = await insert_records(db, module, [ {"_key": "RISK_MANAGEMENT", "code": "RISK_MANAGEMENT", "name": "Risk Management", "package": rid("Package", "RISK_PACKAGE"), "description": "Risk management functions.", "monthly_price": 15000, "annual_price": 150000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "CONTROL_ASSURANCE", "code": "CONTROL_ASSURANCE", "name": "Control Assurance", "package": rid("Package", "RISK_PACKAGE"), "description": "Controls and assurance framework.", "monthly_price": 12000, "annual_price": 120000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "COMPLIANCE_TRACKER", "code": "COMPLIANCE_TRACKER", "name": "Compliance Tracker", "package": rid("Package", "COMPLIANCE_PACKAGE"), "description": "Compliance tracking and reporting.", "monthly_price": 9000, "annual_price": 90000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "FRAMEWORK_MGMT", "code": "FRAMEWORK_MGMT", "name": "Framework Management", "package": rid("Package", "COMPLIANCE_PACKAGE"), "description": "Framework and control mapping management.", "monthly_price": 8000, "annual_price": 80000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "ASSET_REGISTER", "code": "ASSET_REGISTER", "name": "Asset Register", "package": rid("Package", "ASSET_PACKAGE"), "description": "Asset registration and hierarchy management.", "monthly_price": 7000, "annual_price": 70000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "INSPECTION_MGMT", "code": "INSPECTION_MGMT", "name": "Inspection Management", "package": rid("Package", "ASSET_PACKAGE"), "description": "Scheduled inspection and integrity tracking.", "monthly_price": 8500, "annual_price": 85000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "AUDIT_PLANNING", "code": "AUDIT_PLANNING", "name": "Audit Planning", "package": rid("Package", "AUDIT_PACKAGE"), "description": "Audit scheduling and workpaper management.", "monthly_price": 6000, "annual_price": 60000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "INCIDENT_TRACKING", "code": "INCIDENT_TRACKING", "name": "Incident Tracking", "package": rid("Package", "INCIDENT_PACKAGE"), "description": "Incident capture and root-cause tracking.", "monthly_price": 5500, "annual_price": 55000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "VENDOR_ONBOARDING", "code": "VENDOR_ONBOARDING", "name": "Vendor Onboarding", "package": rid("Package", "VENDOR_PACKAGE"), "description": "Vendor qualification and onboarding workflows.", "monthly_price": 5000, "annual_price": 50000, "currency": rid("Currency", "USD"), "status": "Active"}, {"_key": "DASHBOARD_ANALYTICS", "code": "DASHBOARD_ANALYTICS", "name": "Dashboard & Analytics", "package": rid("Package", "OPS_PACKAGE"), "description": "Operational dashboards and analytics.", "monthly_price": 10000, "annual_price": 100000, "currency": rid("Currency", "USD"), "status": "Inactive"}, ], admin_id) feature = await create_master( db, "Feature", [f_text("code", True), f_text("name", True), f_ref("module", mid("Module"), "name", required=True), f_text("description"), f_number("monthly_price"), f_number("annual_price"), f_ref("currency", mid("Currency"), "name"), f_status()], admin_id, ) master_ids["Feature"] = feature.id records["Feature"] = await insert_records(db, feature, [ {"code": "RISK_REGISTER", "name": "Risk Register", "module": rid("Module", "RISK_MANAGEMENT"), "description": "Create and maintain risk register.", "monthly_price": 5000, "annual_price": 50000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "RISK_REVIEW", "name": "Risk Review", "module": rid("Module", "RISK_MANAGEMENT"), "description": "Review and approve risk activities.", "monthly_price": 4000, "annual_price": 40000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "CONTROL_LIBRARY", "name": "Control Library", "module": rid("Module", "CONTROL_ASSURANCE"), "description": "Control catalog and risk links.", "monthly_price": 3500, "annual_price": 35000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "CONTROL_TESTING", "name": "Control Testing", "module": rid("Module", "CONTROL_ASSURANCE"), "description": "Control self-assessment and testing.", "monthly_price": 4500, "annual_price": 45000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "FRAMEWORK_MAPPING", "name": "Framework Mapping", "module": rid("Module", "FRAMEWORK_MGMT"), "description": "Map controls to framework requirements.", "monthly_price": 3000, "annual_price": 30000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "EVIDENCE_VAULT", "name": "Evidence Vault", "module": rid("Module", "COMPLIANCE_TRACKER"), "description": "Central evidence storage and retrieval.", "monthly_price": 2500, "annual_price": 25000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "ASSET_HIERARCHY", "name": "Asset Hierarchy", "module": rid("Module", "ASSET_REGISTER"), "description": "Type/category hierarchy configuration.", "monthly_price": 2000, "annual_price": 20000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "INSPECTION_SCHEDULING", "name": "Inspection Scheduling", "module": rid("Module", "INSPECTION_MGMT"), "description": "Automated inspection scheduling.", "monthly_price": 3000, "annual_price": 30000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "INCIDENT_REPORTING", "name": "Incident Reporting", "module": rid("Module", "INCIDENT_TRACKING"), "description": "Field incident reporting forms.", "monthly_price": 2500, "annual_price": 25000, "currency": rid("Currency", "USD"), "status": "Active"}, {"code": "VENDOR_SCORECARD", "name": "Vendor Scorecard", "module": rid("Module", "VENDOR_ONBOARDING"), "description": "Vendor HSE and performance scorecards.", "monthly_price": 2000, "annual_price": 20000, "currency": rid("Currency", "USD"), "status": "Inactive"}, ], admin_id) package_assignment = await create_master( db, "Package Assignment", [f_ref("package", mid("Package"), "name", required=True), f_ref("industry", mid("Industry"), "name", required=True), f_boolean("is_default"), f_boolean("is_mandatory"), f_status()], admin_id, ) master_ids["Package Assignment"] = package_assignment.id records["Package Assignment"] = await insert_records(db, package_assignment, [ {"package": rid("Package", "RISK_PACKAGE"), "industry": rid("Industry", "OFFSHORE_DRILLING"), "is_default": True, "is_mandatory": True, "status": "Active"}, {"package": rid("Package", "COMPLIANCE_PACKAGE"), "industry": rid("Industry", "OFFSHORE_DRILLING"), "is_default": True, "is_mandatory": True, "status": "Active"}, {"package": rid("Package", "HSE_PACKAGE"), "industry": rid("Industry", "OFFSHORE_DRILLING"), "is_default": True, "is_mandatory": True, "status": "Active"}, {"package": rid("Package", "ASSET_PACKAGE"), "industry": rid("Industry", "ONSHORE_PRODUCTION"), "is_default": True, "is_mandatory": False, "status": "Active"}, {"package": rid("Package", "RISK_PACKAGE"), "industry": rid("Industry", "ONSHORE_PRODUCTION"), "is_default": True, "is_mandatory": True, "status": "Active"}, {"package": rid("Package", "COMPLIANCE_PACKAGE"), "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "is_default": True, "is_mandatory": True, "status": "Active"}, {"package": rid("Package", "AUDIT_PACKAGE"), "industry": rid("Industry", "MIDSTREAM_PIPELINES"), "is_default": False, "is_mandatory": False, "status": "Active"}, {"package": rid("Package", "VENDOR_PACKAGE"), "industry": rid("Industry", "OILFIELD_SERVICES"), "is_default": True, "is_mandatory": False, "status": "Active"}, {"package": rid("Package", "INCIDENT_PACKAGE"), "industry": rid("Industry", "OFFSHORE_SUPPORT_VESSELS"), "is_default": True, "is_mandatory": True, "status": "Active"}, {"package": rid("Package", "ENTERPRISE_PACKAGE"), "industry": rid("Industry", "POWER_TRANSMISSION"), "is_default": False, "is_mandatory": False, "status": "Inactive"}, ], admin_id) await db.commit() print(f"Created {len(master_ids)} Masters with {sum(len(v) for v in records.values())} sample records.") # ------------------------------------------------------------------ # Tasks — simple 2-node "start -> create_notification" automations # ------------------------------------------------------------------ def notification_task(name: str, subject: str, body: str) -> Task: return Task( tenant_id=TENANT_ID, name=name, nodes=[ {"key": "start", "type": "start", "label": "Start", "next_nodes": [{"to": "notify"}]}, {"key": "notify", "type": "create_notification", "label": "Notify", "subject": subject, "body": body, "next_nodes": []}, ], status="published", created_by=admin_id, ) task_control = notification_task( "Notify Control Owner on Self-Assessment", "Control self-assessment submitted", "A new control self-assessment has been submitted for review.", ) task_risk = notification_task( "Risk Escalation Alert", "New risk reported", "A new risk has been reported and requires triage.", ) task_asset = notification_task( "Asset Registration Confirmation", "New asset registered", "A new asset has been registered in the system.", ) db.add_all([task_control, task_risk, task_asset]) await db.flush() # ------------------------------------------------------------------ # Forms — curated data-entry workflows, wired to the Tasks above # ------------------------------------------------------------------ def lookup_field(name, label, master_key, display_field="name", required=False): return { "name": name, "type": "lookup", "label": label, "required": required, "master_id": str(master_ids[master_key]), "master_display_field": display_field, } form_control_assessment = Form( tenant_id=TENANT_ID, name="Control Self-Assessment", field_definitions=[ lookup_field("control", "Control", "Control", "title", required=True), {"name": "assessed_by", "type": "text", "label": "Assessed By", "required": True}, {"name": "result", "type": "dropdown", "label": "Result", "required": True, "options": ["Pass", "Fail", "Partial"]}, lookup_field("evidence_type", "Evidence Type", "Evidence Type"), {"name": "comments", "type": "textarea", "label": "Comments"}, {"name": "assessment_date", "type": "date", "label": "Assessment Date", "required": True}, ], events=[{"trigger": "on_submit", "action_type": "run_task", "task_id": str(task_control.id)}], status="published", is_current=True, created_by=admin_id, ) form_asset_registration = Form( tenant_id=TENANT_ID, name="Asset Registration", field_definitions=[ lookup_field("asset_type", "Asset Type", "Asset Type", required=True), lookup_field("asset_category", "Asset Category", "Asset Category", required=True), {"name": "asset_name", "type": "text", "label": "Asset Name", "required": True}, lookup_field("unit_type", "Unit Type", "Unit Type"), lookup_field("country", "Country", "Country"), {"name": "status", "type": "dropdown", "label": "Status", "required": True, "options": ["Active", "Inactive"]}, ], events=[{"trigger": "on_submit", "action_type": "run_task", "task_id": str(task_asset.id)}], status="published", is_current=True, created_by=admin_id, ) form_risk_intake = Form( tenant_id=TENANT_ID, name="Risk Assessment Intake", field_definitions=[ lookup_field("risk_category", "Risk Category", "Risk Category", required=True), lookup_field("industry", "Industry", "Industry"), {"name": "description", "type": "textarea", "label": "Risk Description", "required": True}, {"name": "likelihood", "type": "dropdown", "label": "Likelihood", "required": True, "options": ["Low", "Medium", "High"]}, {"name": "impact", "type": "dropdown", "label": "Impact", "required": True, "options": ["Low", "Medium", "High"]}, {"name": "reported_by", "type": "text", "label": "Reported By", "required": True}, ], events=[{"trigger": "on_submit", "action_type": "run_task", "task_id": str(task_risk.id)}], status="published", is_current=True, created_by=admin_id, ) db.add_all([form_control_assessment, form_asset_registration, form_risk_intake]) await db.commit() print("Created 3 Tasks and 3 Forms.") # ------------------------------------------------------------------ # Pages — dashboards built from data_list/kpi_card blocks # ------------------------------------------------------------------ CANVAS_WIDTH = 1400 GAP = 20 def text_block(text, x, y, w, h, style="heading"): return {"id": uid(), "type": "text", "text": text, "text_style": style, "layout": {"x": x, "y": y, "w": w, "h": h}} def kpi_block(master_key, x, y, w, h, color): return {"id": uid(), "type": "kpi_card", "data_source": f"master:{master_ids[master_key]}", "color": color, "layout": {"x": x, "y": y, "w": w, "h": h}} def grid_block(master_key, columns, x, y, w, h): return { "id": uid(), "type": "data_list", "data_source": f"master:{master_ids[master_key]}", "columns": columns, "live_pagination": True, "page_size": 10, "layout": {"x": x, "y": y, "w": w, "h": h}, } def flow_grids(entries: list[tuple], start_y: int, w: int = 660, h: int = 360) -> tuple[list[dict], int]: """entries: list of (master_key, columns, caption). Two columns wide.""" blocks = [] y = start_y for i, (mkey, cols, caption) in enumerate(entries): col = i % 2 row = i // 2 x = col * (w + GAP) row_y = y + row * (h + 40 + GAP) blocks.append(text_block(caption, x, row_y, w, 28, style="subheading")) blocks.append(grid_block(mkey, cols, x, row_y + 32, w, h)) rows_used = (len(entries) + 1) // 2 total_h = y + rows_used * (h + 40 + GAP) return blocks, total_h # Page 1 — Oil & Gas GRC Command Center kpi_specs = [ ("Framework", "Frameworks", "#2563eb"), ("Control", "Controls", "#059669"), ("Risk Category", "Risk Categories", "#d97706"), ("Asset Type", "Asset Types", "#7c3aed"), ] kpi_w = (CANVAS_WIDTH - 3 * GAP) // 4 page1_blocks = [text_block("Oil & Gas GRC Command Center", 0, 0, CANVAS_WIDTH, 40, style="heading")] for i, (mkey, caption, color) in enumerate(kpi_specs): x = i * (kpi_w + GAP) page1_blocks.append(text_block(caption, x, 56, kpi_w, 24, style="subheading")) page1_blocks.append(kpi_block(mkey, x, 84, kpi_w, 100, color)) grid_blocks, _ = flow_grids([ ("Framework", ["code", "name", "type", "status"], "Frameworks"), ("Control", ["code", "title", "category", "status"], "Controls"), ("Risk Category", ["code", "name", "industry", "status"], "Risk Categories"), ("Asset Type", ["code", "name", "status"], "Asset Types"), ], start_y=204) page1_blocks += grid_blocks page1 = Page( tenant_id=TENANT_ID, name="Oil & Gas GRC Command Center", blocks=page1_blocks, canvas_width=CANVAS_WIDTH, status="published", created_by=admin_id, ) # Page 2 — Compliance Framework Explorer page2_blocks = [text_block("Compliance Framework Explorer", 0, 0, CANVAS_WIDTH, 40, style="heading")] grid_blocks2, next_y = flow_grids([ ("Framework", ["code", "name", "type", "status"], "Frameworks"), ("Framework Version", ["version", "framework", "status"], "Framework Versions"), ], start_y=56) page2_blocks += grid_blocks2 grid_blocks2b, next_y2 = flow_grids([ ("Clause", ["clause_number", "title", "framework_version", "status"], "Clauses / Domains"), ("Objective", ["code", "title", "clause", "status"], "Objectives"), ], start_y=next_y) page2_blocks += grid_blocks2b grid_blocks2c, _ = flow_grids([ ("Control Point", ["code", "title", "result_type", "status"], "Control Points"), ], start_y=next_y2) page2_blocks += grid_blocks2c page2 = Page( tenant_id=TENANT_ID, name="Compliance Framework Explorer", blocks=page2_blocks, canvas_width=CANVAS_WIDTH, status="published", created_by=admin_id, ) # Page 3 — Asset & Unit Register page3_blocks = [text_block("Asset & Unit Register", 0, 0, CANVAS_WIDTH, 40, style="heading")] grid_blocks3, _ = flow_grids([ ("Unit Type", ["code", "name", "industry", "status"], "Unit Types"), ("Section", ["code", "name", "unit_type", "status"], "Sections"), ("Asset Type", ["code", "name", "status"], "Asset Types"), ("Asset Category", ["code", "name", "industry", "status"], "Asset Categories"), ], start_y=56) page3_blocks += grid_blocks3 page3 = Page( tenant_id=TENANT_ID, name="Asset & Unit Register", blocks=page3_blocks, canvas_width=CANVAS_WIDTH, status="published", created_by=admin_id, ) db.add_all([page1, page2, page3]) await db.commit() print("Created 3 Pages.") print("Done.") if __name__ == "__main__": asyncio.run(main())