github.com/nais/knorten@v0.0.0-20240104110906-55926958e361/pkg/database/migrations/013_events.sql (about)

     1  -- +goose Up
     2  CREATE TYPE event_status AS ENUM (
     3      'new',
     4      'processing',
     5      'completed',
     6      'pending',
     7      'failed'
     8      );
     9  
    10  CREATE TYPE event_type AS ENUM (
    11      'create:team',
    12      'update:team',
    13      'delete:team',
    14      'create:jupyter',
    15      'update:jupyter',
    16      'delete:jupyter',
    17      'create:airflow',
    18      'update:airflow',
    19      'delete:airflow',
    20      'create:compute',
    21      'delete:compute'
    22      );
    23  
    24  CREATE TYPE log_type as ENUM (
    25      'info',
    26      'error'
    27      );
    28  
    29  CREATE TABLE Events
    30  (
    31      id         uuid         DEFAULT uuid_generate_v4(),
    32      event_type event_type                             NOT NULL,
    33      task       JSONB                                  NOT NULL,
    34      status     event_status DEFAULT 'new'             NOT NULL,
    35      deadline   TEXT                                   NOT NULL,
    36      created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    37      updated_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP NOT NULL,
    38      owner      TEXT                                   NOT NULL,
    39      PRIMARY KEY (id)
    40  );
    41  
    42  CREATE TABLE Event_Logs
    43  (
    44      id         uuid      DEFAULT uuid_generate_v4(),
    45      event_id   uuid                                NOT NULL,
    46      log_type   log_type                            NOT NULL,
    47      message    TEXT                                NOT NULL,
    48      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    49      PRIMARY KEY (id),
    50      FOREIGN KEY (event_id) REFERENCES Events (id) ON DELETE CASCADE
    51  );
    52  
    53  -- +goose StatementBegin
    54  CREATE OR REPLACE FUNCTION update_updated_at_column()
    55      RETURNS TRIGGER AS $$
    56  BEGIN
    57      NEW.updated_at = now();
    58      RETURN NEW;
    59  END;
    60  $$ language 'plpgsql';
    61  -- +goose StatementEnd
    62  
    63  CREATE TRIGGER update_events_updated_at BEFORE UPDATE ON events FOR EACH ROW EXECUTE PROCEDURE  update_updated_at_column();
    64  
    65  -- +goose Down
    66  DROP FUNCTION update_updated_at_column();
    67  
    68  DROP TABLE Event_Logs;
    69  
    70  DROP TABLE Events;
    71  
    72  DROP TYPE log_type;
    73  
    74  DROP TYPE event_type;
    75  
    76  DROP TYPE event_status;