""" Plain-language description of every table, grouped by business area. Used by the g3b4c5d6e7f8 migration to write COMMENT ON TABLE, so the description of a table is visible in pgAdmin (Properties > Comment) and in `\\dt+` in psql. Keep this file in step with the models: when you add a table, add a line here and re-run `python scripts/apply_table_comments.py`. """ TABLE_CATALOG: dict[str, dict[str, str]] = { 'Users and access': { 'login_users': 'Every person who can log in (staff). Holds name, employee code, email, password hash and role.', 'role_master': 'Roles (ADMIN, QA_INSPECTOR, OPERATOR, ...). A user has one role.', 'module_master': 'Application modules that permissions are granted on (PRODUCTION, QA, YARD, ...).', 'role_module_permission': 'What each role may do per module (view / create / edit / delete / approve).', 'user_permission_override': 'Per-user exceptions to the role permissions (grant or revoke one action).', 'station_user_assignment': 'Which user works at which station, as OPERATOR or QA. Drives My Station and QA Inspection.', }, 'Dropdown lists (admin-managed)': { 'master_list_registry': 'Index of the dropdown lists: list code, table name, label, description, display order.', 'production_line_master': 'Dropdown list: production lines.', 'ac_make_master': 'Dropdown list: AC makes fitted to buses.', 'interior_work_master': 'Dropdown list: interior work types.', 'yard_location_master': 'Dropdown list: yard locations used on chassis inward.', 'machine_type_master': 'Dropdown list: machine types (maintenance).', 'incoming_material_category_master': 'Dropdown list: incoming material categories (maintenance).', 'rental_vendor_master': 'Dropdown list: equipment rental vendors (maintenance).', 'rental_equipment_type_master': 'Dropdown list: rental equipment types (maintenance).', 'qa_verification_method_master': 'Dropdown list: QA verification methods (visual, gauge, ...).', 'production_hold_type_master': 'Dropdown list: reasons a chassis is put on hold in production.', 'payment_terms_master': 'Dropdown list: payment terms (sales / procurement).', 'region_master': 'Dropdown list: sales regions.', 'enquiry_type_master': 'Dropdown list: sales enquiry types.', 'enquiry_source_master': 'Dropdown list: sales enquiry sources.', 'chassis_document_type_master': 'Dropdown list: document types collected at chassis arrival.', 'gate_pass_item_master': 'Dropdown list: items printed on the dispatch gate pass (with default quantity and print row).', 'thermal_check_item_master': 'Dropdown list: checkpoints on the dispatch thermal checksheet.', }, 'Plant layout': { 'chassis_location_master': 'Macro factory locations in order (Chassis Inward, Production, FVI, PDI, Dispatch, ...).', 'station_master': 'Production stations, their sequence number, location and line. The order chassis move through.', 'store_master': 'Stores (warehouses) that hold raw material.', 'yard_checkpoint_master': 'Named physical stops a chassis passes (yard, unit gate, main gate, dispatched).', 'yard_slot_master': 'Parking bays in the yard and their occupancy.', }, 'Chassis and yard': { 'chassis_master': 'One row per chassis (bus). Customer, model, current status, current station / location, key dates.', 'chassis_status_log': 'History of every status change on a chassis.', 'chassis_movement_log': 'History of physical movements between yard checkpoints.', 'chassis_movement_checklist_result': 'Answers to the checklist filled on each movement.', 'movement_checklist_item_master': 'Checklist questions asked when a chassis moves.', 'chassis_arrival_document': 'Documents received with a chassis on arrival (uploaded files).', 'chassis_qa_checklist_result': 'Answers to the yard QA checklist on chassis inward.', 'yard_qa_checklist_item_master': 'Questions on the yard QA checklist.', 'customer_supplied_part': 'Parts sent by the customer with the chassis (FAPS, AC, seats, ...).', 'chassis_store_handover': 'Header of the store hand-over sheet for a chassis (one per chassis).', 'chassis_store_handover_part': 'Parts listed on a chassis store hand-over sheet.', 'store_handover_part_master': 'Standard parts that appear on the store hand-over sheet.', 'vehicle_model_master': 'Chassis / vehicle models (model number and name).', }, 'Production': { 'chassis_station_log': 'Check-in and check-out of a chassis at each station, with timings and operator.', 'chassis_station_handoff': 'Hand-over of a chassis from one station to the next; the QA gate (AWAITING_QA, QA_PASSED, RELEASED, RECEIVED).', 'production_hold': 'Holds placed on a chassis in production, with type, reason and release.', 'delay_reason_master': 'Reasons recorded when a station runs over its standard time.', 'production_report_manual_entry': 'Columns of the production report that staff type in by hand, one row per chassis.', 'chassis_bom_snapshot': 'Frozen bill of materials for one chassis: part, quantity, station, reserved / consumed quantity.', }, 'Quality (QA)': { 'qa_checksheet_template': 'QA checksheet definitions (name, version, applicability flags).', 'qa_checksheet_section': 'Sections within a checksheet template.', 'qa_checkpoint_item': 'Individual checkpoints within a section (what to check, specification, mandatory flag).', 'qa_station_template_map': 'Which checksheet templates apply at which station.', 'qa_checksheet_instance': 'A checksheet being filled for one chassis at one station, with status and overall remarks.', 'qa_checkpoint_response': 'Answer to one checkpoint on an instance (OK / NOT OK / NA, value, remark).', 'qa_response_photo': 'Photos attached to a checkpoint answer.', 'qa_section_remark': 'Inspector remark on a whole section of a checksheet instance.', 'qa_checksheet_signoff': 'Sign-off of a checksheet instance (who, when, outcome).', 'qa_shortage_log': 'Parts found short during QA, and their resolution.', 'station_qa_checklist_item_master': 'Older per-station QA checklist questions (kept for history).', 'chassis_station_qa_result': 'Older per-station QA results per chassis scan (kept for history).', }, 'Dispatch': { 'dispatch_gate_pass': 'Gate pass issued when a bus leaves the plant.', 'dispatch_gate_pass_item': 'Items listed on a gate pass, with quantities.', 'dispatch_thermal_checksheet': 'Thermal (AC) checksheet filled before dispatch.', 'dispatch_thermal_checksheet_item': 'Readings / answers on a thermal checksheet.', }, 'Products, BOM and configuration': { 'product_master': 'Sellable products (body type + segment + model combinations).', 'bom_master': 'Bill of materials header per product.', 'bom_detail': 'Parts and quantities in a BOM, and the station that consumes them.', 'special_feature_master': 'Optional features a customer can choose (door type, AC, dicky, ...).', 'special_feature_option_master': 'Choices available for each special feature.', 'special_feature_bom': 'Extra parts required when a feature option is chosen.', 'feature_segment_map': 'Which customer segments see a given special feature.', 'body_type_master': 'Bus body types.', 'customer_segment_master': 'Customer segments (school, staff, tourist, ...).', 'model_body_type_map': 'Which body types are offered on which chassis model.', 'model_segment_map': 'Which segments are offered on which chassis model.', 'seating_layout_master': 'Seating layouts (seat count and arrangement, with image).', 'seat_model_configuration': 'Valid seat model, supplier and dimensions for a body type, seat type and layout size.', 'rto_master': 'RTO (vehicle registration office) list.', }, 'Sales and customers': { 'customer_master': 'Customers and dealers (company, contact, GST, type, region).', 'customer_type_master': 'Customer types (DEALER, DIRECT, ...).', 'customer_contact': 'Additional contact people for a customer.', 'client_master': 'End clients bought for through a dealer (sales side).', 'dealer_client_map': 'Which dealer serves which client.', 'transport_master': 'Transporters used for delivery.', 'sales_enquiry': 'Sales enquiries (lead pipeline).', 'sales_activity_log': 'Activity history on sales enquiries and orders.', 'sales_vehicle_template': 'Saved vehicle configurations for internal sales use.', 'sales_vehicle_template_feature': 'Feature choices stored in a sales vehicle template.', 'payment_receipt': 'Payments received from customers.', 'customer_payment_log': 'Payment ledger entries per customer / order.', 'goodwill_approval_log': 'Approvals for goodwill (discount / free-of-cost) requests.', 'da_lite_hold_log': 'Holds and releases on an order under the DA-Lite process.', 'service_order': 'After-sales service orders.', 'spare_parts_order': 'Spare parts orders from customers.', 'spare_parts_order_detail': 'Lines of a spare parts order.', }, 'Dealer portal': { 'dealer_order': 'Orders placed by dealers (header, status, totals).', 'dealer_order_vehicle': 'Vehicles (configured buses) within a dealer order.', 'dealer_order_vehicle_feature': 'Feature choices for a vehicle on a dealer order.', 'dealer_order_vehicle_chassis': 'Chassis allocated to a vehicle line on a dealer order.', 'dealer_order_activity_log': 'History of actions on a dealer order.', 'dealer_client_master': "A dealer's own clients, shared by the dealer portal and the sales portal.", 'dealer_request': 'Requests raised by dealers (new feature, change, query).', 'dealer_vehicle_template': 'Vehicle configurations saved by a dealer.', 'dealer_vehicle_template_feature': 'Feature choices stored in a dealer vehicle template.', 'dealer_portal_log': 'Dealer portal login / session log.', 'dealer_portal_action_log': 'Actions performed by dealers in the portal.', 'dealer_portal_admin_action_log': 'Actions performed by staff on dealer portal data.', }, 'Raw material, stores and procurement': { 'rm_master': 'Raw material (part) master: part number, description, unit, group, reorder levels.', 'material_type_master': 'Raw material types.', 'rm_store_mapping': 'Which store holds which raw material.', 'rm_vendor_mapping': 'Approved vendors for each raw material, with rate and lead time.', 'rm_inventory': 'Current stock of each raw material per store.', 'rm_inventory_log': 'Every stock movement (receipt, issue, adjustment) with before / after quantity.', 'rm_consumption_log': 'Material consumed against a chassis / station.', 'rm_receiving_log': 'Goods received at stores against purchase orders.', 'grn_detail': 'Goods receipt note lines.', 'rm_purchase_request': 'Purchase requests (header).', 'rm_purchase_request_detail': 'Lines of a purchase request.', 'rm_purchase_order': 'Purchase orders to vendors (header).', 'rm_purchase_order_detail': 'Lines of a purchase order.', 'rm_po_history': 'Historic purchase prices / orders per raw material.', 'po_status_master': 'Purchase order statuses.', 'procurement_source_master': 'Procurement sources (local, import, customer supplied, ...).', 'monthly_procurement_plan': 'Monthly procurement plan shared with a vendor (header).', 'monthly_procurement_plan_line': 'Lines of a monthly procurement plan.', 'vendor_master': 'Suppliers, with contacts and vendor portal login.', }, 'Vendor portal': { 'asn_log': 'Advance shipping notices sent by vendors (header).', 'asn_detail': 'Lines of an advance shipping notice.', 'po_tracking_log': 'Status updates on purchase orders from the vendor side.', 'inspection_document': 'Inspection documents uploaded by vendors.', 'rejection_log': 'Material rejected at incoming inspection (header).', 'rejection_log_detail': 'Lines of a material rejection.', 'rejection_photo': 'Photos of rejected material.', 'rejection_reason_master': 'Reasons for rejecting material.', 'debit_note': 'Debit notes raised on vendors.', 'ncr_log': 'Non-conformance reports raised on vendors.', 'vendor_dispute_log': 'Disputes raised by vendors and their resolution.', 'vendor_scorecard': 'Vendor performance scores per period.', 'notification_log': 'Notifications sent to vendors / users.', 'vendor_portal_log': 'Vendor portal login / session log.', 'vendor_portal_action_log': 'Actions performed by vendors in the portal.', 'vendor_portal_admin_action_log': 'Actions performed by staff on vendor portal data.', }, 'Maintenance': { 'maintenance_asset': 'Machines and equipment that are maintained.', 'maintenance_record': 'Breakdown and maintenance jobs on an asset.', 'maintenance_record_action': 'Actions taken on a maintenance job.', 'maintenance_checklist_item': 'Checklist items for preventive maintenance.', 'maintenance_pm_schedule': 'Preventive maintenance schedule per asset.', 'maintenance_incoming_material': 'Material received for maintenance use.', 'maintenance_daily_consumable_stock': 'Daily stock of maintenance consumables.', 'maintenance_monthly_stock': 'Monthly stock position of maintenance items.', 'maintenance_machine_consumption_cost': 'Monthly consumption cost per machine.', 'maintenance_equipment_purchase': 'Equipment purchased for maintenance.', 'maintenance_equipment_rental': 'Equipment rented, with vendor, period and cost.', 'maintenance_diesel_record': 'Diesel usage records.', 'maintenance_lpg_gas_record': 'LPG gas usage records.', 'maintenance_solar_record': 'Solar generation records.', }, } def table_descriptions() -> dict[str, str]: """Flat {table_name: description}.""" return {t: d for group in TABLE_CATALOG.values() for t, d in group.items()}