Schema for lm10

CREATE TABLE activity(id BIGINT, "12b_exists" BIGINT, activity_code VARCHAR, activity_type VARCHAR, agencies VARCHAR, counterparty_position VARCHAR, date_of_agreement DATE, explanation VARCHAR, federal_work VARCHAR, form_agreement VARCHAR, no_uei_checkbox VARCHAR, uei VARCHAR, unlisted_agencies VARCHAR, rptId BIGINT, PRIMARY KEY(id), FOREIGN KEY (rptId) REFERENCES filing(rptId));
CREATE TABLE counterparty_contact(city VARCHAR, "name" VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, "zip_code_+_4" VARCHAR, activity_id BIGINT, FOREIGN KEY (activity_id) REFERENCES activity(id));
CREATE TABLE counterparty_organization(city VARCHAR, organization VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, "zip_code_+_4" VARCHAR, activity_id BIGINT, FOREIGN KEY (activity_id) REFERENCES activity(id));
CREATE TABLE expenditure(amount VARCHAR, date DATE, kind VARCHAR, activity_id BIGINT, FOREIGN KEY (activity_id) REFERENCES activity(id));
CREATE TABLE filer(filerType VARCHAR, srFilerId VARCHAR, srNum BIGINT, companyName VARCHAR, companyCity VARCHAR, companyState VARCHAR, PRIMARY KEY(srNum));
CREATE TABLE filing(address1 VARCHAR, address2 VARCHAR, amended VARCHAR, amendment BIGINT, beginDate DATE, city VARCHAR, empLabOrg VARCHAR, empTrdName VARCHAR, endDate DATE, file_checksum UUID, file_path VARCHAR, file_status VARCHAR, filing_url VARCHAR, formFiled VARCHAR, formLink VARCHAR, oID BIGINT, paperOrElect VARCHAR, promiseDate BIGINT, receiveDate DATE, registerDate DATE, repOrgsCnt BIGINT, rptId BIGINT, srFilerId BIGINT, srNum BIGINT, state VARCHAR, subLabOrg1 VARCHAR, subLabOrg2 VARCHAR, yrCovered BIGINT, zip BIGINT, PRIMARY KEY(rptId), FOREIGN KEY (srNum) REFERENCES filer(srNum));
CREATE TABLE lm10(rptId BIGINT, file_number BIGINT, formFiled VARCHAR, period_begin DATE, period_through DATE, type_of_organization VARCHAR, records_hosted_at_other_address VARCHAR, records_hosted_with_principal_officer VARCHAR, records_hosted_with_reporting_employer VARCHAR, FOREIGN KEY (rptId) REFERENCES filing(rptId));
CREATE TABLE organization(promiseDate DATE, oID BIGINT, rptId BIGINT, empLabOrg VARCHAR, city VARCHAR, state VARCHAR, FOREIGN KEY (rptId) REFERENCES filing(rptId));
CREATE TABLE other_address(rptId BIGINT, city VARCHAR, "name" VARCHAR, organization VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, title VARCHAR, "zip_code_+_4" VARCHAR, FOREIGN KEY (rptId) REFERENCES filing(rptId));
CREATE TABLE principal_officer(rptId BIGINT, city VARCHAR, "name" VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, "zip_code_+_4" VARCHAR, FOREIGN KEY (rptId) REFERENCES filing(rptId));
CREATE TABLE raw_activity(rptId BIGINT, activity_id BIGINT, "12b_exists" VARCHAR, activity_code VARCHAR, activity_type VARCHAR, agencies VARCHAR, counterparty_position VARCHAR, date_of_agreement VARCHAR, explanation VARCHAR, federal_work VARCHAR, form_agreement VARCHAR, no_uei_checkbox VARCHAR, uei VARCHAR, unlisted_agencies VARCHAR);
CREATE TABLE raw_counterparty_contact(rptId BIGINT, activity_id BIGINT, "order" BIGINT, city VARCHAR, "name" VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, "zip_code_+_4" VARCHAR);
CREATE TABLE raw_counterparty_organization(rptId BIGINT, activity_id BIGINT, "order" BIGINT, city VARCHAR, organization VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, "zip_code_+_4" VARCHAR);
CREATE TABLE raw_expenditure(rptId BIGINT, activity_id BIGINT, "order" BIGINT, amount VARCHAR, date VARCHAR, kind VARCHAR);
CREATE TABLE raw_filer(filerType VARCHAR, srFilerId VARCHAR, srNum BIGINT, companyName VARCHAR, companyCity VARCHAR, companyState VARCHAR);
CREATE TABLE raw_filing(_key VARCHAR, address1 VARCHAR, address2 VARCHAR, amended VARCHAR, amendment VARCHAR, beginDate VARCHAR, city VARCHAR, empLabOrg VARCHAR, empTrdName VARCHAR, endDate VARCHAR, file_checksum VARCHAR, file_path VARCHAR, file_status VARCHAR, filing_url VARCHAR, formFiled VARCHAR, formLink VARCHAR, oID VARCHAR, paperOrElect VARCHAR, promiseDate VARCHAR, receiveDate VARCHAR, registerDate VARCHAR, repOrgsCnt VARCHAR, rptId VARCHAR, srFilerId VARCHAR, srNum VARCHAR, state VARCHAR, subLabOrg1 VARCHAR, subLabOrg2 VARCHAR, yrCovered VARCHAR, zip VARCHAR);
CREATE TABLE raw_lm10(rptId VARCHAR, file_number VARCHAR, formFiled VARCHAR, period_begin VARCHAR, period_through VARCHAR, type_of_organization VARCHAR, records_hosted_at_other_address VARCHAR, records_hosted_with_principal_officer VARCHAR, records_hosted_with_reporting_employer VARCHAR);
CREATE TABLE raw_organization(promiseDate VARCHAR, oID BIGINT, rptId BIGINT, empLabOrg VARCHAR, city VARCHAR, state VARCHAR);
CREATE TABLE raw_other_address(rptId VARCHAR, _key VARCHAR, city VARCHAR, "name" VARCHAR, organization VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, title VARCHAR, "zip_code_+_4" VARCHAR);
CREATE TABLE raw_principal_officer(rptId VARCHAR, _key VARCHAR, city VARCHAR, "name" VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, "zip_code_+_4" VARCHAR);
CREATE TABLE raw_reportable_activity(rptId BIGINT, _key BIGINT, answer VARCHAR, code VARCHAR, n_responses BIGINT, question VARCHAR);
CREATE TABLE raw_reporting_employer(rptId VARCHAR, _key VARCHAR, attention_to VARCHAR, city VARCHAR, employer VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, title VARCHAR, trade_name VARCHAR, "zip_code_+_4" VARCHAR);
CREATE TABLE raw_signature(rptId VARCHAR, _key VARCHAR, on_date VARCHAR, signed VARCHAR, telephone_number VARCHAR, title VARCHAR);
CREATE TABLE reportable_activity(rptId BIGINT, answer VARCHAR, code VARCHAR, n_responses BIGINT, question VARCHAR, FOREIGN KEY (rptId) REFERENCES filing(rptId));
CREATE TABLE reporting_employer(rptId BIGINT, attention_to VARCHAR, city VARCHAR, employer VARCHAR, "po_box,_bldg,_room_no,_if_any" VARCHAR, state VARCHAR, street VARCHAR, title VARCHAR, trade_name VARCHAR, "zip_code_+_4" VARCHAR, FOREIGN KEY (rptId) REFERENCES filing(rptId));
CREATE TABLE signature(rptId BIGINT, on_date DATE, signed VARCHAR, telephone_number VARCHAR, title VARCHAR, FOREIGN KEY (rptId) REFERENCES filing(rptId));