init.sql

DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS features;
DROP TABLE IF EXISTS experiments;
DROP TABLE IF EXISTS rules;
DROP TABLE IF EXISTS postprocessed_transactions;


CREATE TABLE IF NOT EXISTS transactions (
    transaction_id BIGINT, 
    from_customer_id BIGINT, 
    to_customer_id BIGINT, 
    created_at BIGINT,
    amount FLOAT,
    latitude FLOAT, 
    longitude FLOAT,
    record_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX transaction_from_customer_id_index ON transactions(from_customer_id);
CREATE INDEX transaction_to_customer_id_index ON transactions(to_customer_id);


CREATE TABLE IF NOT EXISTS customers (
    customer_id BIGINT PRIMARY KEY,
    created_at BIGINT,
    name VARCHAR,
    ssn VARCHAR,
    date_of_birth VARCHAR,
    email VARCHAR,
    latitude VARCHAR,
    longitude VARCHAR,
    record_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX customers_customer_id ON customers(customer_id);

COPY customers(customer_id, created_at, name, ssn, date_of_birth, email, latitude, longitude)
FROM '/docker-entrypoint-initdb.d/customers.csv'
DELIMITER ','
CSV HEADER;


CREATE TABLE IF NOT EXISTS features (
    customer_id BIGINT PRIMARY KEY,
    features VARCHAR,
    record_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX features_customer_id ON features(customer_id);


CREATE TABLE IF NOT EXISTS experiments (
    experiment_id SERIAL PRIMARY KEY,
    experiment BYTEA,
    active BOOLEAN,
    record_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE IF NOT EXISTS rules (
    rule_id SERIAL PRIMARY KEY,
    rule BYTEA,
    active BOOLEAN,
    record_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE IF NOT EXISTS postprocessed_transactions (
    start_window TIMESTAMP, 
    end_window TIMESTAMP,
    transaction_type VARCHAR,
    amount_bucket VARCHAR,
    rules VARCHAR,
    experiments VARCHAR,
    attempted_transactions INT,
    approved_transactions INT,
    attempted_amount FLOAT,
    approved_amount FLOAT,
    record_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);