Data Model: Item Catalog¶

Module: item_catalog Last Updated: 2026-05-10 Generated From: analysis.md

Tables¶

All tables are prefixed with ic_ to namespace them within the modular monolith. All include tenant_id for Row Level Security (RLS).

ic_item_group¶

Stores organizational groups for items. Created before items so items can reference it.

Constraints:

CONSTRAINT uq_ic_item_group_name_type UNIQUE (tenant_id, item_type, lower(name))
CONSTRAINT chk_ic_item_group_item_type CHECK (
    item_type IN ('ingredient','product','intermediate','packaging','consumable','service','resale')
)

ic_item¶

The canonical item registry — the core table of this module.

Constraints:

CONSTRAINT uq_ic_item_code UNIQUE (tenant_id, lower(code))

CONSTRAINT uq_ic_item_ean UNIQUE (tenant_id, ean)
    -- Partial: applied only where ean IS NOT NULL

CONSTRAINT chk_ic_item_status CHECK (status IN ('active', 'inactive'))

CONSTRAINT chk_ic_item_item_type CHECK (
    item_type IN ('ingredient','product','intermediate','packaging','consumable','service','resale')
)

CONSTRAINT chk_ic_item_storage_unit CHECK (
    storage_unit IN ('kg','g','l','ml','piece')
)

CONSTRAINT chk_ic_item_shelf_life_positive CHECK (
    shelf_life_days IS NULL OR shelf_life_days > 0
)

CONSTRAINT fk_ic_item_group FOREIGN KEY (group_id)
    REFERENCES ic_item_group(id)
    ON DELETE RESTRICT   -- prevents orphaned group references; group must be empty to delete

ic_item_tag¶

Tags stored in a child table to allow indexed filtering and avoid array column complexity.

Alternative: tags can be stored as a text[] column with GIN index on ic_item. The child-table approach is preferred for query flexibility (tag-level filtering without array operators). Choose one at implementation time.

Constraints:

CONSTRAINT uq_ic_item_tag UNIQUE (item_id, lower(tag))
CONSTRAINT fk_ic_item_tag_item FOREIGN KEY (item_id)
    REFERENCES ic_item(id) ON DELETE CASCADE

Full-Text Search Column (on ic_item)¶

To support diacritic-insensitive full-text search across name, code, description, group name, and tags, a generated tsvector column is maintained:

ALTER TABLE ic_item ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (
        to_tsvector('simple',
            unaccent(coalesce(name, '')) || ' ' ||
            unaccent(coalesce(code, '')) || ' ' ||
            unaccent(coalesce(description, ''))
        )
    ) STORED;

Tag content and group name are included in search either by joining ic_item_tag and ic_item_group at query time (via ILIKE + unaccent), or by denormalizing them into the search_vector via a trigger. Decision deferred to implementation. The unaccent PostgreSQL extension must be enabled.

Indexes¶

Row Level Security¶

All tables use tenant_id for RLS. Policy pattern (applied identically to all three tables):

ALTER TABLE ic_item ENABLE ROW LEVEL SECURITY;

CREATE POLICY ic_item_tenant_isolation ON ic_item
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

ALTER TABLE ic_item_group ENABLE ROW LEVEL SECURITY;

CREATE POLICY ic_item_group_tenant_isolation ON ic_item_group
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

ALTER TABLE ic_item_tag ENABLE ROW LEVEL SECURITY;

CREATE POLICY ic_item_tag_tenant_isolation ON ic_item_tag
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

System items and system groups have tenant_id = NULL or a reserved system tenant UUID — see provisioning design (outside scope of this doc).

Transaction Boundaries¶

FK Relationships Diagram¶

ic_item_group
    id ─────────────────────────────┐
    tenant_id                       │ (group_id FK)
    name                            │
    item_type                       │
    is_system                       │
                                    ▼
ic_item ────────────── ic_item_tag
    id ──────────────────────── item_id (FK, ON DELETE CASCADE)
    tenant_id                   tenant_id
    code                        tag
    name
    item_type
    storage_unit
    status
    group_id (FK → ic_item_group.id, NULLABLE)
    shelf_life_days
    ean

Alembic Migration Notes¶

• Enable unaccent extension before creating the search_vector generated column.
• The partial unique index on ean requires CREATE UNIQUE INDEX ... WHERE ean IS NOT NULL syntax (not expressible as a table constraint in some versions — use op.create_index in Alembic).
• The lower(code) and lower(name) unique constraints require expression indexes, which Alembic creates via op.create_index('...', 'ic_item', [text('lower(code)')], unique=True).