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

   1 BEGIN;
   2
   3         CREATE TABLE redeemed_ticket (
   4                 ticket_id BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   5                 created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
   6                 subject TEXT NOT NULL,
   7                 resource TEXT NOT NULL,
   8                 iss TEXT,
   9                 token TEXT NOT NULL,
  10                 ticket TEXT NOT NULL,
  11                 published TIMESTAMP WITH TIME ZONE
  12         );
  13         COMMENT ON TABLE redeemed_ticket IS $docstring$
  14 Tickets which have been redeemed for tokens and published.
  15         $docstring$;
  16         CREATE INDEX redeemed_ticket_created_idx ON redeemed_ticket(created);
  17         CREATE INDEX redeemed_ticket_published_idx ON redeemed_ticket(published) WHERE published IS NOT NULL;
  18         CREATE INDEX redeemed_ticket_ref_idx ON redeemed_ticket(subject, resource, iss, ticket);
  19
  20         -- Update schema version
  21         INSERT INTO _meta_schema_version (major, minor, patch) VALUES (1, 1, 0);
  22
  23 COMMIT;