lamassu.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.5
-- Dumped by pg_dump version 9.5.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET search_path = public, pg_catalog;

--
-- Name: cash_out_action_types; Type: TYPE; Schema: public; Owner: -
--

CREATE TYPE cash_out_action_types AS ENUM (
    'published',
    'authorized',
    'instant',
    'confirmed',
    'rejected',
    'insufficientFunds',
    'dispenseRequested',
    'dispensed',
    'notified',
    'addedPhone',
    'redeem'
);


--
-- Name: status_stage; Type: TYPE; Schema: public; Owner: -
--

CREATE TYPE status_stage AS ENUM (
    'notSeen',
    'published',
    'authorized',
    'instant',
    'confirmed',
    'rejected',
    'insufficientFunds'
);


--
-- Name: transaction_authority; Type: TYPE; Schema: public; Owner: -
--

CREATE TYPE transaction_authority AS ENUM (
    'timeout',
    'machine',
    'pending',
    'rejected',
    'published',
    'authorized',
    'confirmed'
);


--
-- Name: transaction_stage; Type: TYPE; Schema: public; Owner: -
--

CREATE TYPE transaction_stage AS ENUM (
    'initial_request',
    'partial_request',
    'final_request',
    'partial_send',
    'deposit',
    'dispense_request',
    'dispense'
);


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: bills; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE bills (
    id uuid NOT NULL,
    device_id text NOT NULL,
    denomination integer NOT NULL,
    currency_code text NOT NULL,
    crypto_atoms bigint NOT NULL,
    to_address text NOT NULL,
    cash_in_txs_id uuid NOT NULL,
    device_time bigint NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL,
    crypto_code text DEFAULT 'BTC'::text
);


