surtr: ...

1 files changed, 9 insertions, 3 deletions

diff --git a/hosts/surtr/postgresql.nix b/hosts/surtr/postgresql.nix
index 7b3b8c74..d8f66fcc 100644
--- a/hosts/surtr/postgresql.nix
+++ b/hosts/surtr/postgresql.nix
@@ -30,17 +30,23 @@ in {
           BEGIN;
           SELECT _v.register_patch('000-base', null, null);
 
-          CREATE TABLE virtual_mailbox_mapping (
+          CREATE TABLE mailbox (
+            id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
+            mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''),
+            quota_bytes bigint CONSTRAINT quota_bytes_positive CHECK (CASE WHEN quota_bytes IS NOT NULL THEN quota_bytes > 0 ELSE true),
+            quota_rule text GENERATED ALWAYS AS (CASE WHEN quota_bytes IS NULL THEN '*:ignore' ELSE '*:bytes=' || quota_bytes) STORED
+          )
+          CREATE TABLE mailbox_mapping (
             id uuid PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
             local text CONSTRAINT local_non_empty CHECK (local IS DISTINCT FROM '''),
             domain text NOT NULL CONSTRAINT domain_non_empty CHECK (domain <> '''),
-            mailbox text NOT NULL CONSTRAINT mailbox_non_empty CHECK (mailbox <> '''),
+            mailbox uuid REFERENCES virtual_mailbox(id)
             CONSTRAINT local_domain_unique UNIQUE (local, domain)
           );
           CREATE UNIQUE INDEX domain_unique ON virtual_mailbox_mapping (domain) WHERE local IS NULL;
 
           CREATE VIEW virtual_mailbox_domain (domain) AS SELECT DISTINCT domain FROM virtual_mailbox_mapping;
-          CREATE VIEW virtual_mailbox (mailbox) AS SELECT DISTINCT mailbox FROM virtual_mailbox_mapping;
+          CREATE VIEW virtual_mailbox_mapping (mailbox, lookup) AS SELECT mailbox.mailbox as mailbox, (CASE WHEN local IS NULL THEN ''' ELSE local) || '@' || domain AS lookup FROM mailbox_mapping INNER JOIN mailbox on mailbox.id = mailbox_mapping.mailbox;
           COMMIT;
         ''}
       '';