1 files changed, 34 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index c5bcc879..86d5c5ec 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -58,12 +58,17 @@ CREATE TYPE settlement_type AS ENUM('Delivery', 'Cash');
 
 CREATE TYPE beta_type AS ENUM('IG', 'CRT', 'EU', 'SUBPRIME', 'XO');
 
+CREATE TYPE account_type AS ENUM('Cash', 'Brokerage', 'Fcm', 'Future');
+
 CREATE TABLE accounts(
        code varchar(5) PRIMARY KEY,
        name text,
-       custodian text,
-       cash_account text,
-       counterparty varchar(12) REFERENCES counterparties(code))
+       custodian text NOT NULL,
+       cash_account text NOT NULL,
+       counterparty varchar(12) REFERENCES counterparties(code),
+       fund fund NOT NULL,
+       account_type account_type NOT NULL,
+       active boolean NOT NULL);
 
 CREATE TABLE counterparties(code varchar(12) PRIMARY KEY,
        name text,
@@ -87,7 +92,8 @@ CREATE TABLE counterparties(code varchar(12) PRIMARY KEY,
 
 CREATE INDEX ON counterparties(name);
 
-CREATE TABLE bonds(id serial primary key,
+-- deprecated
+CREATE TABLE bond_old(id serial primary key,
        dealid varchar(28) UNIQUE,
        fund fund NOT NULL DEFAULT 'SERCGMAST';
        lastupdate timestamp DEFAULT now(),
@@ -112,6 +118,30 @@ CREATE TABLE bonds(id serial primary key,
        accrued_payment float,
        CONSTRAINT bonds2_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL));
 
+CREATE TABLE bonds(id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
+       dealid varchar(28) GENERATED ALWAYS AS ('SC_'||CASE WHEN asset_class ='Subprime' THEN 'SUB'
+                                                           WHEN asset_class='CLO' THEN 'CLO'
+                                                           WHEN asset_class='CSO' THEN 'CSO' END
+                                                    ||id::text) STORED,
+       folder bond_strat NOT NULL,
+       cp_code varchar(12) NOT NULL REFERENCES counterparties(code) ON UPDATE CASCADE,
+       trade_date date NOT NULL,
+       settle_date date NOT NULL,
+       cusip varchar(9),
+       isin varchar(12),
+       identifier varchar(12),
+       description varchar(32) NOT NULL,
+       buysell bool NOT NULL,
+       faceamount float NOT NULL,
+       price float NOT NULL,
+       accrued float,
+       asset_class asset_class,
+       ticket text,
+       principal_payment float,
+       accrued_payment float,
+       current_face float,
+       CONSTRAINT bonds_check CHECK (cusip IS NOT NULL OR isin IS NOT NULL));
+
 CREATE TRIGGER dealid
        AFTER INSERT ON bonds
        FOR EACH ROW