initial commit

[squeep-indie-auther] / src / db / postgres / sql / token-cleanup.sql

diff --git a/src/db/postgres/sql/token-cleanup.sql b/src/db/postgres/sql/token-cleanup.sql
new file mode 100644 (file)
index 0000000..9c2c744
--- /dev/null
+++ b/src/db/postgres/sql/token-cleanup.sql
@@ -0,0 +1,26 @@
+-- Remove tokens no longer in use.
+-- only clean after code has expired
+WITH
+cleanable_codes AS (
+       SELECT t.code_id, t.is_token, t.is_revoked, t.expires, t.refresh_expires FROM token t
+               WHERE t.created < (now() - $(codeLifespanSeconds)::text::interval)
+)
+DELETE FROM token WHERE code_id IN (
+       SELECT code_id FROM cleanable_codes
+       WHERE
+               NOT is_token -- profile-only redemptions
+       OR (
+               is_token AND (
+                       is_revoked -- revoked
+                       OR (
+                               expires < now() AND (
+                                       -- expired and unrefreshable
+                                       refresh_expires IS NULL
+                                       OR
+                                       -- expired and refresh expired
+                                       (refresh_expires IS NOT NULL AND refresh_expires < now())
+                               )
+                       )
+               )
+       )
+)