Programme Seats Without Organisation

Overview

This feature allows users to be assigned to programmes without being part of an organization. These seats exist in a "parallel flow" outside the normal organization/licensing logic.

Use Case

For users who:

• Exist in the system but have no organization
• Have no licenses
• Need to be linked to programmes with ignition dates for reporting purposes

Database Changes

The organisation_programme_seats table now has a nullable organisation_id column:

-- organisation_id can now be NULL
-- Foreign key uses CASCADE on delete (if org is deleted, seat is deleted)
-- Unique constraint includes ignition_date:
--   (tenant_id, organisation_id, programme_id, user_id, ignition_date)

Important: The foreign key still uses CASCADE on delete. This means:

• If an organisation IS set and gets deleted, the seat is also deleted
• NULL organisation_id is only for seats that were never part of an organization

Creating Seats Without Organisation

Using the User Model

$user = User::find($userId);

$seat = $user->assignToProgrammeWithoutOrganisation(
    programmeId: 1,
    tenantId: 1,
    contractStartDate: '2025-01-01',
    ignitionDate: '2025-02-01',
    isActive: true
);

Direct Creation

OrganisationProgrammeSeat::create([
    'tenant_id' => 1,
    'organisation_id' => null, // Explicitly NULL
    'programme_id' => 1,
    'user_id' => $userId,
    'contract_start_date' => '2025-01-01',
    'ignition_date' => '2025-02-01',
    'is_active' => true,
]);

Activity Logging

Seats with an organization:

• Logged to: ActivityLoggerLogName::ORGANISATION
• Subject: The organisation

Seats without an organization:

• Logged to: ActivityLoggerLogName::PROGRAMME
• Subject: The programme

Licensing Logic (OrganisationLicenceHelper)

No changes needed! The helper methods automatically ignore seats with NULL organisation_id because they filter by organisation. This is the desired behavior - these seats exist outside the licensing flow.

Reporting Queries

When querying organisation_programme_seats for reporting, the CTE needs to handle NULL organisation_id. Here's the updated pattern:

Original Query (Organisation-only)

WITH entrep_progs AS (
    SELECT
        pu1.tenant_id,
        pu1.organisation_id,  -- Always has a value
        pu1.user_id,
        ...
    FROM organisation_programme_seats AS pu1
    ...
)

Updated Query (With and Without Organisation)

WITH entrep_progs AS (
    SELECT
        pu1.tenant_id,
        pu1.organisation_id,  -- Can be NULL now
        pu1.user_id,
        ...
    FROM organisation_programme_seats AS pu1
    LEFT JOIN programmes AS p ON p.id = pu1.programme_id
    LEFT JOIN organisation_programme_seats AS pu2 ON pu1.user_id = pu2.user_id
        AND pu1.ignition_date < pu2.ignition_date
    LEFT JOIN organisation_programme_seats AS pu3 ON pu1.user_id = pu3.user_id
        AND pu1.ignition_date <= pu3.ignition_date
    WHERE pu3.ignition_date <= NOW()
    GROUP BY pu1.tenant_id, pu1.organisation_id, pu1.user_id, pu1.programme_id, pu1.ignition_date
)
SELECT
    u.name,
    -- Handle NULL organisation
    COALESCE(o.name, 'No Organisation') AS company_name,
    ...
FROM engauge_session_user AS esu
LEFT JOIN entrep_progs AS ep ON ...
LEFT JOIN organisations AS o ON o.id = ep.organisation_id  -- Will be NULL for seats without org
...
WHERE ep.user_id IS NOT NULL  -- Make sure we have a user match

Key Changes for Queries:

1. GROUP BY: Must include organisation_id even when NULL (MySQL groups NULLs separately)
2. LEFT JOIN organisations: Join will return NULL for seats without organisations
3. COALESCE: Use to provide default values when organisation is NULL
4. Filtering: Use organisation_id IS NULL to specifically find seats without organisations

Widgets and Admin Interfaces

Filament widgets that are organization-scoped (like OrganisationProgrammeSeatsTable) will not show seats with NULL organisation_id because they filter by organisation_id. This is correct behavior.

To view seats without organizations, you would need a separate admin interface or report that specifically queries for:

WHERE organisation_id IS NULL

Testing

See tests/Unit/Models/OrganisationProgrammeSeatTest.php for examples testing:

• Creating seats without organisation
• Activity logging for seats without organisation
• Unique constraints with NULL organisation_id