0001_change_tracking.down.sql (3139B)
1 create table local_event_projection_cursor_previous ( 2 consumer_id text primary key, 3 last_seq integer not null, 4 updated_at_ms integer not null, 5 check (trim(consumer_id) <> ''), 6 check (last_seq >= 0) 7 ); 8 9 insert into local_event_projection_cursor_previous( 10 consumer_id, 11 last_seq, 12 updated_at_ms 13 ) 14 select 15 consumer_id, 16 last_change_seq, 17 updated_at_ms 18 from local_event_projection_cursor; 19 20 drop table local_event_projection_cursor; 21 alter table local_event_projection_cursor_previous rename to local_event_projection_cursor; 22 23 create table local_event_record_previous ( 24 seq integer primary key autoincrement, 25 record_id text not null unique, 26 family text not null check (family in ('local_work', 'signed_event')), 27 status text not null check (status in ('local_draft', 'local_saved', 'pending_publish', 'published', 'failed', 'conflict')), 28 source_runtime text not null check (source_runtime in ('cli', 'app', 'network', 'service', 'worker', 'test')), 29 created_at_ms integer not null, 30 inserted_at_ms integer not null, 31 updated_at_ms integer not null, 32 owner_account_id text, 33 owner_pubkey text, 34 farm_id text, 35 listing_addr text, 36 local_work_json text, 37 event_id text, 38 event_kind integer, 39 event_pubkey text, 40 event_created_at integer, 41 event_tags_json text, 42 event_content text, 43 event_sig text, 44 raw_event_json text, 45 outbox_status text not null check (outbox_status in ('none', 'pending', 'acknowledged', 'failed')), 46 relay_set_fingerprint text, 47 relay_delivery_json text, 48 check (trim(record_id) <> ''), 49 check (family <> 'local_work' or local_work_json is not null), 50 check (family <> 'local_work' or outbox_status = 'none'), 51 check (family <> 'signed_event' or (event_id is not null and event_kind is not null and event_pubkey is not null and event_sig is not null and raw_event_json is not null)) 52 ); 53 54 insert into local_event_record_previous( 55 seq, 56 record_id, 57 family, 58 status, 59 source_runtime, 60 created_at_ms, 61 inserted_at_ms, 62 updated_at_ms, 63 owner_account_id, 64 owner_pubkey, 65 farm_id, 66 listing_addr, 67 local_work_json, 68 event_id, 69 event_kind, 70 event_pubkey, 71 event_created_at, 72 event_tags_json, 73 event_content, 74 event_sig, 75 raw_event_json, 76 outbox_status, 77 relay_set_fingerprint, 78 relay_delivery_json 79 ) 80 select 81 seq, 82 record_id, 83 family, 84 status, 85 source_runtime, 86 created_at_ms, 87 inserted_at_ms, 88 updated_at_ms, 89 owner_account_id, 90 owner_pubkey, 91 farm_id, 92 listing_addr, 93 local_work_json, 94 event_id, 95 event_kind, 96 event_pubkey, 97 event_created_at, 98 event_tags_json, 99 event_content, 100 event_sig, 101 raw_event_json, 102 outbox_status, 103 relay_set_fingerprint, 104 relay_delivery_json 105 from local_event_record 106 order by seq asc; 107 108 drop table local_event_record; 109 alter table local_event_record_previous rename to local_event_record; 110 111 create index local_event_record_event_id_idx on local_event_record(event_id); 112 create index local_event_record_listing_addr_idx on local_event_record(listing_addr); 113 create index local_event_record_owner_pubkey_idx on local_event_record(owner_pubkey); 114 create index local_event_record_status_idx on local_event_record(status);