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
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.case_groups table, questions.case_group_id FK, a group is served or withheld as a whole.lessons.published).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
pool_visible AND NOT archived.tenant_id column, not a new database.Migration risks
caseGroupId values referenced by questions have no entry in caseGroups — the scenario text is missing, so those item sets cannot be rendered.explanation; importing as-is means the results screen cannot show explanations for most questions.journalEntries, tAccounts, schedules, given, required, commonMistakes, standard, standardParagraph) are populated on zero questions; schema exists but content does not.Worked queries
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.
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
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?