API platform: keys, usage and webhooks

CREATE TYPE delivery_state AS ENUM ('pending', 'delivered', 'failed');

CREATE TABLE developers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    company text
);

CREATE TABLE applications (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    developer_id uuid NOT NULL,
    name text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX applications_developer_id_idx ON applications (developer_id);

CREATE TABLE api_keys (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    application_id uuid NOT NULL,
    prefix text NOT NULL UNIQUE,
    key_hash text NOT NULL,
    scopes text[] DEFAULT '{}' NOT NULL,
    expires_at timestamptz,
    revoked_at timestamptz
);
CREATE INDEX api_keys_application_id_idx ON api_keys (application_id);

CREATE TABLE rate_limits (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    application_id uuid NOT NULL UNIQUE,
    requests_per_minute int DEFAULT 60 NOT NULL,
    requests_per_day int DEFAULT 10000 NOT NULL
);

CREATE TABLE usage_records (
    application_id uuid NOT NULL,
    day date NOT NULL,
    endpoint text NOT NULL,
    requests bigint DEFAULT 0 NOT NULL,
    errors bigint DEFAULT 0 NOT NULL,
    PRIMARY KEY (application_id, day, endpoint)
);

CREATE TABLE webhook_endpoints (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    application_id uuid NOT NULL,
    url text NOT NULL,
    secret text NOT NULL,
    events text[] DEFAULT '{}' NOT NULL,
    active boolean DEFAULT true NOT NULL
);
CREATE INDEX webhook_endpoints_application_id_idx ON webhook_endpoints (application_id);

CREATE TABLE webhook_deliveries (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    endpoint_id uuid NOT NULL,
    event text NOT NULL,
    payload jsonb NOT NULL,
    state delivery_state DEFAULT 'pending' NOT NULL,
    attempts int DEFAULT 0 NOT NULL,
    next_attempt_at timestamptz
);
CREATE INDEX webhook_deliveries_due_idx ON webhook_deliveries (state, next_attempt_at);
CREATE INDEX webhook_deliveries_endpoint_id_idx ON webhook_deliveries (endpoint_id);

ALTER TABLE applications ADD FOREIGN KEY (developer_id) REFERENCES developers (id) ON DELETE CASCADE;

ALTER TABLE api_keys ADD FOREIGN KEY (application_id) REFERENCES applications (id) ON DELETE CASCADE;

ALTER TABLE rate_limits ADD FOREIGN KEY (application_id) REFERENCES applications (id) ON DELETE CASCADE;

ALTER TABLE usage_records ADD FOREIGN KEY (application_id) REFERENCES applications (id) ON DELETE CASCADE;

ALTER TABLE webhook_endpoints ADD FOREIGN KEY (application_id) REFERENCES applications (id) ON DELETE CASCADE;

ALTER TABLE webhook_deliveries ADD FOREIGN KEY (endpoint_id) REFERENCES webhook_endpoints (id) ON DELETE CASCADE;