a tool for shared writing and social publishing
leaflet.pub
1-- Add sort_date computed column to documents table
2-- This column stores the older of publishedAt (from JSON data) or indexed_at
3-- Used for sorting feeds chronologically by when content was actually published
4
5-- Create an immutable function to parse ISO 8601 timestamps from text
6-- This is needed because direct ::timestamp cast is not immutable (accepts 'now', 'today', etc.)
7-- The regex validates the format before casting to ensure immutability
8CREATE OR REPLACE FUNCTION parse_iso_timestamp(text) RETURNS timestamptz
9LANGUAGE sql IMMUTABLE STRICT AS $$
10 SELECT CASE
11 -- Match ISO 8601 format: YYYY-MM-DDTHH:MM:SS with optional fractional seconds and Z/timezone
12 WHEN $1 ~ '^\d{4}-\d{2}-\d{2}[T ]\d{2}:\d{2}:\d{2}(\.\d+)?(Z|[+-]\d{2}:?\d{2})?$' THEN
13 $1::timestamptz
14 ELSE
15 NULL
16 END
17$$;
18
19ALTER TABLE documents
20ADD COLUMN sort_date timestamptz GENERATED ALWAYS AS (
21 LEAST(
22 COALESCE(parse_iso_timestamp(data->>'publishedAt'), indexed_at),
23 indexed_at
24 )
25) STORED;
26
27-- Create index on sort_date for efficient ordering
28CREATE INDEX documents_sort_date_idx ON documents (sort_date DESC, uri DESC);