Category

Purpose

TBD by human

Identity & key fields

• Primary key: id (uuid, default gen_random_uuid()).
• slug (text, UNIQUE, NOT NULL).
• parentId (nullable uuid, self-FK → activities.categories.id) — hierarchy parent.
• sphereId (uuid, NOT NULL, FK → activities.spheres.id) — D2: sphere binding, NOT NULL from day one (per schema comment).
• companyId (nullable uuid) — D2: NULL = platform/system category; no DB-level FK declared.
• iconUrl (nullable text).
• Indexes: categories_company_sphere_idx on (companyId, sphereId); UNIQUE index categories_lower_title_parent_sphere_uq on (LOWER(title), parentId, sphereId).

business meaning: TBD by human

Invariants

• slug is UNIQUE (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).
• sphereId is NOT NULL with FK → activities.spheres.id (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).
• UNIQUE INDEX categories_lower_title_parent_sphere_uq — case-insensitive title is unique within (parentId, sphereId) (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).
• parentId is self-referential; no on-delete cascade declared on the FK → deleting a parent would fail by default if children exist (enforced in tktspace-backend/libs/shared/data-access-db/src/lib/schema/activities.schema.ts).

business invariants: TBD by human

Lifecycle

No explicit lifecycle / status column.

Relationships

• Sphere (ENT-037) — sphereId → activities.spheres.id. N:1.
• Category (self) — parentId → activities.categories.id. N:1, hierarchical. Materialised path also lives in category_closure (closure table, not documented separately).
• Company (ENT-016) — companyId (nullable, no DB FK). N:1, application-level only.
• Activity (ENT-005) — N:M via activity_categories join table (PK = (activityId, categoryId), ON DELETE CASCADE both sides).

API surfaces

Surface	Exposed	Notes
client	yes — /categories, CategoryClientDto	Swagger UI
business	yes — /categories, /categories/{id}, CategoryAdminDto, CreateCategoryAdminDto, UpdateCategoryAdminDto	Swagger UI
super-admin	no	—

Known gotchas / open questions

• category_closure is a Drizzle-defined closure table for fast ancestor/descendant queries; it is a maintenance artefact (D4 in schema comments), not a separate business concept — kept inside this entity’s docs.
• companyId is nullable: NULL = platform-wide system category, otherwise scoped to a company. No DB-level FK (cross-schema reference: activities.categories → companies.companies). See ADR cross-schema-references-without-fk.
• The case-insensitive uniqueness considers (LOWER(title), parentId, sphereId) — duplicates within the same parent/sphere are blocked regardless of casing.