Selecting unique test data from tenants - RodrigoMattosoSilveira/EnterpriseRemoteSystems GitHub Wiki

There is currently no cross-tenant comparison screen in the UI. For local manual testing, the simplest method is a read-only SQLite report that discovers the differences for you.

From the ERS project root:

#!/usr/bin/env bash

set -euo pipefail

source /tmp/ers-b28c-manual-env.sh

REPORT_DIR="/tmp"
OUTPUT_FILE="${REPORT_DIR}/tenant-differences.txt"

mkdir -p "$REPORT_DIR"

sqlite3 -header -column backend/data/app.db >"$OUTPUT_FILE" <<SQL
WITH tenant_inventory AS (
    SELECT
        p.tenant_id,
        'People' AS area,
        lower(p.email) AS comparison_key,
        p.nickname || ' — ' ||
        p.first_name || ' ' || p.last_name ||
        ' — ' || p.email AS item
    FROM people p

    UNION ALL

    SELECT
        c.tenant_id,
        'Collaborators' AS area,
        lower(p.email) AS comparison_key,
        p.nickname || ' — ' ||
        p.first_name || ' ' || p.last_name ||
        CASE
            WHEN c.closed_at IS NULL THEN ' — active journey'
            ELSE ' — closed journey'
        END AS item
    FROM collaborator_journeys c
    JOIN people p
      ON p.id = c.person_id
     AND p.tenant_id = c.tenant_id

    UNION ALL

    SELECT
        r.tenant_id,
        'Reference Data' AS area,
        lower(r.type || '|' || r.code) AS comparison_key,
        r.type || ': ' || r.code || ' — ' || r.label ||
        CASE
            WHEN r.active = 1 THEN ''
            ELSE ' — inactive'
        END AS item
    FROM reference_data r

    UNION ALL

    SELECT
        e.tenant_id,
        'Expenses' AS area,
        e.id AS comparison_key,
        e.expense_date || ' — ' ||
        COALESCE(
            NULLIF(e.item_description, ''),
            NULLIF(e.description, ''),
            e.id
        ) || ' — ' ||
        printf('%.2f', COALESCE(e.total_amount, e.amount)) AS item
    FROM expenses e

    UNION ALL

    SELECT
        w.tenant_id,
        'Work Periods' AS area,
        w.work_date || '|' || w.period_code AS comparison_key,
        w.work_date || ' — ' ||
        w.period_code || ' — ' ||
        w.name || ' — ' ||
        w.status AS item
    FROM work_periods w
)
SELECT
    t.name AS tenant,
    i.area,
    i.item
FROM tenant_inventory i
JOIN tenants t
  ON t.id = i.tenant_id
WHERE i.tenant_id IN ('default', '$SECOND_TENANT_ID')
  AND NOT EXISTS (
      SELECT 1
      FROM tenant_inventory other
      WHERE other.tenant_id IN ('default', '$SECOND_TENANT_ID')
        AND other.tenant_id <> i.tenant_id
        AND other.area = i.area
        AND other.comparison_key = i.comparison_key
  )
ORDER BY
    t.name,
    i.area,
    i.item;
SQL

printf 'Tenant differences report written to:\n%s\n' "$OUTPUT_FILE"

The output will be saved as: /tmp/tenant-differences.txt

The report lists records that occur in only one of the two tenants, grouped by:

• Tenant
• Data area
• Human-readable item

For example, you should see entries similar to:

tenant                    area    item
------------------------  ------  -------------------------------------------
Default Tenant            People  B28C Tenant Admin — Manual TenantAdmin ...
Bite 28C Secondary Tenant People  B28C Secondary Tenant Person — Manual ...

Reference Data records with the same type and code in both tenants are omitted because they are not differences.

For Expenses, each transaction has its own identity, so every tenant-specific expense appears. Work Periods are compared by work date and period code.

A shorter People-only report

For Manual Test 6, this smaller report is probably enough:

#!/usr/bin/env bash

set -euo pipefail

source /tmp/ers-b28c-manual-env.sh

REPORT_DIR="/tmp"
OUTPUT_FILE="${REPORT_DIR}/tenant-people.txt"

mkdir -p "$REPORT_DIR"

sqlite3 -header -column backend/data/app.db >"$OUTPUT_FILE" <<SQL
SELECT
    t.name AS tenant,
    p.nickname,
    p.first_name || ' ' || p.last_name AS legal_name,
    p.email
FROM people p
JOIN tenants t
  ON t.id = p.tenant_id
WHERE p.tenant_id IN ('default', '$SECOND_TENANT_ID')
ORDER BY
    t.name,
    p.nickname;
SQL

printf 'People-by-tenant report written to:\n%s\n' "$OUTPUT_FILE"

The output will be saved as: /tmp/tenant-people.txt

Keep that output open beside the browser. Switch tenants and verify that each UI displays only the People listed for the selected tenant.