JodGig Org Domain — Data Analysis
This document captures the data analysis performed against the jodgig_2026_clean MySQL database (dumped on 10 March 2026) to inform architectural decisions for the JodApp Org domain design.
Each section includes the context (why the question was asked), the data, and any assumptions made.
1. Company Structure Patterns
Context: We needed to determine whether Org::Company should represent a legal entity or an operational entity. The key question was: how many companies share the same business registration number (UEN)? This would reveal whether "business units" (divisions within a single legal entity) are a common pattern.
Data:
| Metric | Value |
|---|---|
| Total active companies (non-deleted) | 315 |
| Unique registration numbers | 274 |
| Companies sharing a registration number with at least one other | 41 (13%) |
| Distinct registration numbers shared by multiple companies | 15 |
Top shared registration numbers:
| Registration No | Company Count | Companies |
|---|---|---|
| S83CS0191L | 21 | NTUC Fairprice divisions (Finest, Supermarkets South, Central, East, North, etc.) |
| 199600354R | 5 | Millennium Hotels (Copthorne King's, Grand Copthorne, M Hotel, Orchard Hotel, Millennium & Copthorne) |
| 200002490D | 4 | Minor Food Group (ThaiExpress, Poulet, Xin Wang, Western) |
| 194700005R | 3 | Cold Storage / DFI Retail Group |
| 199300545M | 3 | Grocery Logistics of Singapore (BNDC, JKDC, Picking) |
Assumption: status = 1 means active in the legacy system. Companies with deleted_at IS NULL are considered non-deleted.
Design Impact: This data drove the decision to keep Org::Company as the operational entity (not the legal entity), with child companies sharing a parent's registration_no via a partial unique index.
2. NTUC Fairprice — Enterprise Division Pattern
Context: NTUC Fairprice is the largest client on the platform and the canonical example of the enterprise division pattern. We needed to understand how their structure works to validate the parent-child company model.
Data:
27 total NTUC company records (including inactive). Key active divisions:
| Division Name | Outlets | Users | Status |
|---|---|---|---|
| NTUC Fairprice Co-operative Ltd (Finest) | 45 | 131 | Active |
| NTUC Fairprice Co-Operative Limited (Supermarkets NE, NW, E, Central) | 66 | 167 | Inactive |
| NTUC Fairprice Co-operative Ltd Supermarkets (Central) | 21 | 44 | Active |
| NTUC Fairprice Co-operative Ltd Supermarkets (Central East) | 18 | 35 | Active |
| NTUC Fairprice Co-operative Ltd Supermarkets (East) | 19 | 38 | Active |
| NTUC Fairprice Co-operative Ltd Supermarkets (North) | 15 | 30 | Active |
| NTUC Fairprice Co-operative Ltd Supermarkets (Northeast) | 19 | 39 | Active |
| NTUC Fairprice Co-operative Ltd Supermarkets (Northwest) | 17 | 35 | Active |
| NTUC Fairprice Co-operative Ltd Supermarkets (South) | 18 | 38 | Active |
| NTUC Fairprice Co-operative Ltd Supermarkets (West) | 23 | 45 | Active |
All share UEN S83CS0191L.
NTUC Fairprice (South) — Sample outlets (15 active): FairPrice Bukit Ho Swee, FairPrice Bukit Merah Central, FairPrice Buona Vista CC, FairPrice Chinatown Point, FairPrice Dawson Road, FairPrice Depot Heights, FairPrice Jalan Bukit Merah, FairPrice Kim Tian, FairPrice NUH Medical Centre, FairPrice PSA, FairPrice Redhill, FairPrice Silat Ave, FairPrice Stirling Road, FairPrice Tanjong Pagar Plaza, FairPrice Telok Blangah.
Restructuring evidence: Several divisions are marked with "(Old)" suffixes and inactive status (e.g., "Central - Old", "Northeast - Old", "Northwest - Old"), replaced by new divisions. The original broad divisions (e.g., "Supermarkets NE, NW, E, Central") were split into individual regional divisions.
Assumption: status = 0 means inactive (disabled) in the legacy companies table. Inactive divisions represent previous organizational structures that were replaced.
Design Impact: Confirmed that divisions need their own billing accounts, users, outlets, and jobs. Restructuring happens at the client's request. The parent-child company model with shared registration_no accommodates this pattern.
3. Millennium Hotels — Multi-Property Pattern
Context: We needed to check if HQ managers span multiple companies, and how hotel groups model their properties.
Data:
| Company Name | Registration No | HQ Users | AREA Users | LOCATION Users |
|---|---|---|---|---|
| Millennium and Copthorne International Limited | 199600354R | 1 | 0 | 0 |
| Grand Copthorne Waterfront Hotel Singapore | 199600354R | 1 | 0 | 0 |
| Orchard Hotel Singapore | 199600354R | 1 | 1 | 1 |
| M Hotel Singapore | 199600354R | 1 | 0 | 0 |
| Copthorne King's Hotel Singapore | 199600354R | 1 | 0 | 0 |
5 company records sharing UEN 199600354R. Each hotel property is a separate company record with its own HQ user. No SUPER_HQ_EXTERNAL users span across these properties. No cross-company role assignments.
Assumption: Millennium Hotels manages each property independently on the platform, rather than using a centralized management structure.
Design Impact: Validated that the multi-property pattern works the same as the NTUC division pattern — separate company records per operational entity, shared registration number.
4. User Type Distribution (Global)
Context: We needed to understand the scale and composition of users on the platform to inform role design.
Data:
| User Type | Count | % of Total |
|---|---|---|
| APP (gig workers) | 97,368 | 96.7% |
| LOCATION | 1,843 | 1.8% |
| AREA | 985 | 1.0% |
| HQ | 310 | 0.3% |
| SUPER_HQ_EXTERNAL | 70 | 0.07% |
| INTERNAL | 33 | 0.03% |
| SUPER_HQ_INTERNAL | 23 | 0.02% |
| SUPER_ADMIN | 8 | 0.01% |
| USER_ADMIN | 1 | <0.01% |
Total: ~100,642 users.
NTUC-specific distribution:
| User Type | Count |
|---|---|
| AREA | 471 |
| LOCATION | 471 |
| APP (gig workers) | 380 |
| HQ | 27 |
| SUPER_HQ_INTERNAL | 7 |
| SUPER_HQ_EXTERNAL | 3 |
| SUPER_ADMIN | 1 |
Assumption: is_deleted = 0 used to filter active users (the users table does not have deleted_at).
Design Impact: Employer-facing roles (HQ, AREA, LOCATION) represent ~3.1% of all users. LOCATION is the largest employer role. The new system needs to prioritize the AREA/LOCATION user experience over HQ.
5. Employer Login Activity
Context: We needed to validate the hypothesis that HQ managers are not the primary platform users, and that outlet-level managers drive daily usage. This informs how much weight to give HQ features vs outlet-level features.
Data (last 30 days from database dump, i.e., 8 Feb 2026 — 10 Mar 2026):
| Role | Total | Active (30d) | % Active | Never Logged In | % Never |
|---|---|---|---|---|---|
| LOCATION | 1,843 | 205 | 11.1% | 1,172 | 63.6% |
| AREA | 985 | 82 | 8.3% | 575 | 58.4% |
| HQ | 310 | 23 | 7.4% | 136 | 43.9% |
NTUC-specific login activity:
| Role | Total | Active (30d) | Active (90d) | Never Logged In |
|---|---|---|---|---|
| AREA | 431 | 51 (11.8%) | 66 | 196 (45.5%) |
| LOCATION | 430 | 64 (14.9%) | 71 | 242 (56.3%) |
| HQ | 21 | 0 (0%) | 0 | 1 |
NTUC HQ Managers — Last Login Dates: Most recent NTUC HQ login was 27 June 2024 — approximately 21 months before the database dump. All 21 NTUC HQ accounts are completely dormant. 20 are stale, 1 has never logged in.
Active HQ users are concentrated in small F&B businesses: The top most-recently-active HQ managers are from: Jiang's Cafe, Tung Lok Millennium, Savornana, Vision Mission Cleaning, Abaavo Group, Dim Sum Pte Ltd — all small/mid-size companies.
Companies with active employer users:
- 47 companies have at least one active LOCATION or AREA user (last 30 days)
- 23 companies have at least one active HQ user (last 30 days)
Assumption: "Active" means last_login_at >= '2026-02-08' (30 days before the 10 March 2026 dump date). "Never logged in" means last_login_at IS NULL.
Design Impact: Confirmed that the platform is used primarily at the outlet level. HQ engagement is the weakest tier — 7.4% globally, 0% for the largest client. The role model should prioritize area_manager and outlet_manager UX, with hq_manager as a governance/admin role rather than a daily-use role.
6. AREA Manager Cross-Company Scoping
Context: We needed to determine if AREA managers can manage outlets across multiple companies (business units). This determines whether role scoping needs to support cross-company access.
Data:
- AREA users with locations belonging to different companies: 0 (zero)
- Every AREA user's assigned locations belong to the same
company_idas the user - No cross-company assignment exists in the data
NTUC AREA manager distribution (top by locations managed):
| User ID | Company | Locations Managed |
|---|---|---|
| 32464 | Unity Pharmacy NTUC Fairprice | 10 |
| 32465 | Unity Pharmacy NTUC Fairprice | 8 |
| 44181 | NTUC Enterprise | 6 |
| 51764 | NTUC Fairprice Supermarkets (East) | 2 |
| 51707 | NTUC Fairprice Supermarkets (South) | 2 |
Each AREA manager belongs to a single company and manages 1–10 outlets, all within that same company.
Assumption: The locations.area_user_id column links an AREA user to the locations they manage. We checked if any user's assigned locations had a different company_id than the user's own.
Design Impact: Confirmed that role scoping is strictly single-company. Org::OutletAssignment does not need to support cross-company references. Multi-company access is handled at the Org::Membership level (one membership per company).
7. SUPER_HQ_EXTERNAL — Multi-Company Access
Context: We needed to understand how the legacy system handles users who access multiple companies, to inform the design of Org::Membership.
Data:
70 SUPER_HQ_EXTERNAL users exist. Only 3 actually span more than 1 company via the user_company pivot table:
| User ID | Primary Company | Linked Companies |
|---|---|---|
| 75029 | Cold Storage Singapore | Cold Storage Singapore, Giant |
| 19471 | Nanbantei Japanese Restaurant | Nanbantei Japanese Restaurant, Shinjuku Restaurant |
| 88935 | Shin Katsu Pte Ltd | Shin Katsu, Butterwerkz Group |
The remaining 67 SUPER_HQ_EXTERNAL users are linked to only 1 company.
HQ users in the user_company pivot: Only 1 HQ user (user 74659) appears in the pivot, linked to their own company only.
Assumption: The user_company table with deleted_at IS NULL represents active multi-company links.
Design Impact: Multi-company access is extremely rare (3 users). The Org::Membership model (one membership per company per user) handles this naturally — no special role needed. SUPER_HQ_EXTERNAL can be replaced by creating multiple Org::Memberships for the same user.
8. Job Ownership
Context: We needed to determine which entity owns gig jobs — the legal entity, the division/business unit, or the outlet. This affects where jobs are created and scoped in the new system.
Data:
The jod_jobs table has both company_id and location_id foreign keys.
NTUC Fairprice job counts by division (UEN S83CS0191L):
| Division | Job Count |
|---|---|
| NTUC Fairprice Co-operative Ltd (Finest) | 26,783 |
| NTUC Fairprice Co-operative Ltd Supermarkets (South) | 11,325 |
| NTUC Fairprice Co-operative Ltd Supermarkets (Northwest) | 11,076 |
| NTUC Fairprice Co-Operative Limited (Supermarkets NE, NW, E, Central) | 10,370 |
| NTUC Fairprice Co-operative Limited Supermarkets (N, W & S) | 9,546 |
| NTUC Fairprice Co-operative Ltd Supermarkets (Central) | 9,173 |
| NTUC Fairprice Co-operative Ltd Supermarkets (North) | 9,082 |
| NTUC Fairprice Co-operative Ltd Supermarkets (Northeast) | 8,125 |
| NTUC Fairprice Co-operative Ltd Supermarkets (Central East) | 8,089 |
| NTUC Fairprice Co-operative Ltd Supermarkets (East) | 6,028 |
| NTUC Fairprice Co-operative Ltd Supermarkets (West) | 4,248 |
| ... (4 more divisions) | ... |
Total: ~117,859 jobs across all NTUC Fairprice divisions.
Each job references both a company_id (the division) and a location_id (the outlet). Jobs are owned at the division level, not the legal entity level.
Assumption: All jobs in the jod_jobs table are included regardless of status or deletion.
Design Impact: Confirmed that jobs should belong to the Org::Company (division) + Org::Outlet. The legal entity (root company) does not own jobs directly.
9. Permissions and Role Analysis
Context: We needed to determine whether per-company permission customization is necessary in the new system, or if standardized roles with fixed permissions are sufficient.
Data:
Permission system structure:
- 277 individual permissions (granular, per-API-endpoint level)
- 13 roles
company_role_permissionstable: 2,205 records across 315 companies and 7 partner-facing roles
Are permissions customized per company?
| Role | Companies | Distinct Permission Sets |
|---|---|---|
| HQ manager | 315 | 7 |
| Area manager | 315 | 7 |
| Location manager | 315 | 7 |
| HQ - Read only | 315 | 3 |
| Area + job approve | 315 | 2 |
| Super HQ External | 315 | 2 |
| Super HQ Internal | 315 | 2 |
For HQ/Area/Location roles, only 7 distinct permission sets exist across 315 companies. A sample of 10 companies confirmed identical permission JSON for the same role.
Capability comparison across roles:
| Capability | HQ Manager | Area Manager | Location Manager |
|---|---|---|---|
| Permission count | 131 | 98 | 96 |
| Create/manage jobs | Yes | Yes | Yes |
| View candidates | Yes | Yes | Yes |
| View credit history | Yes | Yes | Yes |
| Generate QR codes | Yes | Yes | Yes |
| View roster | Yes | Yes | Yes |
| Manage users | Yes | No | No |
| Manage outlets | Yes | No | No |
| Manage job templates | Yes | No | No |
| Assign credits | Yes | No | No |
| Approve jobs | Yes | Via AM_JOB_APPROVAL | No |
| Manage roles/features | Yes | No | No |
Area manager and Location manager have nearly identical permissions (98 vs 96). The primary difference is scope (which outlets they can see), not capabilities.
Assumption: Permission sets were compared by the exact JSON content of the permissions column in company_role_permissions. Small variations (7 out of 315) are likely from different onboarding times or one-off adjustments, not intentional per-company customization.
Design Impact: Per-company permission customization is not needed. Three standardized roles (hq_manager, area_manager, outlet_manager) with fixed capabilities and outlet-based scoping are sufficient. The difference between area and outlet manager is scope, not permissions.
10. AM_JOB_APPROVAL — Job Approval Feature Usage
Context: The legacy system has a special role variant AM_JOB_APPROVAL (role_id=15) for area managers who can approve pending jobs. We needed to determine if this warrants a separate role in the new system or can be modeled as a feature/setting.
Data:
How it works (from code analysis):
- Job approval is controlled by a per-location flag:
locations.job_approval_required - When enabled, jobs created at that location enter
PENDINGstatus (code 5) - HQ managers or AREA users with role_id=15 can manually approve (→
OPENING, code 2) or reject (→NO_APPROVE, code 6) - Approval is recorded via
approved_datetimeon thejod_jobstable - Notifications (email + SMS) are sent on both approval request and approval/rejection
Overall adoption:
| Metric | Value |
|---|---|
| Total jobs in system | 447,037 |
| Jobs with approval | 2,589 (0.58%) |
| Users with AM_JOB_APPROVAL role | 41 |
| Companies with AM_JOB_APPROVAL users | 31 |
| Companies that actually approve jobs | 3 |
The 3 companies that use job approval:
| Company | Total Approved | Approval Period | Avg/Month | Locations with Approval ON |
|---|---|---|---|---|
| The Robertson House | 2,479 (96.4%) | Jan 2024 — Mar 2026 (26 months) | ~80 | 9 of 9 outlets |
| PALOMAR TECHNOLOGIES | 92 (3.6%) | Nov 2024 — Mar 2026 (16 months) | ~6 | 1 of 1 outlet |
| Nikuya Tanaka | 1 (<0.1%) | Dec 2025 (single day) | — | 0 (turned off) |
The Robertson House — Monthly approval volume (last 12 months):
| Month | Approvals |
|---|---|
| 2025-03 | 36 |
| 2025-04 | 55 |
| 2025-05 | 107 |
| 2025-06 | 75 |
| 2025-07 | 70 |
| 2025-08 | 93 |
| 2025-09 | 115 (peak) |
| 2025-10 | 76 |
| 2025-11 | 88 |
| 2025-12 | 68 |
| 2026-01 | 81 |
| 2026-02 | 94 |
| 2026-03 | 37 (partial — dump was 10 March) |
The Robertson House user structure:
- 1 HQ user (last login: May 2024 — dormant)
- 5 AREA users (3 with AM_JOB_APPROVAL role, 2 regular)
- 16 LOCATION users
- Primary approver: user 60418 (AREA, AM_JOB_APPROVAL) — last login 8 March 2026, batch-approves jobs
Approval pattern: Batch approvals — multiple jobs approved within seconds of each other, indicating a "select all and approve" workflow. Job titles concentrated: Bartender, PT Banquet Captain, Banquet Server, F&B Service Crew, Process Support Operator.
Assumption: "Uses job approval" means at least one job with approved_datetime IS NOT NULL for that company. The 28 companies with AM_JOB_APPROVAL users but zero approved jobs likely had the role assigned as part of a default setup but never used the feature.
Design Impact: Job approval is a per-outlet setting, not a role. In the new system, is_job_approval_required lives on Org::Outlet. Any hq_manager or area_manager with access to that outlet can approve. This eliminates the need for a separate AM_JOB_APPROVAL role variant. The Robertson House's batch-approve workflow should be supported in the UI.
Data Source
- Database:
jodgig_2026_clean(MySQL) - Dump Date: 10 March 2026
- All date-relative calculations (e.g., "last 30 days") are anchored to 10 March 2026, not the current date
- Queries run: April 2026 against the static dump