One SQL view that tells me what is overdue across forty-odd trackers

The first full run of the nightly refresh died with this:

SequelizeDatabaseError: No operator matches the given name and argument types.
You might need to add explicit type casts.   code 42883   position 1521

Position 1521 of a statement that was, by then, several thousand characters long. I'll come back to that. First, why there was one enormous statement at all.

Over two days in October I let an AI coding assistant build out my self-hosted NocoBase through its REST API. I asked for a certificate tracker, then a finance tracker, then, after a "give me a lot of ideas" conversation that I probably should not have started, "okay do all of these". What came out the other end is a menu with Finance, Career, Work, Homelab, Personal, Growth, Life, Projects and a couple more, and as of mid-October 2026 the spec files in the repo hold 98 module definitions. Most of them have a date column that matters: an expiry, a renewal, a review date, a follow-up, a "practise this again on" date.

I am not going to open sixty pages every morning. Nobody would. The only way a tracker like this survives past week two is if something tells me what needs doing, and that something has to look in all of them at once.

So the rule became: anything with a date gets the same treatment, and one view collects everything that is close or late.

Every tracker gets the same three columns

The modules are written as declarative specs (the platform side of that is someone else's story). The part that matters here is the flag argument. A module that passes it gets three extra fields for free: warn_days (editable per row, with a per-module default), days_left, and a coloured flag select with three values, ok, soon, overdue.

Mod('expiry_items', 'Expiry tracker', 'Personal', 'IdcardOutlined', label='item',
    fields=[string('item','Item'), sel('category','Category','passport id_card driving_licence ...'),
            string('holder','Holder'), date('expires_on','Expires on'), url('renew_url','Renewal link'), ...],
    flag=dict(date='expires_on', label='🪪 Expiry', item="item || COALESCE(' (' || holder || ')', '')", warn_default=60))

From that one flag=dict(...) the generator writes the refresh SQL:

UPDATE expiry_items SET
  days_left = CASE WHEN (true) AND expires_on IS NOT NULL THEN (expires_on - TODAY) END,
  flag = CASE WHEN NOT (true) OR expires_on IS NULL THEN NULL
              WHEN expires_on < TODAY THEN 'overdue'
              WHEN expires_on <= TODAY + COALESCE(warn_days, 60)::int THEN 'soon'
              ELSE 'ok' END

TODAY is not current_date. It is (now() AT TIME ZONE 'Region/City')::date with my real zone in it, because Postgres in that container runs on UTC and I am several hours ahead of UTC. Without it, everything due "today" flips to overdue at the wrong hour of the evening.

The active part of the dict (the (true) above) is what stops finished things from nagging: a bill on autopay, a backlog item marked done, a trip that got cancelled. That clause turned out to matter more than I expected, which I'll get to.

As of mid-October, 43 module specs carry a flag. The older hand-built tables (certifications and domains) predate the convention and have their own status column with valid / expiring / expired, warned in months rather than days.

The view

v_attention is a NocoBase "SQL collection": you give it a SELECT, a list of fields and a key, and it behaves like a read-only table you can drop on a page. It is created through the same API as everything else:

call('POST', 'collections:create', dict(
    name='v_attention', title='Needs attention', template='sql',
    sql=f"SELECT * FROM ({ATT}) a",
    fields=[f_str('module','Area'), f_str('item','Item'), f_date('due_on','Due'),
            f_int('days_left','Days left'), flag_f],
    filterTargetKey=['module','item'], autoGenId=False, timestamps=False))

ATT is a UNION ALL built in Python. Every flagged module contributes one SELECT of the same shape:

SELECT '🪪 Expiry' AS module, (item || COALESCE(' (' || holder || ')', ''))::text AS item,
       expires_on::date AS due_on, days_left::int AS days_left, flag::text AS flag
FROM expiry_items WHERE flag IN ('soon','overdue')

Then a handful of hand-written branches for things that are not dates at all. These were the ones I actually cared about once I saw them:

SELECT '🏦 Low balance', name || ' is ' || balance::text || ' (minimum ' || min_balance::text || ')',
       NULL::date, NULL::int, 'overdue'
FROM accounts WHERE COALESCE(is_active,true) AND min_balance IS NOT NULL AND balance < min_balance
UNION ALL
SELECT '🔌 Service down', name || COALESCE(' (HTTP ' || last_http_code::text || ')',''),
       NULL::date, NULL::int, 'overdue'
FROM homelab_services WHERE health = 'down' AND COALESCE(status,'running') = 'running'

There is also an over-budget branch, a "disk above 85%" branch reading the latest row of a resource log, an open bank-reconciliation branch, and one that asks whether the projected balance of all my accounts goes below zero in the next 30 days. That last one is the reason for the second error in this post.

The view went live with one row in it: a cloud certification with 49 days left. It was correct. I had forgotten about it.

Two things that were refused

The first was the one at the top. The "every N days" fields (warn_days, practice_every_days, contact_every_days) are integers in NocoBase, which means bigint in Postgres. And Postgres has date + integer but no date + bigint. Every expression that added a stored interval to a date had to become + COALESCE(x, 14)::int. The assistant found it the boring way: rebuild the exact same SQL text locally, slice it at character 1521, and read what was there:

UPDATE interview_bank SET next_review_on = COALESCE(last_practiced_on, "createdAt"::date) + COALESCE(practice_every_days,14);

Six places across two spec files got the cast. The same bug came back a day later in the car-servicing module, word for word, because new code does not read old commit messages. It is now the fourth bullet in the provisioning README under "Gotchas learned", for whatever that is worth.

The second refusal came from NocoBase itself. When the cash-flow forecast was added to the union, creating the view failed:

{"errors":[{"message":"SQL statements contain dangerous keywords"}]}

No hint which keyword. So: bisect. One small query per suspect, each sent to the same validator:

with             OK
lateral          OK
generate_series  {"errors":[{"message":"SQL statements contain dangerous keywords"}]}
cross join       OK
over             OK
having           OK
union all        OK

generate_series is on NocoBase's deny list for SQL collections. I can sort of see why (it is a cheap way to make a query enormous), but it also happens to be the normal way to get "every day for the next 90 days". The replacement is a recursive CTE, which the validator does accept:

WITH RECURSIVE days(day) AS (
  SELECT TODAY UNION ALL SELECT day + 1 FROM days WHERE day < TODAY + 90
), ...

Recurrence of bills and income on those days had to be done with date arithmetic too (month differences modulo 3 for quarterly, day-of-month clamped to the month's last day for things due on the 31st). It is not pretty SQL. It works.

Amusingly, a workflow SQL node does not run through that validator. The monthly job that creates next year's planner rows still uses generate_series(1, 30) and nobody complains. Only the view collection is policed.

Testing it with fake passports

A view over forty tables is easy to get subtly wrong, and the failure mode is silence: a module whose branch never returns anything looks exactly like a module with nothing due. So before the view went on the Home page, it was tested with throwaway rows chosen to land in every bucket, then deleted again.

The test created an expiring passport twenty days out with a sixty-day warning window, a driving licence that had expired three days earlier, something four hundred days away, a contact last spoken to a hundred days ago with a "keep in touch every 60 days" rule, two birthdays (one five days away, one in March), a habit with a broken streak, and a goal with two key results at 50% and 100%. The refresh ran, and the computed columns came back as:

passport (20d, warn60)       {'flag': 'soon', 'days_left': 20}
licence (-3d)                {'flag': 'overdue', 'days_left': -3}
far (400d)                   {'flag': 'ok', 'days_left': 400}
contact (100d ago/60)        {'next_contact_on': '2026-09-03', 'flag': 'overdue', 'days_left': -40}
bday Oct 18                  {'next_on': '2026-10-18', 'flag': 'soon', 'days_left': 5}
bday Mar 2                   {'next_on': '2027-03-02', 'flag': 'ok', 'days_left': 140}
habit streak (0,-1,-2,-4)    {'streak': 3, 'last_done_on': '2026-10-13'}

Every line there exercises a different piece. The passport proves the per-row warning window beats the module default. The contact proves that a date computed from two other columns (last contacted plus the interval) flows into the flag. The birthdays prove the "next occurrence of a yearly date" logic, including rolling into next year. The habit streak is not a flag at all, but it lives in the same refresh blob and a bug there would have killed the night run for everyone.

Then the morning digest was run by hand against the same rows:

🔔 Needs attention (6)
🔴 📞 Reach out: TEST friend — 03 Sep 2026 (-40 days)
🔴 🪪 Expiry: TEST licence — 10 Oct 2026 (-3 days)
🟠 🔑 Rotate: NocoBase API key "claude" (NocoBase) — 14 Oct 2026 (1 days)
🟠 🤲 Owed to me: TEST ... — 15 Oct 2026 (2 days)
...

Red before orange, then by days left. The API-key row in the middle was not test data. It was the real key I had created for the build, with a one-day expiry, tracked in the credential-rotation table. So one of the first real lines the attention view produced about my own setup was a reminder to delete the key that built it.

After the check, a cleanup pass deleted every test row and printed what was left per table; the only survivor was the six seeded rows of the idea parking lot, which were supposed to be there.

Keeping the flags fresh

A view is computed when you read it, but the flag and days_left columns it reads are stored. Something has to update them. There are two paths.

On save: every flagged module gets a collection-triggered workflow, Auto: <Module> — refresh on save, with a single SQL node holding that module's statements. NocoBase lets one SQL node carry several ;-separated statements, which keeps the workflow count sane.

At night: Auto: daily refresh (all flags & computed dates) runs at 00:10 and executes every module's statements in one node. That is the one that died at position 1521. It is a single point of failure on purpose: one node, one transaction-ish blob, one place to look. When it fails, nothing is refreshed, and the morning digest is built from yesterday's flags. I have gone back and forth on splitting it per module. A split means forty workflows to look at when something breaks instead of one. I kept the blob.

Some modules depend on other tables. A habit's streak depends on its daily check-ins; a goal's progress is the average of its key results; one yearly giving record depends on a ledger of payments. Saving a check-in has to refresh the habit, not just the check-in. So three child tables get their own pair of workflows (on save and on delete) that run the parent's refresh statements. That list is short and hand-written in the builder, and it is the place I would look first if a number on a parent page ever looked stale.

One more subtlety caught me later. SQL nodes do not trigger other workflows. If a workflow changes rows by SQL (the car servicing one, say, or the "bill paid" one that rolls a due date forward), the per-module "refresh on save" trigger never fires for those rows. So each workflow that edits data by SQL also appends the target module's refresh statements to its own SQL. Easy to forget, and the symptom is subtle: a row whose date you just pushed out still says "overdue" until 00:10.

The same trick, sideways: the calendar

Once every tracker described its own date column in its spec, a second union fell out almost for free. v_calendar takes the same list of flagged modules and, instead of filtering on the flag, emits every dated row as an event: an id built as <table>:<row id>, a title made from the module's label and item expression, a start and end date, a kind, and the source table.

def add(coll, title, start, end, kind, where='true'):
    parts.append(f"SELECT '{coll}:' || id::text AS id, ({title})::text AS title, ({start})::date AS start_on, "
                 f"COALESCE(({end})::date, ({start})::date) AS end_on, '{kind}'::text AS kind, '{coll}'::text AS source "
                 f"FROM {coll} WHERE ({start}) IS NOT NULL AND ({where})")

The kind comes from the menu the module lives in, except for anything that expires (credentials, documents, licences, TLS certificates, servers), which is always shown in red as "Expiry / renewal" no matter where it lives. A trip has a real start and end; most other things are one-day events. The active clause from the flag spec doubles as the calendar's filter, so a cancelled trip or a finished goal disappears from the calendar for the same reason it disappears from the attention list.

v_upcoming is the calendar restricted to the next fourteen days, and it sits on the Home page right under the attention list. The two answer different questions. Attention is "what is inside its warning window or late", which for a passport means two months ahead. Upcoming is "what happens in the next two weeks", which includes things that need no action at all, like a public holiday or a conference I already registered for.

The calendar has grown since. A table of events I register myself (from the Calendar page or from a chat command on my phone) was added as one more hand-written branch, so my own appointments sit next to certificate expiries. Each time a branch is added, the view collection is destroyed and recreated with the new SQL, because NocoBase has no "alter view". The pages that show it keep working, since they refer to the collection by name.

What a real morning looked like

The first time the uptime branch was live, the view said:

🎓 Certification   (cloud cert)                  48 days    soon
🔌 Service down    Serverkit                      -          overdue
🔌 Service down    Traefik                        -          overdue
🔑 Rotate          NocoBase API key (NocoBase)   0 days     soon

Two of those four were false. The uptime check pings each service's URL from inside NocoBase, and those two hostnames had no DNS records at all. The dashboard had a link for Traefik that pointed nowhere, and Serverkit was not published. Neither was down; both were badly described. The view did its job, the data was wrong. That is fine. I would rather fix data than miss a real outage.

The other false alarm took a day to notice. Public holidays carry a flag so that the next one shows up two weeks ahead. Past holidays that did not repeat (moveable ones with a fixed date row for this year) were then sitting in the view as "overdue". Three festivals from earlier in the year, all of them overdue, all of them long gone. The fix was the active clause: a holiday only counts if it recurs yearly or its date is still in the future.

flag=dict(date='next_on', active="(COALESCE(recurring,false) OR date >= " + TODAY + ")", ...)

That is the general lesson I took from this view: the hard part is never the date maths, it is deciding when something is finished enough to stop counting.

The view sits at the top of the NocoBase Home page, sorted by days_left, fifteen rows per page. The 08:00 in-app digest, the 08:05 e-mail and the 08:10 phone push are all built from the same union, filtered a bit harder: overdue items every day, items due within seven days every day, and everything inside its warning window only on Mondays. If one module has more than three rows in a given digest, it is collapsed to a single line like 🔴 📌 Backlog item: 8 items need attention (see Home).

In practice I read the table, not the digest. The digest tells me something is there; the table lets me sort, filter by area with the filter button, and click through to the module. One quirk of putting a SQL view in a NocoBase table: a view has no id column, so the collection is declared with filterTargetKey=['module','item'] and the table block uses item as its row key. If one module ever produces two rows with the same item text on the same day (two backlog chores with an identical title, say), the table will treat them as one row. It has not happened, because titles in my trackers are long and specific, but it is the kind of edge case that would look like a missing reminder rather than an error.

The newest trackers sit outside the union on purpose. Recurring household tasks and self-registered calendar events were built on the 16th as separate modules with their own reminder workflows and their own blocks on Home, because "pending until I press done, remind me at 18:00" does not fit a single daily flag. The recurring tasks module promptly hit the same date + bigint error as everything else, and grew one rule worth copying: occurrences from before the task was created never count as missed. A brand-new weekly chore should not arrive already overdue.

What it costs and what I would change

Maintenance is close to zero as long as I never add a flagged module by hand. The union is regenerated from the specs whenever the views are rebuilt, so a new tracker joins it automatically. If I ever create a table in the UI and want it in the list, I have to remember to add a branch. I have not done that yet, which probably tells you how often I create tables in the UI now.

The other thing I would do differently is the stored flag. If days_left were computed in the view instead of written by a nightly job, there would be nothing to go stale and no nightly blob to fail. The reason it is stored is that the per-module pages show the flag too, with colours, and NocoBase tables colour a select field much more easily than a computed expression. It was a UI decision that leaked into the data model. I knew that when I accepted it and I would probably accept it again.