Table: posts
Connected to
Section titled “Connected to”This table references:
assets(id) via columncover_asset_idworkflow_states(id) via columnstate_idteams(id) via columnteam_idassets(id) via columncover_thumbnail_asset_id
Referenced by:
collection_posts: columnpost_idpost_acls: columnpost_idpost_assets: columnpost_idpost_tags: columnpost_id
Columns
Section titled “Columns”| Column | Type | Nullable | Notes |
|---|---|---|---|
id | uuid | no | Default gen_random_uuid() |
author_user_ref | bigint | no | - |
title | text | no | Default ''::text |
description | text | no | Default ''::text |
visibility | text | no | Default 'org-only'::text |
cover_asset_id | uuid | yes | References assets(id) |
posted_at | timestamp | no | Default now() |
like_count | bigint | no | Default 0 |
comment_count | bigint | no | Default 0 |
search_text | tsvector | yes | - |
origin_server_id | uuid | yes | - |
deleted_at | timestamp | yes | - |
created_at | timestamp | no | Default now() |
updated_at | timestamp | no | Default now() |
state_id | uuid | yes | References workflow_states(id); Default public.post_initial_state_id(). The post’s workflow state: published (shown on shared surfaces) or wip, the draft state, visible only to its author and to holders of posts.admin (ADR 0091). Set at creation from the request’s draft flag and changed only through publish and unpublish, which record a workflow audit entry. Reads treat a NULL or unknown state as unpublished. An asset’s workflow state means something different and never decides publication. |
team_id | uuid | yes | References teams(id) |
cover_thumbnail_asset_id | uuid | yes | References assets(id) |
subtitle_track_override | jsonb | yes | Per-post override of the parent asset’s subtitle tracks. NULL means use the asset’s own tracks; a JSON value replaces them (the subtitles package defines the shape). |
deleted_reason | text | yes | - |
deleted_by_user_ref | bigint | yes | - |
mature | boolean | no | Default false |
ai_provenance | text | yes | Derived from the post’s contributors (its member assets and its two cover pictures) and kept up to date by triggers; no request sets it. A positive claim spreads: one generated contributor makes the post generated, otherwise one assisted makes it assisted. The post reads none only if it has contributors and every one declares none. If there is no generated or assisted contributor, an undeclared contributor leaves the post undeclared. A post with no contributors is NULL. |
ai_pure | boolean | no | Default false. Derived: true only when the post has contributors and every one of them declares generated. This is the column to filter on, while ai_provenance is the label: a post mixing generated with other declarations is labelled generated but isn’t pure, so a ‘hide AI work’ filter doesn’t hide mixed work. assisted and undeclared contributors make a post not pure. Like ai_provenance, it’s a filter, never a gate (ADR 0094). |
cover_focal_x | double | yes | Horizontal focal point for the post cover’s square crop, as a fraction of the picture’s width (0 = left, 1 = right). The browse grid is the only post view that crops the cover; other views show it whole. Maps to CSS object-position; NULL means centre, distinct from an explicit 0.5. Must be NULL or in 0..1 together with cover_focal_y. Chosen against the original picture, so render it on an uncropped preview, not the square col thumbnail. |
cover_focal_y | double | yes | Vertical focal point for the post cover’s square crop, as a fraction of the picture’s height (0 = top, 1 = bottom). Works like cover_focal_x. |
comments_enabled | boolean | no | Default true. Whether the post accepts new comments and replies. A setting of the post itself, chosen by whoever can edit it; system.admin doesn’t bypass it. When false, creating a comment returns 409 comments_disabled and nothing else changes: existing comments stay readable, and listing, deletion and moderation are unaffected. Whiteboards and annotations don’t read it. The comment-create transaction reads it under a row lock, so a disable that has committed can’t be raced. |
Constraints
Section titled “Constraints”CONSTRAINT posts_ai_provenance_check CHECK (((ai_provenance IS NULL) OR (ai_provenance = ANY (ARRAY['none'::text, 'assisted'::text, 'generated'::text]))))CONSTRAINT posts_cover_focal_check CHECK ((((cover_focal_x IS NULL) AND (cover_focal_y IS NULL)) OR (((cover_focal_x >= (0)::double precision) AND (cover_focal_x <= (1)::double precision)) AND ((cover_focal_y >= (0)::double precision) AND (cover_focal_y <= (1)::double precision)))))CONSTRAINT posts_visibility_check CHECK ((visibility = ANY (ARRAY['private'::text, 'org-only'::text, 'followers'::text, 'explicit-share'::text, 'public'::text])))
Full DDL
Section titled “Full DDL”This is the table’s CREATE TABLE statement. Constraints added with separate ALTER TABLE statements, including foreign-key ON DELETE rules, and indexes are defined elsewhere in the dump; see the complete app/schema.sql.
CREATE TABLE public.posts ( id uuid DEFAULT gen_random_uuid() NOT NULL, author_user_ref bigint NOT NULL, title text DEFAULT ''::text NOT NULL, description text DEFAULT ''::text NOT NULL, visibility text DEFAULT 'org-only'::text NOT NULL, cover_asset_id uuid, posted_at timestamp with time zone DEFAULT now() NOT NULL, like_count bigint DEFAULT 0 NOT NULL, comment_count bigint DEFAULT 0 NOT NULL, search_text tsvector, origin_server_id uuid, deleted_at timestamp with time zone, created_at timestamp with time zone DEFAULT now() NOT NULL, updated_at timestamp with time zone DEFAULT now() NOT NULL, state_id uuid DEFAULT public.post_initial_state_id(), team_id uuid, cover_thumbnail_asset_id uuid, subtitle_track_override jsonb, deleted_reason text, deleted_by_user_ref bigint, mature boolean DEFAULT false NOT NULL, ai_provenance text, ai_pure boolean DEFAULT false NOT NULL, cover_focal_x double precision, cover_focal_y double precision, comments_enabled boolean DEFAULT true NOT NULL, CONSTRAINT posts_ai_provenance_check CHECK (((ai_provenance IS NULL) OR (ai_provenance = ANY (ARRAY['none'::text, 'assisted'::text, 'generated'::text])))), CONSTRAINT posts_cover_focal_check CHECK ((((cover_focal_x IS NULL) AND (cover_focal_y IS NULL)) OR (((cover_focal_x >= (0)::double precision) AND (cover_focal_x <= (1)::double precision)) AND ((cover_focal_y >= (0)::double precision) AND (cover_focal_y <= (1)::double precision))))), CONSTRAINT posts_visibility_check CHECK ((visibility = ANY (ARRAY['private'::text, 'org-only'::text, 'followers'::text, 'explicit-share'::text, 'public'::text]))));See the schema overview for context.