main.sql

CREATE TABLE "plans" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
  "for_user" text,
  "name" text NOT NULL,
  "base_price_id" text,
  "base_credits" bigint NOT NULL DEFAULT 42000000,
  "extra_packagse_enabled" boolean NOT NULL DEFAULT true,
  "package_price_id" text,
  "credits_per_package" bigint NOT NULL DEFAULT 10000000,
  "max_api_keys" int NOT NULL DEFAULT 7,
  PRIMARY KEY ("id")
);

CREATE TABLE "updates" (
  "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL,
  "title" text NOT NULL,
  "summary" text NOT NULL,
  "description" text NOT NULL,
  "learn_more_link" text NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "users" (
  "id" text PRIMARY KEY NOT NULL,
  "failed_gift_code_attempts" int NOT NULL DEFAULT 0,
  "last_update_acknowledged" int NOT NULL DEFAULT 0,
  "current_plan_id" uuid,
  "remainign_credits" bigint NOT NULL DEFAULT 0,
  "billing_cycle_end" timestampz NOT NULL,
  "stripe_customer_id" text,
  "auto_purchase_credits_packages" boolean NOT NULL DEFAULT false,
  "notification_settings" jsonb NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "gift_codes" (
  "code" uuid PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
  "credits" bigint NOT NULL,
  "used_at" timestampz,
  "used_by" text,
  PRIMARY KEY ("code")
);

CREATE TABLE "tickets" (
  "id" uuid PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
  "created_at" timestampz NOT NULL DEFAULT (NOW()),
  "message" text NOT NULL,
  "emotional_state" text NOT NULL,
  "uid" text,
  "contact" text,
  "resolved" boolean NOT NULL DEFAULT false,
  PRIMARY KEY ("id")
);

CREATE TABLE "api_keys" (
  "api_key" uuid PRIMARY KEY NOT NULL DEFAULT (uuid_generate_v4()),
  "disabled" boolean NOT NULL DEFAULT false,
  "monthly_credit_limit" bigint NOT NULL DEFAULT 0,
  "name" text NOT NULL,
  "origin" text NOT NULL,
  "uid" text NOT NULL,
  PRIMARY KEY ("api_key")
);

CREATE TABLE "usage" (
  "billing_cycle_end" timestampz NOT NULL,
  "api_key" uuid NOT NULL,
  "credits" bigint NOT NULL DEFAULT 0,
  PRIMARY KEY ("billing_cycle_end", "api_key")
);

CREATE INDEX ON "plans" ("for_user");

CREATE INDEX ON "plans" ("base_price_id");

CREATE INDEX ON "users" ("billing_cycle_end");

CREATE INDEX ON "gift_codes" ("used_at");

CREATE INDEX ON "tickets" ("resolved", "created_at");

CREATE INDEX ON "api_keys" ("uid");

CREATE INDEX ON "usage" ("api_key");

COMMENT ON COLUMN "plans"."for_user" IS 'null if the plan is for everyone';

COMMENT ON COLUMN "plans"."base_price_id" IS 'null if no checkout is needed - i.e., the plan is a sponsorship/Free (can have packages, though!)';

COMMENT ON COLUMN "plans"."extra_packagse_enabled" IS 'should only be false for the "Free - No Credit Card" plan';

COMMENT ON COLUMN "plans"."package_price_id" IS 'null if extra credits packages should not be billed - e.g., unlimited plans';

COMMENT ON COLUMN "updates"."title" IS 'e.g., "The Kraken Update"';

COMMENT ON COLUMN "updates"."summary" IS 'e.g., "A New and Better Infrastructure"';

COMMENT ON COLUMN "updates"."description" IS 'e.g., "Our service is back up! We"ve significantly re-designed our infrastructure: we can deploy it to any cloud (not just DigitalOcean), everything is faster, and, more importantly, RPC calls are 20x cheaper (10,000 RPC calls cost just $1). New APIs will extremely easy to add in the future. Traffic has been renamed to credits."';

COMMENT ON COLUMN "updates"."learn_more_link" IS 'e.g., "https://blog.kuhi.to/release-the-kraken"';

COMMENT ON COLUMN "users"."last_update_acknowledged" IS 'not a ref because it can be -1, meaning no update acknowledged';

COMMENT ON COLUMN "users"."current_plan_id" IS 'if null, user is on the "Free - No Card" plan.';

COMMENT ON COLUMN "users"."remainign_credits" IS 'remaining credits for THIS billing cycle';

COMMENT ON COLUMN "users"."stripe_customer_id" IS 'only created if they are subscribed to a plan that has a price id, either base or for packages';

COMMENT ON COLUMN "gift_codes"."used_at" IS 'null if unused';

COMMENT ON COLUMN "api_keys"."monthly_credit_limit" IS '0 means no limit';

ALTER TABLE "users" ADD FOREIGN KEY ("current_plan_id") REFERENCES "plans" ("id");

ALTER TABLE "gift_codes" ADD FOREIGN KEY ("used_by") REFERENCES "users" ("id");

ALTER TABLE "tickets" ADD FOREIGN KEY ("uid") REFERENCES "users" ("id");

ALTER TABLE "api_keys" ADD FOREIGN KEY ("uid") REFERENCES "users" ("id");