Database pages

1-- core.notion.database_pages
2-- Every page that is a row in a Notion database, with its title, its status, who
3-- created it and how long since anyone touched it.
4-- Grain: page.
5--
6-- Pages are matched to a data source on parent_data_source_id. Pages that only
7-- carry a parent database id are matched to that database's data source when the
8-- database has exactly one; otherwise data_source_id stays NULL rather than
9-- guessing.
10--
11-- `status` is the value of the page's status-type property. A data source with
12-- more than one status property gets the one whose name sorts first; add a filter
13-- on property_name in staging.notion.page_property if you need another. Every
14-- other property stays in that staging model, one row per page x property.
15
16WITH data_sources AS (
17    SELECT
18        ds.data_source_id,
19        ds.database_id,
20        ARRAY_TO_STRING(
21            ARRAY(SELECT JSON_VALUE(r, '$.plain_text') FROM UNNEST(JSON_QUERY_ARRAY(ds.title_json)) AS r),
22            ''
23        ) AS data_source_title
24    FROM {{staging.notion.data_source}} ds
25),
26
27single_source_databases AS (
28    SELECT database_id, ANY_VALUE(data_source_id) AS data_source_id
29    FROM data_sources
30    GROUP BY 1
31    HAVING COUNT(*) = 1
32),
33
34titles AS (
35    SELECT page_id, string_value AS title
36    FROM {{staging.notion.page_property}}
37    WHERE property_type = 'title'
38    QUALIFY ROW_NUMBER() OVER (PARTITION BY page_id ORDER BY property_name) = 1
39),
40