Content Categories & Link Collections
Overview
This domain has 3 tables: content_category stores top-level content categories per LINE OA,
content_subcategory stores subcategories with multi-level nesting through parent_id/level,
and content_link stores content link collections (a single link that resolves to a set of
content filtered by category and publication date range) together with a click counter
Table content_category
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | SERIAL | NO | nextval(...) | Primary key |
uuid | UUID | NO | gen_random_uuid() | External reference id for the category (unique) |
line_oa_id | INTEGER | NO | - | LINE OA the category belongs to (FK to line_oa.id) |
organization_id | INTEGER | NO | - | Organization that owns the category |
name | VARCHAR(255) | NO | - | Category name |
slug | VARCHAR(255) | NO | - | Category slug used in the URL |
description | TEXT | YES | - | Category description |
thumbnail | VARCHAR(500) | YES | - | Category thumbnail image URL |
sort_order | INTEGER | NO | 0 | Display order |
status | ContentCategoryStatus (ENUM) | NO | active | Status: active, inactive, delete |
created_date | TIMESTAMPTZ(3) | NO | now() | Creation timestamp |
created_by | INTEGER | NO | 0 | Creator (user id) |
updated_date | TIMESTAMPTZ(3) | NO | now() | Last update timestamp |
updated_by | INTEGER | NO | 0 | Last updater (user id) |
deleted_date | TIMESTAMPTZ(3) | YES | - | Deletion timestamp (soft delete) |
Table content_subcategory
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | SERIAL | NO | nextval(...) | Primary key |
uuid | UUID | NO | gen_random_uuid() | External reference id for the subcategory (unique) |
category_id | INTEGER | NO | - | Parent category (FK to content_category.id) |
line_oa_id | INTEGER | NO | - | LINE OA the subcategory belongs to |
organization_id | INTEGER | NO | - | Organization that owns the subcategory |
name | VARCHAR(255) | NO | - | Subcategory name |
slug | VARCHAR(255) | NO | - | Subcategory slug used in the URL |
description | TEXT | YES | - | Subcategory description |
thumbnail | VARCHAR(500) | YES | - | Subcategory thumbnail image URL |
sort_order | INTEGER | NO | 0 | Display order |
status | ContentCategoryStatus (ENUM) | NO | active | Status: active, inactive, delete |
parent_id | INTEGER | YES | - | Higher-level subcategory (enables multi-level nesting) |
content_subcategory_id | INTEGER | YES | - | Reference to a related subcategory (self-reference) |
level | INTEGER | NO | 2 | Nesting level of the subcategory (level 1 is the category) |
created_date | TIMESTAMPTZ(3) | NO | now() | Creation timestamp |
created_by | INTEGER | NO | 0 | Creator (user id) |
updated_date | TIMESTAMPTZ(3) | NO | now() | Last update timestamp |
updated_by | INTEGER | NO | 0 | Last updater (user id) |
deleted_date | TIMESTAMPTZ(3) | YES | - | Deletion timestamp (soft delete) |
Table content_link
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
id | SERIAL | NO | nextval(...) | Primary key |
uuid | UUID | NO | gen_random_uuid() | External reference id for the link (unique) |
token | VARCHAR(64) | NO | - | Token used in the public link (unique) |
line_oa_id | INTEGER | NO | - | LINE OA the link belongs to (FK to line_oa.id) |
organization_id | INTEGER | NO | - | Organization that owns the link |
name | VARCHAR(255) | NO | - | Link collection name |
description | TEXT | YES | - | Link collection description |
category_id | INTEGER | YES | - | Filter content by category (FK to content_category.id) |
subcategory_id | INTEGER | YES | - | Filter content by subcategory (FK to content_subcategory.id) |
published_date_from | TIMESTAMPTZ(3) | YES | - | Include content published on or after this timestamp |
published_date_to | TIMESTAMPTZ(3) | YES | - | Include content published up to this timestamp |
audience_ids | JSONB | YES | '[]' | Audiences allowed to access this link |
status | VARCHAR(20) | NO | 'active' | Link status |
click_count | BIGINT | NO | 0 | Cumulative click count for the link |
created_date | TIMESTAMPTZ(3) | NO | now() | Creation timestamp |
created_by | INTEGER | NO | - | Creator (user id) |
updated_date | TIMESTAMPTZ(3) | NO | now() | Last update timestamp |
updated_by | INTEGER | NO | - | Last updater (user id) |
deleted_date | TIMESTAMPTZ(3) | YES | - | Deletion timestamp (soft delete) |
deleted_by | INTEGER | YES | - | User who deleted the link (user id) |
Notes
content_categoryhas a unique constraint on (line_oa_id,slug) — slugs must be unique within one OAcontent_subcategoryhas a unique constraint on (category_id,slug) — subcategory slugs must be unique within the same parent categorycontent_subcategory.category_idis a FK tocontent_category.idwithON DELETE CASCADE— deleting a parent category deletes all of its subcategoriescontent_subcategorysupports multi-level nesting throughparent_idandlevel(default2meaning the second level below the category), and both columns are indexedcontent_link.tokenhas a unique constraint plus a separate index, because it is the key used to open the link from outsidecontent_linkstores only the filter criteria, not an explicit content list — the displayed content is resolved fromcategory_id,subcategory_idand thepublished_date_from/published_date_torange- All three tables use soft delete via
deleted_date, which is indexed content_categoryandcontent_subcategoryare referenced bycontent_pageandcontent_link(see Content Pages)