Knowledge base articles organized into hierarchical categories with slug-based URLs and view tracking.
Articles, categories, and many-to-many links between them. Articles move through draft, published, and archived statuses. Categories support nesting via parent_id for multi-level navigation trees. Each article can belong to multiple categories.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key, auto-generated. |
| org_id | uuid | References organizations. Cascade delete. |
| author_id | uuid | References users. Set NULL on user delete. |
| title | text | Article title. |
| slug | text | URL-friendly identifier, unique per org. |
| body | text | Full article content. |
| excerpt | text | Short summary for listings and search results. |
| status | article_status |
| One of: draft, published, archived. Defaults to draft. |
| is_featured | boolean | Pinned articles shown prominently. Defaults to false. |
| view_count | integer | Read counter, incremented by the application layer. Defaults to 0. |
| published_at | timestamptz | Timestamp when the article was published. |
| created_at | timestamptz | Row creation timestamp. |
| updated_at | timestamptz | Auto-updated via trigger. |
| metadata | jsonb | Arbitrary key-value data. Defaults to empty object. |
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key, auto-generated. |
| org_id | uuid | References organizations. Cascade delete. |
| name | text | Category display name. |
| slug | text | URL-friendly identifier, unique per org. |
| description | text | Optional category description. |
| parent_id | uuid | Self-referencing FK. NULL for top-level categories. Cascade delete. |
| position | integer | Sort order within the same parent. Defaults to 0. |
| created_at | timestamptz | Row creation timestamp. |
| updated_at | timestamptz | Auto-updated via trigger. |
| metadata | jsonb | Arbitrary key-value data. Defaults to empty object. |
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key, auto-generated. |
| org_id | uuid | References organizations. Cascade delete. |
| article_id | uuid | References articles. Cascade delete. |
| category_id | uuid | References article_categories. Cascade delete. |
| position | integer | Sort order within the category. Defaults to 0. |
| created_at | timestamptz | Row creation timestamp. |
| updated_at | timestamptz | Auto-updated via trigger. |
| metadata | jsonb | Arbitrary key-value data. Defaults to empty object. |
| Value | Description |
|---|---|
| draft | Article is a work in progress. |
| published | Article is visible to the organization. |
| archived | Article is hidden from default views. |
All three tables are scoped to the current user's organization via get_user_org_id(). Any org member can select, insert, and update rows. Only admins (checked via is_admin()) can delete articles, categories, or links.
get_user_org_id(), is_admin(), set_updated_at()List published articles, newest first:
SELECT id, title, slug, excerpt, author_id, published_at, view_count
FROM articles
WHERE status = 'published'
ORDER BY published_at DESC;
Fetch published articles in a specific category:
SELECT a.id, a.title, a.slug, a.excerpt, a.published_at
FROM articles a
JOIN article_category_links acl ON acl.article_id = a.id
WHERE acl.category_id = '<category_id>'
AND a.status = 'published'
ORDER BY acl.position ASC;
Search articles by title or body text:
SELECT id, title, slug, excerpt, status, updated_at
FROM articles
WHERE (title ILIKE '%' || '<search_term>' || '%'
OR body ILIKE '%' || '<search_term>' || '%')
ORDER BY updated_at DESC
LIMIT 20;
List featured articles:
SELECT id, title, slug, excerpt, view_count, published_at
FROM articles
WHERE is_featured = true
AND status = 'published'
ORDER BY published_at DESC;
Build the full category hierarchy using a recursive CTE:
WITH RECURSIVE tree AS (
SELECT id, name, slug, parent_id, position, 1 AS depth
FROM article_categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.slug, c.parent_id, c.position, t.depth + 1
FROM article_categories c
JOIN tree t ON t.id = c.parent_id
)
SELECT * FROM tree
ORDER BY depth, position, name;
Count articles per category:
SELECT
c.id,
c.name,
count(acl.article_id) AS article_count
FROM article_categories c
LEFT JOIN article_category_links acl ON acl.category_id = c.id
GROUP BY c.id, c.name
ORDER BY article_count DESC;