Folders, documents, and polymorphic threaded comments for wikis, notes, and knowledge bases.
Hierarchical folders, documents, and threaded comments. Documents live in folders (or at the root) and move through draft, published, and archived statuses. Comments are polymorphic and can attach to documents, tasks, tickets, deals, projects, contacts, or companies.
| Column | Type | Description |
|---|---|---|
| id | uuid | Primary key, auto-generated. |
| org_id | uuid | References organizations. Cascade delete. |
| parent_id | uuid | Self-referencing FK. NULL for root folders. Cascade delete. |
| name | text | Folder name. |
| description | text | Optional description. |
| 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. |
| folder_id | uuid | References folders. Set NULL on folder delete. |
| author_id | uuid | References users. Set NULL on user delete. |
| title | text | Document title. |
| body | text | Document content. |
| status | document_status | One of: draft, published, archived. Defaults to draft. |
| published_at | timestamptz | Timestamp when document 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. |
| author_id | uuid | References users. Set NULL on user delete. |
| entity_type | text | Type of parent record. Constrained to: task, ticket, document, deal, project, contact, company. |
| entity_id | uuid | ID of the parent record. |
| body | text | Comment text. |
| parent_id | uuid | Self-referencing FK for threaded replies. Cascade delete. |
| 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 | Document is a work in progress. |
| published | Document is visible to the organization. |
| archived | Document 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 folders, documents, or comments.
get_user_org_id(), is_admin(), set_updated_at()List published documents in a folder, newest first:
SELECT id, title, author_id, published_at
FROM documents
WHERE folder_id = '<folder_id>'
AND status = 'published'
ORDER BY published_at DESC;
Build the full folder hierarchy for an organization using a recursive CTE:
WITH RECURSIVE tree AS (
SELECT id, name, parent_id, 1 AS depth
FROM folders
WHERE parent_id IS NULL
UNION ALL
SELECT f.id, f.name, f.parent_id, t.depth + 1
FROM folders f
JOIN tree t ON t.id = f.parent_id
)
SELECT * FROM tree
ORDER BY depth, name;
Fetch all comments (with author) on a specific document, ordered by creation time:
SELECT
c.id,
c.body,
c.parent_id,
c.created_at,
u.full_name AS author_name
FROM comments c
LEFT JOIN users u ON u.id = c.author_id
WHERE c.entity_type = 'document'
AND c.entity_id = '<document_id>'
ORDER BY c.created_at ASC;
Search documents by title or body text:
SELECT id, title, status, updated_at
FROM documents
WHERE (title ILIKE '%' || '<search_term>' || '%'
OR body ILIKE '%' || '<search_term>' || '%')
ORDER BY updated_at DESC
LIMIT 20;
List the 10 most recently updated documents across all folders:
SELECT
d.id,
d.title,
d.status,
d.updated_at,
f.name AS folder_name
FROM documents d
LEFT JOIN folders f ON f.id = d.folder_id
ORDER BY d.updated_at DESC
LIMIT 10;
Count documents per folder, including folders with zero documents:
SELECT
f.id,
f.name,
count(d.id) AS document_count
FROM folders f
LEFT JOIN documents d ON d.folder_id = f.id
GROUP BY f.id, f.name
ORDER BY document_count DESC;