Why wouldn’t you use something like this instead?

WITH episode_ AS (...), -- stale episodes s_ AS ( SELECT e.feed_id, s.card_id, MAX(e.created_at) AS last_created_at, AVG(COALESCE(s.score::INT::REAL, 0.5)) AS _avg, COUNT(s.score) AS _count FROM score s INNER JOIN episode e ON s.episode_id = e.episode_id INNER JOIN feed f ON e.feed_id = f.feed_id WHERE f.usr_id IS NOT NULL GROUP BY e.feed_id, s.card_id ) INSERT INTO episode (episode_id, feed_id, card_ids) SELECT LEFT(MD5(RANDOM()::TEXT), 16), feed_id, ARRAY_AGG(card_id) FROM ( SELECT feed_id, card_id FROM ( SELECT feed_id, card_id, per_episode, ROW_NUMBER() OVER ( PARTITION BY feed_id ORDER BY COALESCE(s_.last_created_at, c.created_at, NOW()) + f.every * RANDOM() * COALESCE(fd.chaos, 0.3) + f.every * COALESCE(s_._count, 7) ^ COALESCE(fd.deceleration, 0.7) * s_._avg ^ COALESCE(fd.feedback, 1.2) + f.every * LN(1 + c.bytes_b) * COALESCE(fd.ease, 1.0), RANDOM() ) AS n FROM episode_ e_ INNER JOIN feed f USING (feed_id) INNER JOIN feed_deck fd USING (feed_id) INNER JOIN card c USING (deck_id) LEFT JOIN s_ ON s_.feed_id = e_.feed_id AND s_.card_id = c.card_id WHERE NOT c.card_id = ANY(fd.hidden_card_ids) ) a WHERE per_episode >= n ) b GROUP BY feed_id;

So weird how some minds think alike in more than one aspect.

I already loved your writing, but I never would have predicted that anyone else (besides me) was trying to put spaced repetition algorithms into SQL.

I've been in touch with the guys making Open Spaced Repetition: https://github.com/open-spaced-repetition

... specifically the Free Spaced Repetition Scheduler (FSRS), of which they recommended if we're going to copy one of the implementations it should be the TypeScript one, since that's the best documented: https://github.com/open-spaced-repetition/ts-fsrs + https://open-spaced-repetition.github.io/ts-fsrs/

I've been working on it the last few days and was working on it this morning, then I saw this/your post on HN. Jaw dropped.

I always enjoy making things from scratch as a way of getting to know them, but in this case it seems wise (and a bit of a new experience for me) to copy someone else's open source work and try to do a real implementation of the FSRS-5 scheduler in PostgreSQL PL/pgSQL functions.

Then cards and their data-logic can stay together in one place, and anyone can use whatever language they want for UI/API.