db_push_uprova

Block for upserting uprova application data into the public.uprova table in the staging database.

The table stores uprova-specific attributes keyed by application_key. The block uses a parameterised INSERT ... ON CONFLICT ... DO UPDATE statement defined in main.sql to upsert records.

Table DDL

The public.uprova table can be created with the following SQL, following the style of k8s/mlgraph-db/init-configmap.yaml:

-- Connect to postgres database as superuser
\c postgres;

-- Create databases
CREATE DATABASE staging;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE staging TO cpflowxuser;

-- Connect to staging database and create uprova table
\connect staging;

CREATE TABLE IF NOT EXISTS uprova (
    application_key TEXT PRIMARY KEY,
    application_timestamp TIMESTAMP NOT NULL,
    educationlevel TEXT NULL,
    employmentstatus TEXT NULL,
    lengthatbank TEXT NULL,
    lengthatjob NUMERIC NULL,
    ownhome BOOLEAN NULL,
    payfrequency TEXT NULL,
    monthsatresidence NUMERIC NULL,
    state TEXT NULL,
    zip TEXT NULL
);

CREATE INDEX IF NOT EXISTS idx_uprova_application_key ON uprova (application_key);
CREATE INDEX IF NOT EXISTS idx_uprova_application_timestamp ON uprova (application_timestamp);