Case Study - Datenmanagement

queries.sql

-- Queries to retrieve important information
-- The most used models
-- The highest billed clients
-- Users with the most fine-tuning jobs or API Keys


-- Most used Models
SELECT lm.id, lm.model, COUNT(r.id) AS request_count
FROM language_models lm
         JOIN requests r ON lm.id = r.model_id
GROUP BY lm.id, lm.model
ORDER BY request_count DESC
    LIMIT 10;

-- Highest billed clients
SELECT org.id, org.name,
       COALESCE(SUM(i.amount), 0) + COALESCE(SUM(ul.cost), 0) AS total_billed
FROM organizations org
         LEFT JOIN invoices i ON org.id = i.organization_id
         LEFT JOIN usage_logs ul ON org.id = ul.organization_id
GROUP BY org.id, org.name
ORDER BY total_billed DESC
    LIMIT 10;

-- Users with the most fine-tuning jobs
SELECT u.id, u.email, COUNT(ftj.id) AS fine_tuning_jobs
FROM users u
         LEFT JOIN fine_tuning_jobs ftj ON u.id = ftj.user_id
GROUP BY u.id, u.email
ORDER BY fine_tuning_jobs DESC
    LIMIT 10;

-- Users with the most API Keys
SELECT org.id, org.name, COUNT(r.id) AS total_requests
FROM organizations org
         JOIN api_keys ak ON org.id = ak.organization_id
         JOIN requests r ON ak.id = r.api_key_id
GROUP BY org.id, org.name
ORDER BY total_requests DESC
    LIMIT 10;

-- All Requests trying to beat Harry
SELECT *
FROM requests
WHERE content ILIKE '%Harry%';

sample_data.sql

-- Subscriptions
INSERT INTO subscriptions (name, requests, tokens, billing_start, billing_end)
VALUES
    ('Basic Plan', 1000, 50000, '2025-07-01', '2025-07-31'),
    ('Pro Plan', 10000, 1000000, '2025-07-01', '2025-07-31'),
    ('Enterprise Plan', 100000, 10000000, '2025-07-01', '2025-07-31');

-- Organizations
INSERT INTO organizations (name, billing_address, payment_method, subscription_plan)
VALUES
    ('Leo 1 Students', 'Leonardo Campus', 'credit_card', 1),
    ('H1 Students', 'Irgendwo am Schloss', 'paypal', 2),
    ('Harry Potter Fan Club', 'Hogwarts Castle', 'bank_transfer', 3);
-- Users
INSERT INTO users (email, password_hash, session_provider, subscription_id, deactivated)
VALUES
    ('[email protected]', 'greatpassword', 'email', 1, false),
    ('[email protected]', 'betterpassword', 'google', 2, false),
    ('[email protected]', 'expecto-patronum', 'email', 3, false);

-- Org Users (roles)
INSERT INTO org_users (user_id, organization_id, role, can_finetune, can_prompt)
VALUES
    (1, 1, 'admin', true, true),
    (2, 2, 'member', false, true),
    (3, 3, 'admin', true, false);

-- API Keys
INSERT INTO api_keys (user_id, organization_id, key, expires_at, scopes, rate_limit)
VALUES
    (1, 1, 'secret-unhackable-hash', '2025-12-31', ARRAY['read', 'write'], 1000),
    (2, 2, 'totally-hackable-hash', '2025-12-31', ARRAY['read'], 500),
    (3, 3, 'e4ba74cd813a59fd103494a9a5140bfc7db72c57162e849ad24b70ff641a66dd', '2025-12-31', ARRAY['write'], 2000);

-- Language Models
INSERT INTO language_models (model, description, type, metadata)
VALUES
    ('gpt-4', 'General purpose LLM', 'openai', '{}'),
    ('custom-jura', 'Fine-tuned for legal documents', 'custom', '{}'),
    ('hogwarts-database', 'Magical and most powerful model', 'custom', '{"magical": true}');

-- Prompt Templates
INSERT INTO prompt_templates (model_id, user_id, name, content)
VALUES
    (1, 1, 'Basic Chat', 'Hello, how may I assist you today?');

-- Fine-Tuning Jobs
INSERT INTO fine_tuning_jobs (model_id, user_id, status, dataset_path, completed_at)
VALUES
    (2, 1, 'completed', '/data/jura_dataset.csv', '2025-07-01 12:00:00'),
    (3, 3, 'completed', '/data/hogwarts_dataset.csv', NULL);

-- Requests
INSERT INTO requests (api_key_id, model_id, access_time, content, response_type, avg_latency, tokens, response)
VALUES
    (1, 1, '2025-07-07 14:00:00','Hi', 'text', 120, 500, '{"response": "Hello!"}'),
    (2, 2, '2025-07-07 15:00:00', 'Generate a legal text from our entries.','text', 150, 600, '{"response": "Legal text generated."}'),
    (3, 3, '2025-07-07 16:00:00', 'How can I beat Harry?','text', 200, 700, '{"response": "Good luck."}');

-- Usage Logs
INSERT INTO usage_logs (user_id, organization_id, model_id, from_time, to_time, tokens, cost, avg_latency)
VALUES
    (1, 1, 1, '2025-07-01', '2025-07-31', 5000, 49.99, 130),
    (2, 2, 2, '2025-07-01', '2025-07-31', 10000, 99.99, 140),
    (3, 3, 3, '2025-07-01', '2025-07-31', 15000, 149.99, 150);