--
-- Name: cash_in_txs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE cash_in_txs (
    id uuid NOT NULL,
    device_id text NOT NULL,
    to_address text NOT NULL,
    crypto_atoms bigint NOT NULL,
    crypto_code text NOT NULL,
    fiat numeric(14,5) NOT NULL,
    currency_code text NOT NULL,
    fee bigint,
    tx_hash text,
    phone text,
    error text,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: cash_out_actions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE cash_out_actions (
    id integer NOT NULL,
    cash_out_txs_id uuid,
    action cash_out_action_types NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: cash_out_actions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE cash_out_actions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: cash_out_actions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE cash_out_actions_id_seq OWNED BY cash_out_actions.id;


--
-- Name: cash_out_hds; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE cash_out_hds (
    id uuid NOT NULL,
    crypto_code text NOT NULL,
    hd_serial integer NOT NULL,
    swept boolean DEFAULT false NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL,
    last_checked timestamp with time zone DEFAULT now() NOT NULL,
    confirmed boolean DEFAULT false NOT NULL
);


--
-- Name: cash_out_txs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE cash_out_txs (
    id uuid NOT NULL,
    device_id text NOT NULL,
    to_address text NOT NULL,
    crypto_atoms bigint NOT NULL,
    crypto_code text NOT NULL,
    fiat numeric(14,5) NOT NULL,
    currency_code text NOT NULL,
    tx_hash text,
    status status_stage DEFAULT 'notSeen'::status_stage NOT NULL,
    dispensed boolean DEFAULT false NOT NULL,
    notified boolean DEFAULT false NOT NULL,
    redeem boolean DEFAULT false NOT NULL,
    phone text,
    error text,
    created timestamp with time zone DEFAULT now() NOT NULL,
    confirmation_time timestamp with time zone
);


--
-- Name: devices; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE devices (
    device_id text NOT NULL,
    name text NOT NULL,
    cashbox integer DEFAULT 0 NOT NULL,
    cassette1 integer DEFAULT 0 NOT NULL,
    cassette2 integer DEFAULT 0 NOT NULL,
    paired boolean DEFAULT true NOT NULL,
    display boolean DEFAULT true NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL,
    user_config_id integer
);


--
-- Name: dispenses; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE dispenses (
    id integer NOT NULL,
    device_id text NOT NULL,
    transaction_id integer,
    dispense1 integer NOT NULL,
    reject1 integer NOT NULL,
    dispense2 integer NOT NULL,
    reject2 integer NOT NULL,
    error text,
    created timestamp with time zone DEFAULT now() NOT NULL,
    cash_out_txs_id uuid
);


--
-- Name: dispenses_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE dispenses_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: dispenses_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE dispenses_id_seq OWNED BY dispenses.id;


--
-- Name: idempotents; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE idempotents (
    request_id text NOT NULL,
    device_id text NOT NULL,
    body json NOT NULL,
    status integer NOT NULL,
    pending boolean NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: machine_configs; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE machine_configs (
    id integer NOT NULL,
    device_id text NOT NULL,
    data json NOT NULL
);


--
-- Name: machine_configs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE machine_configs_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: machine_configs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE machine_configs_id_seq OWNED BY machine_configs.id;


--
-- Name: machine_events; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE machine_events (
    id uuid NOT NULL,
    device_id text NOT NULL,
    event_type text NOT NULL,
    note text,
    device_time bigint NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: one_time_passes; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE one_time_passes (
    token text NOT NULL,
    name text NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: pairing_tokens; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE pairing_tokens (
    id integer NOT NULL,
    token text,
    created timestamp with time zone DEFAULT now() NOT NULL,
    name text NOT NULL
);


--
-- Name: pairing_tokens_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE pairing_tokens_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: pairing_tokens_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE pairing_tokens_id_seq OWNED BY pairing_tokens.id;


--
-- Name: pending_transactions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE pending_transactions (
    id integer NOT NULL,
    device_fingerprint text NOT NULL,
    session_id uuid NOT NULL,
    incoming boolean NOT NULL,
    currency_code text NOT NULL,
    to_address text NOT NULL,
    satoshis bigint NOT NULL,
    updated timestamp with time zone DEFAULT now() NOT NULL,
    crypto_code text DEFAULT 'BTC'::text
);


--
-- Name: pending_transactions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE pending_transactions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: pending_transactions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE pending_transactions_id_seq OWNED BY pending_transactions.id;


--
-- Name: server_events; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE server_events (
    id integer NOT NULL,
    event_type text NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: server_events_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE server_events_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: server_events_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE server_events_id_seq OWNED BY server_events.id;


--
-- Name: transactions; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE transactions (
    id integer NOT NULL,
    session_id uuid NOT NULL,
    device_fingerprint text,
    to_address text NOT NULL,
    satoshis bigint DEFAULT 0 NOT NULL,
    fiat integer DEFAULT 0 NOT NULL,
    currency_code text NOT NULL,
    fee integer DEFAULT 0 NOT NULL,
    incoming boolean NOT NULL,
    stage transaction_stage NOT NULL,
    authority transaction_authority NOT NULL,
    tx_hash text,
    error text,
    created timestamp with time zone DEFAULT now() NOT NULL,
    crypto_code text DEFAULT 'BTC'::text,
    phone text,
    dispensed boolean DEFAULT false NOT NULL,
    notified boolean DEFAULT false NOT NULL,
    redeem boolean DEFAULT false NOT NULL,
    confirmation_time timestamp with time zone,
    status status_stage DEFAULT 'notSeen'::status_stage NOT NULL
);


--
-- Name: transactions_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE transactions_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: transactions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE transactions_id_seq OWNED BY transactions.id;


--
-- Name: user_config; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE user_config (
    id integer NOT NULL,
    type text NOT NULL,
    data json NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: user_config_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE user_config_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: user_config_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE user_config_id_seq OWNED BY user_config.id;


--
-- Name: user_tokens; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE user_tokens (
    token text NOT NULL,
    name text NOT NULL,
    created timestamp with time zone DEFAULT now() NOT NULL
);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY cash_out_actions ALTER COLUMN id SET DEFAULT nextval('cash_out_actions_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY dispenses ALTER COLUMN id SET DEFAULT nextval('dispenses_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY machine_configs ALTER COLUMN id SET DEFAULT nextval('machine_configs_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY pairing_tokens ALTER COLUMN id SET DEFAULT nextval('pairing_tokens_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY pending_transactions ALTER COLUMN id SET DEFAULT nextval('pending_transactions_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY server_events ALTER COLUMN id SET DEFAULT nextval('server_events_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY transactions ALTER COLUMN id SET DEFAULT nextval('transactions_id_seq'::regclass);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE ONLY user_config ALTER COLUMN id SET DEFAULT nextval('user_config_id_seq'::regclass);


--
-- Name: bills_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY bills
    ADD CONSTRAINT bills_pkey PRIMARY KEY (id);


--
-- Name: cash_in_txs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY cash_in_txs
    ADD CONSTRAINT cash_in_txs_pkey PRIMARY KEY (id);


--
-- Name: cash_out_actions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY cash_out_actions
    ADD CONSTRAINT cash_out_actions_pkey PRIMARY KEY (id);


--
-- Name: cash_out_hds_crypto_code_hd_serial_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY cash_out_hds
    ADD CONSTRAINT cash_out_hds_crypto_code_hd_serial_key UNIQUE (crypto_code, hd_serial);


--
-- Name: cash_out_hds_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY cash_out_hds
    ADD CONSTRAINT cash_out_hds_pkey PRIMARY KEY (id);


--
-- Name: cash_out_txs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY cash_out_txs
    ADD CONSTRAINT cash_out_txs_pkey PRIMARY KEY (id);


--
-- Name: devices_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY devices
    ADD CONSTRAINT devices_pkey PRIMARY KEY (device_id);


--
-- Name: dispenses_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY dispenses
    ADD CONSTRAINT dispenses_pkey PRIMARY KEY (id);


--
-- Name: dispenses_transaction_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY dispenses
    ADD CONSTRAINT dispenses_transaction_id_key UNIQUE (transaction_id);


--
-- Name: idempotents_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY idempotents
    ADD CONSTRAINT idempotents_pkey PRIMARY KEY (request_id);


--
-- Name: machine_configs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY machine_configs
    ADD CONSTRAINT machine_configs_pkey PRIMARY KEY (id);


--
-- Name: machine_events_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY machine_events
    ADD CONSTRAINT machine_events_pkey PRIMARY KEY (id);


--
-- Name: one_time_passes_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY one_time_passes
    ADD CONSTRAINT one_time_passes_pkey PRIMARY KEY (token);


--
-- Name: pairing_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY pairing_tokens
    ADD CONSTRAINT pairing_tokens_pkey PRIMARY KEY (id);


--
-- Name: pending_transactions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY pending_transactions
    ADD CONSTRAINT pending_transactions_pkey PRIMARY KEY (id);


--
-- Name: pending_transactions_session_id_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY pending_transactions
    ADD CONSTRAINT pending_transactions_session_id_key UNIQUE (session_id);


--
-- Name: server_events_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY server_events
    ADD CONSTRAINT server_events_pkey PRIMARY KEY (id);


--
-- Name: transactions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY transactions
    ADD CONSTRAINT transactions_pkey PRIMARY KEY (id);


--
-- Name: transactions_session_id_to_address_stage_authority_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY transactions
    ADD CONSTRAINT transactions_session_id_to_address_stage_authority_key UNIQUE (session_id, to_address, stage, authority);


--
-- Name: user_config_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY user_config
    ADD CONSTRAINT user_config_pkey PRIMARY KEY (id);


--
-- Name: user_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY user_tokens
    ADD CONSTRAINT user_tokens_pkey PRIMARY KEY (token);


--
-- Name: cash_out_hds_confirmed_last_checked_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX cash_out_hds_confirmed_last_checked_idx ON cash_out_hds USING btree (confirmed, last_checked);


--
-- Name: dispenses_device_fingerprint_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX dispenses_device_fingerprint_idx ON dispenses USING btree (device_id);


--
-- Name: server_events_created_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX server_events_created_idx ON server_events USING btree (created);


--
-- Name: transactions_phone_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX transactions_phone_idx ON transactions USING btree (phone);


--
-- Name: transactions_session_id_idx; Type: INDEX; Schema: public; Owner: -
--

CREATE INDEX transactions_session_id_idx ON transactions USING btree (session_id);


--
-- Name: cash_out_actions_session_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY cash_out_actions
    ADD CONSTRAINT cash_out_actions_session_id_fkey FOREIGN KEY (cash_out_txs_id) REFERENCES cash_out_txs(id);


--
-- Name: user_config_id; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY devices
    ADD CONSTRAINT user_config_id FOREIGN KEY (user_config_id) REFERENCES user_config(id);


--
-- PostgreSQL database dump complete
--