202401191539_initial_migration.sql.tmpl raw

   1  CREATE TABLE "apps" ("id" integer,"name" text,"description" text,"nostr_pubkey" text UNIQUE,"created_at" {{ .Timestamp }},"updated_at" {{ .Timestamp }},PRIMARY KEY ("id"));
   2  CREATE TABLE "app_permissions" ("id" integer,"app_id" integer,"request_method" text,"max_amount" integer,"budget_renewal" text,"expires_at" {{ .Timestamp }},"created_at" {{ .Timestamp }},"updated_at" {{ .Timestamp }},PRIMARY KEY ("id"),CONSTRAINT "fk_app_permissions_app" FOREIGN KEY ("app_id") REFERENCES "apps"("id") ON DELETE CASCADE);
   3  CREATE INDEX "idx_app_permissions_request_method" ON "app_permissions"("request_method");
   4  CREATE INDEX "idx_app_permissions_app_id" ON "app_permissions"("app_id");
   5  CREATE TABLE "request_events" ("id" integer,"app_id" integer null,"nostr_id" text UNIQUE,"content" text,"state" text,"created_at" {{ .Timestamp }},"updated_at" {{ .Timestamp }},PRIMARY KEY ("id"),CONSTRAINT "fk_request_events_app" FOREIGN KEY ("app_id") REFERENCES "apps"("id") ON DELETE CASCADE);
   6  CREATE UNIQUE INDEX "idx_request_events_nostr_id" ON "request_events"("nostr_id");
   7  CREATE INDEX "idx_request_events_app_id" ON "request_events"("app_id");
   8  CREATE TABLE "payments" ("id" integer,"app_id" integer,"request_event_id" integer,"amount" integer,"payment_request" text,"preimage" text,"created_at" {{ .Timestamp }},"updated_at" {{ .Timestamp }},PRIMARY KEY ("id"),CONSTRAINT "fk_payments_app" FOREIGN KEY ("app_id") REFERENCES "apps"("id") ON DELETE CASCADE,CONSTRAINT "fk_payments_request_event" FOREIGN KEY ("request_event_id") REFERENCES "request_events"("id"));
   9  CREATE INDEX "idx_payments_request_event_id" ON "payments"("request_event_id");
  10  CREATE INDEX "idx_payments_app_id" ON "payments"("app_id");
  11  CREATE INDEX idx_payment_sum ON payments (app_id, preimage, created_at);
  12  CREATE INDEX idx_request_events_app_id_and_id ON request_events(app_id, id);
  13  CREATE TABLE "response_events" ("id" integer,"nostr_id" text UNIQUE,"request_id" integer,"content" text,"state" text,"replied_at" {{ .Timestamp }},"created_at" {{ .Timestamp }},"updated_at" {{ .Timestamp }},PRIMARY KEY ("id"),CONSTRAINT "fk_response_events_request_event" FOREIGN KEY ("request_id") REFERENCES "request_events"("id") ON DELETE CASCADE);
  14  CREATE UNIQUE INDEX "idx_response_events_nostr_id" ON "response_events"("nostr_id");
  15  CREATE TABLE "user_configs" ("id"	integer, "key"	text NOT NULL UNIQUE, "value"	text, "encrypted"	numeric, "created_at" {{ .Timestamp }},"updated_at" {{ .Timestamp }}, PRIMARY KEY("id"));
  16  CREATE UNIQUE INDEX "idx_user_configs_key" ON "user_configs" ("key");
  17