git.squeep.com Git - squeep-indie-auther/blob - src/db/postgres/sql/schema/1.0.0/apply.sql

   1 BEGIN;
   2
   3         CREATE TABLE almanac (
   4                 event TEXT NOT NULL PRIMARY KEY,
   5                 date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz
   6         );
   7         COMMENT ON TABLE almanac IS $docstring$
   8 Notable events for service administration.
   9         $docstring$;
  10
  11         CREATE TABLE authentication (
  12                 identifier_id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  13                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  14                 last_authentication TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT '-infinity'::timestamptz,
  15                 identifier TEXT NOT NULL UNIQUE,
  16                 credential TEXT
  17         );
  18         COMMENT ON TABLE authentication IS $docstring$
  19 Users and their credentials.
  20         $docstring$;
  21
  22         CREATE TABLE resource (
  23                 resource_id UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(), -- few insertions, v4 preferred over v1
  24                 description TEXT NOT NULL DEFAULT '',
  25                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  26                 secret TEXT NOT NULL CHECK(length(secret) > 0)
  27         );
  28         COMMENT ON TABLE resource IS $docstring$
  29 External resource servers and their credentials.
  30         $docstring$;
  31
  32         CREATE TABLE profile (
  33                 profile_id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  34                 identifier_id BIGINT NOT NULL REFERENCES authentication(identifier_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  35                 profile TEXT NOT NULL,
  36                 CONSTRAINT unique_identifier_id_profile UNIQUE (identifier_id, profile)
  37         );
  38         CREATE INDEX profile_identifier_id_idx ON profile(identifier_id);
  39         CREATE INDEX profile_profile_idx ON profile(profile);
  40         COMMENT ON TABLE profile IS $docstring$
  41 Profile URIs and the users they are associated with.
  42 $docstring$;
  43
  44         CREATE TABLE scope (
  45                 scope_id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  46                 scope TEXT NOT NULL UNIQUE,
  47                 description TEXT NOT NULL DEFAULT '',
  48                 application TEXT NOT NULL DEFAULT '',
  49                 is_permanent BOOLEAN NOT NULL DEFAULT false,
  50                 is_manually_added BOOLEAN NOT NULL DEFAULT false
  51         );
  52         COMMENT ON TABLE scope IS $docstring$
  53 All and any scopes encountered.
  54 $docstring$;
  55         COMMENT ON COLUMN scope.is_permanent IS $docstring$
  56 Prevents deletion of scope, set on seeded scope rows.
  57 $docstring$;
  58         COMMENT ON COLUMN scope.is_manually_added IS $docstring$
  59 Prevents deletion of scope when no longer referenced, set on user-added scopes.
  60 User can still delete manually.
  61 $docstring$;
  62         CREATE INDEX scope_garbage_collectable_idx ON scope(scope_id) WHERE NOT (is_permanent OR is_manually_added);
  63         COMMENT ON INDEX scope_garbage_collectable_idx IS $docstring$
  64 Shadow the primary index with a partial to help GC cleanup of client-provided scopes.
  65 $docstring$;
  66         CREATE INDEX scope_is_permanent_idx ON scope(scope_id) WHERE is_permanent;
  67         CREATE INDEX scope_is_manual_idx ON scope(scope_id) WHERE is_manually_added;
  68
  69         CREATE TABLE profile_scope (
  70                 profile_id BIGINT NOT NULL REFERENCES profile(profile_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  71                 scope_id BIGINT NOT NULL REFERENCES scope(scope_id) ON DELETE NO ACTION ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  72                 PRIMARY KEY (profile_id, scope_id)
  73         );
  74         COMMENT ON TABLE profile_scope IS $docstring$
  75 Convenience bindings of available scopes for a profile.
  76 $docstring$;
  77         CREATE INDEX profile_scope_scope_id_idx ON profile_scope(scope_id);
  78
  79         CREATE TABLE token (
  80                 code_id UUID NOT NULL PRIMARY KEY,
  81                 profile_id BIGINT NOT NULL REFERENCES profile(profile_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  82                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  83                 expires TIMESTAMP WITH TIME ZONE,
  84                 refresh_expires TIMESTAMP WITH TIME ZONE,
  85                 refreshed TIMESTAMP WITH TIME ZONE,
  86                 duration INTERVAL,
  87                 CONSTRAINT expires_needs_duration CHECK (expires IS NULL OR duration IS NOT NULL),
  88                 refresh_duration INTERVAL,
  89                 CONSTRAINT refresh_expires_needs_refresh_duration CHECK (refresh_expires IS NULL OR refresh_duration IS NOT NULL),
  90                 refresh_count BIGINT NOT NULL DEFAULT 0,
  91                 is_revoked BOOLEAN NOT NULL DEFAULT false,
  92                 is_token BOOLEAN NOT NULL,
  93                 client_id TEXT NOT NULL,
  94                 resource TEXT,
  95                 profile_data JSONB
  96         );
  97         CREATE INDEX token_profile_id_idx ON token(profile_id);
  98         CREATE INDEX token_created_idx ON token(created);
  99         CREATE INDEX token_expires_idx ON token(expires);
 100         CREATE INDEX token_refresh_expires_idx ON token(refresh_expires);
 101         CREATE INDEX token_is_revoked_idx ON token(is_revoked) WHERE is_revoked;
 102         COMMENT ON TABLE token IS $docstring$
 103 Redeemed codes and their current states.
 104 $docstring$;
 105         COMMENT ON COLUMN token.is_token IS $docstring$
 106 Whether code was redeemed for a token, or only a profile.
 107 We track non-token redemptions to prevent re-redemption while
 108 code is still valid.
 109 $docstring$;
 110         COMMENT ON COLUMN token.resource IS $docstring$
 111 Tokens granted by ticket redemption are associated with a
 112 resource url for which they are valid.
 113 $docstring$;
 114         COMMENT ON INDEX token_is_revoked_idx IS $docstring$
 115 A partial index on revoked tokens, to ease cleanup query.
 116 $docstring$;
 117
 118         CREATE TABLE token_scope (
 119                 code_id UUID NOT NULL REFERENCES token(code_id) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 120                 scope_id BIGINT NOT NULL REFERENCES scope(scope_id) ON DELETE NO ACTION ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
 121                 PRIMARY KEY (code_id, scope_id)
 122         );
 123         COMMENT ON TABLE token_scope IS $docstring$
 124 Scopes associated with a token.
 125 $docstring$;
 126         CREATE INDEX token_scope_scope_id_idx ON token_scope(scope_id);
 127
 128         -- Update schema version
 129         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 0, 0);
 130
 131         -- Seed scope data
 132         INSERT INTO scope (scope, description, application, is_permanent) VALUES
 133                 ('profile', 'Access detailed profile information, including name, image, and url.', 'IndieAuth', true),
 134                 ('email', 'Include email address with detailed profile information.', 'IndieAuth', true),
 135                 ('create', 'Create new items.', 'MicroPub', true),
 136                 ('draft', 'All created items are drafts.', 'MicroPub', true),
 137                 ('update', 'Edit existing items.', 'MicroPub', true),
 138                 ('delete', 'Remove items.', 'MicroPub', true),
 139                 ('media', 'Allow file uploads.', 'MicroPub', true),
 140                 ('read', 'Read access to channels.', 'MicroSub', true),
 141                 ('follow', 'Management of following list.', 'MicroSub', true),
 142                 ('mute', 'Management of user muting.', 'MicroSub', true),
 143                 ('block', 'Management of user blocking.', 'MicroSub', true),
 144                 ('channels', 'Management of channels.', 'MicroSub', true)
 145         ;
 146
 147 COMMIT;