Reference

Herald — Data Model (Neon Postgres)

All customer-owned tables carry tenant_id. All timestamps are timestamptz (UTC). Use uuid primary keys (gen_random_uuid() — enable pgcrypto). Tokens are stored encrypted (ciphertext in a text/bytea column); the schema shows the logical field, encryption is applied in the app layer.

Enums

network: instagram | linkedin (extensible).
account_status: active | expired | revoked.
post_status (derived, may be stored denormalized): draft | scheduled | publishing | published | partial | failed.
target_status: pending | publishing | published | failed.
api_key_status: active | revoked.

Tables

tenants

column	type	notes
id	uuid pk
name	text
created_at	timestamptz	default now()

users

column	type	notes
id	uuid pk
tenant_id	uuid fk→tenants
email	citext unique
password_hash	text	argon2/bcrypt; null if magic-link only
name	text
created_at	timestamptz

api_keys

column	type	notes
id	uuid pk
tenant_id	uuid fk→tenants
name	text	label
key_prefix	text	first chars shown in UI (e.g. `hld_live_ab12`)
key_hash	text	sha256 of the full key; full key shown once
status	api_key_status	default active
last_used_at	timestamptz	null
created_at	timestamptz
Index: `(key_hash)` unique; lookups by hash.

platform_apps

Herald's own registered apps (one row per network). Not tenant-scoped.

column	type	notes
network	network pk
client_id	text
client_secret	text	encrypted
scopes	text[]	requested scopes
api_version	text	pinned platform API version
updated_at	timestamptz
(Alternatively hold these in env vars; a table lets you rotate without redeploy.)

connected_accounts

column	type	notes
id	uuid pk
tenant_id	uuid fk→tenants
network	network
platform_account_id	text	IG user id / LinkedIn org URN
display_name	text
avatar_url	text
page_id	text	IG's linked FB Page id / LinkedIn org URN
access_token	text	encrypted
refresh_token	text	encrypted, nullable
token_expires_at	timestamptz	nullable
scopes	text[]
status	account_status	default active
connected_at	timestamptz
updated_at	timestamptz
Unique: `(tenant_id, network, platform_account_id)`.

oauth_states

Short-lived, single-use CSRF/state for the OAuth dance.

column	type	notes
state	text pk	signed random
tenant_id	uuid
network	network
redirect_uri	text
created_at	timestamptz
consumed_at	timestamptz	null until used
(TTL-clean rows older than ~15 min.)

media_assets

column	type	notes
id	uuid pk
tenant_id	uuid fk→tenants
kind	text	image/video
storage_key	text	object-store key
public_url	text	public HTTPS URL
bytes	bigint
width / height	int	nullable
created_at	timestamptz

posts

column	type	notes
id	uuid pk
tenant_id	uuid fk→tenants
created_by	text	api_key id or user id
text	text	caption/commentary
media	jsonb	list of {media_id?
scheduled_at	timestamptz	null = publish now
status	post_status	derived/denormalized
external_ref	text	consumer's idempotency/reference key, nullable
created_at	timestamptz
Index: `(tenant_id, created_at)`; unique `(tenant_id, external_ref)` when not null
(idempotency).

post_targets

One row per (post × connected account). This is the unit the worker publishes.

column	type	notes
id	uuid pk
post_id	uuid fk→posts
tenant_id	uuid	denormalized for scoping
connected_account_id	uuid fk→connected_accounts
network	network
status	target_status	default pending
scheduled_at	timestamptz	copied from post; the worker claims by this
attempts	int	default 0
platform_post_id	text	set on success
permalink	text	set on success
error_code	text	on failure
error_message	text	on failure
published_at	timestamptz	on success
locked_at	timestamptz	worker claim marker
Index: partial on `(status, scheduled_at)` where `status='pending'` for the
worker's due-scan; `(post_id)`.

audit_log (optional but recommended)

column	type	notes
id	uuid pk
tenant_id	uuid
actor	text	api_key/user id
action	text	e.g. post.create, account.connect
meta	jsonb	never tokens
created_at	timestamptz

Worker claim query (safe concurrency)

UPDATE post_targets
SET status='publishing', locked_at=now(), attempts=attempts+1
WHERE id IN (
  SELECT id FROM post_targets
  WHERE status='pending' AND scheduled_at <= now()
  ORDER BY scheduled_at
  FOR UPDATE SKIP LOCKED
  LIMIT 20
)
RETURNING *;

Migrations

Use Alembic (or plain numbered SQL under backend/app/db/migrations/). Seed platform_apps (or read from env). Enable pgcrypto (for gen_random_uuid) and citext in the first migration.