code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0033_referral_programs.sql (about)

     1  -- +goose Up
     2  
     3  -- create a new referral record when a new referral program is created,
     4  -- updated, or ended so that we keep an audit trail, just in case.
     5  -- We create it as a hypertable and set a retention policy to make sure
     6  -- old and redundant data is removed in due course.
     7  create table if not exists referral_programs (
     8    id bytea not null,
     9    version int not null,
    10    benefit_tiers jsonb,
    11    end_of_program_timestamp timestamp with time zone not null,
    12    window_length int not null,
    13    staking_tiers jsonb,
    14    vega_time timestamp with time zone not null,
    15    ended_at timestamp with time zone,
    16    primary key (vega_time)
    17  );
    18  
    19  select create_hypertable('referral_programs', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    20  
    21  -- simplify referral retrieval using a view that provides the latest referral information.
    22  create view current_referral_program as (
    23      select *
    24      from referral_programs
    25      order by vega_time desc limit 1 -- there should only be 1 referral program running at any time, so just get the last record.
    26  );
    27  
    28  create table referral_sets(
    29      id bytea not null,
    30      referrer bytea not null,
    31      created_at timestamp with time zone not null,
    32      updated_at timestamp with time zone not null,
    33      vega_time timestamp with time zone not null,
    34      primary key (id)
    35  );
    36  
    37  create table referral_set_referees(
    38      referral_set_id bytea not null,
    39      referee bytea not null,
    40      joined_at timestamp with time zone not null,
    41      at_epoch bigint not null,
    42      vega_time timestamp with time zone not null,
    43      primary key (referral_set_id, referee)
    44  );
    45  
    46  create table referral_set_stats(
    47      set_id bytea not null,
    48      at_epoch bigint not null,
    49      referral_set_running_notional_taker_volume numeric,
    50      reward_factor numeric,
    51      referees_stats jsonb not null,
    52      vega_time timestamp with time zone not null,
    53      primary key (vega_time, set_id)
    54  );
    55  
    56  select create_hypertable('referral_set_stats', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    57  
    58  -- make sure that it doesn't exist in case migrations have failed previously
    59  drop view if exists referral_set_referee_stats;
    60  
    61  create view referral_set_referee_stats as (
    62    select set_id, at_epoch, referral_set_running_notional_taker_volume, stats.referee_stats->>'party_id' as party_id,
    63           stats.referee_stats->>'discount_factor' as discount_factor, reward_factor,
    64           vega_time
    65    from referral_set_stats,
    66         jsonb_array_elements(referees_stats) with ordinality stats(referee_stats, position)
    67  );
    68  
    69  -- +goose Down
    70  
    71  drop view if exists referral_set_referee_stats;
    72  drop table if exists referral_set_stats;
    73  drop table if exists referral_set_referees;
    74  drop table if exists referral_sets;
    75  drop view if exists current_referral_program;
    76  drop table if exists referral_programs;