ADR-003: Tenancy, question-pool scope, and content ownership

Status: Accepted — ratified by product owner on 2026-11-21. Supersede only with a new ADR.

Date: 2026-11-21

Context

The current app has no tenancy: one Firestore document (testbank/main), one global pool, anonymous users. The export contains 2,343 questions across 4 subjects of the Philippine CPALE. There are 12 defined case groups but 45 dangling caseGroupId references (see CONTENT-INVENTORY.md).

Earlier product framing assumed the US CPA exam sections. The data settles it: this content is the Philippine CPALE, so scope keys must be CPALE subjects, never US sections.

Options considered

| Option | How scoping works | Cost | Verdict |

|--------|-------------------|------|---------|

| 1. Single shared global pool, no scoping | One pool, all questions visible to everyone | Lowest | Chosen as base |

| 2. Per-exam-section silo | One pool per subject, no cross-subject access | Low | Rejected |

| 3. Per-tenant (per-firm) private pool with sharing | Each firm has isolated pool; sharing opt-in | High | Rejected for v1 (a firm tier can be added later without schema change) |

| 4. Both: shared curated pool PLUS user-private questions | Global curated pool filtered by subject; users may create private questions that never enter the pool | Moderate | Chosen (superset of 1) |

The decision is: one global curated pool, filtered by CPALE subject; users may also create private questions that never enter the pool.

Decision

  • One questions table. owner_id NULL + pool_visible bool distinguishes curated from private. Curated rows: owner_id IS NULL, pool_visible = true. User rows: owner_id = <user>, pool_visible = false by default.
  • subject is a scoping key, not a tenant key. The LECPA has six subjects (PRBOA Res. No. 30 s. 2022): Financial Accounting and Reporting, Advanced Financial Accounting and Reporting, Management Services, Auditing, Taxation, and Regulatory Framework for Business Transactions. The export contains four subject values: three map to LECPA subjects (FAR, Management Services, Taxation) and the fourth, "Theory of Accounts", is not one of the six — it needs a mapping decision before import (TODO(user)). AFAR, Auditing and RFBT have no content at all.
  • Item sets (case groups) are first-class: case_groups table, questions.case_group_id FK, a group is served or withheld as a whole.
  • Lessons are admin-authored only in v1 (lessons.published).
  • Nothing is ever hard-deleted by a user: archived flag, deleted_at timestamp for GDPR erasure.
  • Schema implications

    | Concept | Column / Table | Nullability rule |

    |---------|----------------|------------------|

    | Question ID | questions.id TEXT PRIMARY KEY | NOT NULL (ids are TB-000001 strings already in exports and URLs — never convert to integers) |

    | Owner | owner_id UUID | NULL (NULL = curated) |

    | Pool visibility | pool_visible BOOL | NOT NULL |

    | Subject scope | subject TEXT | NOT NULL |

    | Case group | case_group_id TEXT | NULL (FK to case_groups.id) |

    | Question body | body JSONB | NOT NULL |

    | Version history | versions JSONB | NOT NULL DEFAULT '[]' |

    | Usage stats | usage JSONB | NOT NULL |

    | Archived | archived BOOL | NOT NULL DEFAULT false |

    | Deleted timestamp | deleted_at TIMESTAMPTZ | NULL (for GDPR erasure) |

    | Archived curated question | owner_id IS NULL, pool_visible = false, archived = true | pool_visible alone cannot express this; archived is the third state. The pool read filters pool_visible AND NOT archived, so an archived curated row can never leak into the pool. |

    Consequences

  • No per-firm isolation in v1 (acceptable because content is curated, not customer data).
  • The pool query must always filter pool_visible AND NOT archived.
  • A future firm tier adds a tenant_id column, not a new database.
  • Migration risks

  • 45 of 57 caseGroupId values referenced by questions have no entry in caseGroups — the scenario text is missing, so those item sets cannot be rendered.
  • 1,858 of 2,343 questions (79%) have an empty explanation; importing as-is means the results screen cannot show explanations for most questions.
  • Eight accounting-presentation fields (journalEntries, tAccounts, schedules, given, required, commonMistakes, standard, standardParagraph) are populated on zero questions; schema exists but content does not.
  • Worked queries

  • Pool read (curated, subject-scoped):
  • 
      SELECT id, subject, body, usage, versions
      FROM questions
      WHERE pool_visible AND NOT archived
        AND ($1::text IS NULL OR subject = $1)
      ORDER BY subject, dateAdded DESC;
    

    Note: pool_visible is a plain boolean that is NOT NULL, so the predicate cannot silently vanish under a NULL.

    subject values must be normalised to the six LECPA subject names at import; until the Theory of Accounts rows are remapped, a filter on a LECPA subject will not return them.

  • Private-question insert:
  • 
      INSERT INTO questions (id, owner_id, pool_visible, subject, body)
      VALUES ($1, $2, false, $3, $4);
    

    The id is generated server-side in the TB-###### format, never by the client.

    Alternatives rejected — in one line each

  • Per-firm silo in v1: rejected — adds isolation the curated content doesn't need and would require data duplication or complex sharing logic.
  • Per-user database: rejected — unsustainable operational overhead; private questions are rows, not databases.
  • Subject-per-Firestore-document carry-over: rejected — the old single-document model is the technical debt being replaced.
  • Open questions

    1. Should private questions be shareable via a link or invitation, or remain strictly per-user?

    2. Will the firm tier require role-based access within a tenant (owner/admin/member)?

    3. Should case-group completeness be enforced at import (reject questions with dangling caseGroupId) or repaired post-import?