-- Usage Log Entries
INSERT INTO usage_log_entries (usage_log_id, request_id, tokens, cost, avg_latency)
VALUES
    (1, 1, 500, 5.00, 120),
    (1, 2, 600, 6.00, 150),
    (2, 3, 700, 7.00, 200);

-- Invoices
INSERT INTO invoices (organization_id, user_id, subscription_id, amount, issued_at, status, url)
VALUES
    (1, 1, 1, 99.99, '2025-07-01', 'paid', 'https://billing.example.com/invoice/1'),
    (2, 2, 2, 199.99, '2025-07-01', 'paid', 'https://billing.example.com/invoice/2'),
    (3, 3, 3, 9999.99, '2025-07-01', 'paid', 'https://billing.example.com/invoice/3');

Setup.sql

CREATE TABLE subscriptions
(
    id            SERIAL PRIMARY KEY,
    name          TEXT    NOT NULL,
    requests      INTEGER NOT NULL,
    tokens        INTEGER NOT NULL,
    billing_start DATE    NOT NULL,
    billing_end   DATE    NOT NULL
);

CREATE TABLE organizations
(
    id                SERIAL PRIMARY KEY,
    name              TEXT NOT NULL,
    billing_address   TEXT,
    payment_method    TEXT,
    subscription_plan INTEGER REFERENCES subscriptions (id)
);

CREATE TABLE users
(
    id               SERIAL PRIMARY KEY,
    email            TEXT UNIQUE NOT NULL,
    password_hash    TEXT        NOT NULL,
    session_provider TEXT        NOT NULL,
    subscription_id  INTEGER REFERENCES subscriptions (id),
    deactivated      BOOLEAN DEFAULT FALSE
);

CREATE TABLE api_keys
(
    id              SERIAL PRIMARY KEY,
    user_id         INTEGER REFERENCES users (id),
    organization_id INTEGER REFERENCES organizations (id),
    key             TEXT UNIQUE NOT NULL,
    expires_at      TIMESTAMP   NOT NULL,
    revoked_at      TIMESTAMP,
    scopes          TEXT[]      NOT NULL,
    rate_limit      INTEGER     NOT NULL
);

CREATE TABLE org_users
(
    id              SERIAL PRIMARY KEY,
    user_id         INTEGER REFERENCES users (id),
    organization_id INTEGER REFERENCES organizations (id),
    role            TEXT NOT NULL CHECK (role IN ('admin', 'member')),
    can_finetune    BOOLEAN DEFAULT FALSE,
    can_prompt      BOOLEAN DEFAULT FALSE
);

CREATE TABLE language_models
(
    id          SERIAL PRIMARY KEY,
    model       TEXT  NOT NULL,
    description TEXT,
    type        TEXT  NOT NULL,
    metadata    JSONB NOT NULL
);

CREATE TABLE prompt_templates
(
    id       SERIAL PRIMARY KEY,
    model_id INTEGER REFERENCES language_models (id),
    user_id  INTEGER REFERENCES users (id),
    name     TEXT NOT NULL,
    content  TEXT NOT NULL
);

CREATE TABLE fine_tuning_jobs
(
    id           SERIAL PRIMARY KEY,
    model_id     INTEGER REFERENCES language_models (id),
    user_id      INTEGER REFERENCES users (id),
    status       TEXT NOT NULL CHECK (status IN ('pending', 'in_progress', 'completed', 'failed')),
    dataset_path TEXT NOT NULL,
    completed_at TIMESTAMP
);

CREATE TABLE requests
(
    id            SERIAL PRIMARY KEY,
    api_key_id    INTEGER REFERENCES api_keys (id),
    model_id      INTEGER REFERENCES language_models (id),
    content       TEXT      NOT NULL,
    access_time   TIMESTAMP NOT NULL,
    response_type TEXT      NOT NULL,
    avg_latency   INTEGER   NOT NULL,
    tokens        INTEGER   NOT NULL,
    response      JSONB     NOT NULL
);

CREATE TABLE usage_logs
(
    id              SERIAL PRIMARY KEY,
    user_id         INTEGER REFERENCES users (id),
    organization_id INTEGER REFERENCES organizations (id),
    model_id        INTEGER REFERENCES language_models (id),
    from_time       TIMESTAMP NOT NULL,
    to_time         TIMESTAMP NOT NULL,
    tokens          INTEGER   NOT NULL,
    cost            NUMERIC   NOT NULL,
    avg_latency     INTEGER   NOT NULL
);

CREATE TABLE usage_log_entries
(
    id           SERIAL PRIMARY KEY,
    usage_log_id INTEGER REFERENCES usage_logs (id),
    request_id   INTEGER REFERENCES requests (id),
    tokens       INTEGER NOT NULL,
    cost         NUMERIC NOT NULL,
    avg_latency  INTEGER NOT NULL
);

CREATE TABLE invoices
(
    id              SERIAL PRIMARY KEY,
    organization_id INTEGER REFERENCES organizations (id),
    user_id         INTEGER REFERENCES users (id),
    subscription_id INTEGER REFERENCES subscriptions (id),
    amount          NUMERIC   NOT NULL,
    issued_at       TIMESTAMP NOT NULL,
    status          TEXT      NOT NULL CHECK (status IN ('pending', 'paid', 'overdue', 'cancelled')),
    url             TEXT      NOT NULL
);