Skip to content

Instantly share code, notes, and snippets.

@jay-babu

jay-babu/cohort.sql

Created September 13, 2024 15:13
Show Gist options
  • Save jay-babu/7c50bc733d38b95eec8dbcd4d5eb1d5d to your computer and use it in GitHub Desktop.
Save jay-babu/7c50bc733d38b95eec8dbcd4d5eb1d5d to your computer and use it in GitHub Desktop.
Download ZIP
Schema
Raw
cohort.sql
create table cohort
(
id bigserial
primary key,
name text not null,
created_at timestamp with time zone default CURRENT_TIMESTAMP,
updated_at timestamp with time zone default CURRENT_TIMESTAMP
);
Raw
cohort_item.sql
create table cohort_item
(
id bigserial
constraint cohort_item_pk
primary key,
cohort_id bigint not null
constraint cohort_item_cohort_id_fk
references cohort
on update cascade on delete restrict,
name text not null,
barcode varchar(255),
barcode2 varchar(255),
barcode3 varchar(255),
barcode4 varchar(255),
case_quantity integer default 1 not null
constraint positive_case_quantity
check ((case_quantity IS NULL) OR (case_quantity >= 0)),
size numeric(10, 4),
size_unit varchar(10),
alcohol_by_volume text,
deposit_multiplier integer not null,
environment_fee_multiplier integer not null,
parent_cohort_item_id bigint
constraint cohort_item_cohort_item_id_fk
references cohort_item
on update cascade on delete set null,
parent_quantity integer,
notes text,
changed_by varchar(255),
created_at timestamp with time zone default now() not null,
updated_at timestamp with time zone default now() not null,
migration_id varchar(255),
parent_migration_id text,
department bigint
constraint cohort_item_department_id_fk
references department
on update cascade,
brand text,
pack_size integer
constraint positive_pack_size
check ((pack_size IS NULL) OR (pack_size >= 0)),
alcoholic boolean default false not null,
vintage text,
constraint cohort_item_pk_2
unique (cohort_id, migration_id),
constraint valid_parent_item
check ((parent_cohort_item_id IS NULL) OR
((parent_cohort_item_id IS NOT NULL) AND (parent_quantity IS NOT NULL) AND (parent_quantity > 0))),
constraint valid_size
check ((size IS NULL) OR ((size IS NOT NULL) AND (size_unit IS NOT NULL)))
);
Raw
cohort_item_barcode.sql
create table cohort_item_barcode
(
cohort_item_id bigint not null
constraint cohort_item_barcode_cohort_item_id_fk
references cohort_item
on update cascade on delete cascade,
barcode varchar(255) not null,
created_at timestamp with time zone default now() not null,
constraint cohort_item_barcode_pk
primary key (cohort_item_id, barcode)
);
Raw
cohort_item_vendor_item.sql
create table cohort_item_vendor_item
(
cohort_item_id bigint not null
constraint cohort_item_vendor_item_cohort_item_id_fk
references cohort_item
on update cascade on delete cascade,
vendor varchar(255) not null
constraint cohort_item_vendor_item_vendor_fk
references vendor
on update cascade on delete cascade,
sku varchar(255),
updated_at timestamp with time zone default now() not null,
created_at timestamp with time zone default now() not null,
changed_by varchar(255),
constraint cohort_item_vendor_item_pk
primary key (cohort_item_id, vendor)
);
Raw
department.sql
create table department
(
name varchar(255) not null,
tax numeric(38, 4) not null,
bottledeposit numeric(38, 2) not null,
environmentfee numeric(38, 2) not null,
updated_at timestamp with time zone default now() not null,
id bigserial
primary key,
cohort_id bigint
references cohort
on delete cascade,
has_revenue boolean default true not null,
constraint department_pk
unique (cohort_id, name)
);
Raw
entity.sql
create table entity
(
name varchar(255),
id bigint default nextval('entity_id_seq'::regclass) not null
constraint entity_pk
primary key,
credit_card_merchant_application_id varchar(255),
credit_card_merchant_id varchar(255),
address varchar(255),
fintech_host varchar(255),
fintech_user varchar(255),
fintech_passwd varchar(255),
fintech_path varchar(255),
payment_processor text,
payment_fee_fixed numeric(8, 4) default 0.10 not null,
payment_fee_percent numeric(7, 4) default 1.80 not null,
updated_at timestamp with time zone default now() not null,
phone_number text,
cohort_id bigint not null
constraint entity_cohort_id_fk
references cohort
on update cascade,
location_id text,
timezone text default 'America/New_York'::text not null,
card_not_present_payment_fee_fixed numeric(10, 4) not null,
card_not_present_payment_fee_percent numeric(10, 4) not null,
surcharge_fee boolean not null,
constraint credit_card_processor_stripe
check ((payment_processor IS NULL) OR (payment_processor <> 'stripe'::text) OR
((credit_card_merchant_application_id IS NOT NULL) AND (credit_card_merchant_id IS NOT NULL) AND
(payment_fee_fixed IS NOT NULL) AND (payment_fee_percent IS NOT NULL))),
constraint fintech_onboarded
check ((fintech_host IS NULL) OR
((fintech_host IS NOT NULL) AND (fintech_passwd IS NOT NULL) AND (fintech_user IS NOT NULL) AND
(fintech_path IS NOT NULL)))
);
Raw
entity_item.sql
create table entity_item
(
id bigserial
primary key,
entity_id bigint not null,
cohort_item_id bigint not null
constraint entity_item_cohort_item_id_fk
references cohort_item
on update cascade on delete cascade,
price numeric(10, 2) not null,
minimum_price numeric(10, 2),
cost numeric(10, 2),
discount_allowed boolean default true not null,
quantity numeric(10, 4) default 0 not null,
aisle_location text,
fridge_location text,
preferred_vendor_id varchar(255)
constraint entity_item_vendor_name_fk_2
references vendor
on update cascade on delete set null,
last_quantity_received integer,
last_vendor_id varchar(255)
constraint entity_item_vendor_name_fk
references vendor
on update cascade on delete set null,
changed_by varchar(255),
created_at timestamp with time zone default now() not null,
updated_at timestamp with time zone default now() not null,
migration_id varchar(255),
cyjansold numeric(10, 4) default 0 not null,
cyfebsold numeric(10, 4) default 0 not null,
cymarsold numeric(10, 4) default 0 not null,
cyaprsold numeric(10, 4) default 0 not null,
cymaysold numeric(10, 4) default 0 not null,
cyjunsold numeric(10, 4) default 0 not null,
cyjulsold numeric(10, 4) default 0 not null,
cyaugsold numeric(10, 4) default 0 not null,
cysepsold numeric(10, 4) default 0 not null,
cyoctsold numeric(10, 4) default 0 not null,
cynovsold numeric(10, 4) default 0 not null,
cydecsold numeric(10, 4) default 0 not null,
pyjansold numeric(10, 4) default 0 not null,
pyfebsold numeric(10, 4) default 0 not null,
pymarsold numeric(10, 4) default 0 not null,
pyaprsold numeric(10, 4) default 0 not null,
pymaysold numeric(10, 4) default 0 not null,
pyjunsold numeric(10, 4) default 0 not null,
pyjulsold numeric(10, 4) default 0 not null,
pyaugsold numeric(10, 4) default 0 not null,
pysepsold numeric(10, 4) default 0 not null,
pyoctsold numeric(10, 4) default 0 not null,
pynovsold numeric(10, 4) default 0 not null,
pydecsold numeric(10, 4) default 0 not null,
last_receive_date timestamp with time zone,
last_order_date timestamp with time zone,
exclusive boolean default false,
case_cost numeric(10, 2),
variable_cost_percent numeric(10, 2),
constraint entity_item_pk2
unique (entity_id, migration_id),
constraint entity_item_pk3
unique (entity_id, cohort_item_id)
);
Raw
transaction_closings.sql
create table transactionclosings
(
id integer generated by default as identity
constraint transactionclosings_pk
primary key,
registernumber integer not null,
employeeid integer not null
constraint transactionclosings_employee_id_fk
references employee,
opendatetime timestamp with time zone not null,
closedatetime timestamp with time zone not null,
totaltax numeric(38, 2) not null,
totalsale numeric(38, 2) not null,
totalbottlefee numeric(38, 2) not null,
totalenvironmentfee numeric(38, 2) not null,
registeropeningbalance numeric(38, 2),
registerclosingbalance numeric(38, 2),
totalcreditcardsale numeric(38, 2) not null,
totaldiscount numeric(38, 2) not null,
totalothersale numeric(38, 2) not null,
totalchecksale numeric(38, 2) not null,
totalsubtotal numeric(38, 2) not null,
entity_id bigint not null
constraint transactionclosings_entity_id_fk
references entity
on update cascade,
total_drizly_sale numeric(38, 2) default 0 not null,
total_doordash_sale numeric(38, 2) default 0 not null,
total_online_sale numeric(38, 2) default 0 not null,
total_gift_card_sale numeric(38, 2) default 0 not null,
total_cash_sale numeric(38, 2) generated always as ((
((((((totalsale - totalcreditcardsale) - totalchecksale) - totalothersale) - total_drizly_sale) -
total_doordash_sale) - total_online_sale) - total_gift_card_sale)) stored not null,
updated_at timestamp with time zone default now() not null,
migration_id varchar(255),
constraint transactionclosings_pk_entity_migration_id
unique (entity_id, migration_id)
);
Raw
transaction_items.sql
create table transactionitems
(
id integer generated by default as identity
constraint transactionitems_pk
primary key,
quantity integer not null,
price numeric(38, 2) not null,
tax numeric(38, 2) not null,
upccode varchar(255),
name text not null,
totalprice numeric(38, 2) not null,
transactionfk integer not null
constraint transactionitems_transactions_id_fk
references transactions
on update cascade on delete cascade,
discount numeric(38, 2) not null,
bottledeposit numeric(38, 2) not null,
environmentfee numeric(38, 2) not null,
cost numeric(38, 2),
profit numeric(38, 2) generated always as (transaction_item_calc_profit(price, unit_cost => cost,
quantity => quantity,
discount => discount)) stored,
subtotal numeric(38, 2) generated always as (transaction_item_calc_subtotal(price, quantity, discount)) stored not null,
net_unit_price numeric(38, 2) generated always as ((COALESCE(price, (0)::numeric) -
(COALESCE(discount, (0)::numeric) /
(COALESCE(NULLIF(quantity, 0), 1))::numeric))) stored not null,
updated_at timestamp with time zone default now() not null,
created_date timestamp with time zone default CURRENT_TIMESTAMP not null,
parent_id integer,
tax_rate numeric(10, 4),
migration_transaction_id varchar(255),
entity_item_id bigint
constraint transactionitems_entity_item_id_fk
references entity_item
on update cascade on delete set null,
department_id bigint
constraint transactionitems_department_id_fk
references department
on update cascade,
original_price numeric(38, 2),
version bigint default 1 not null,
tax_exempt boolean default false not null,
markup numeric(10, 4) generated always as (transaction_item_calc_markup_decimal(unit_cost => cost,
quantity => quantity,
price => price,
discount => discount)) stored,
gross_profit numeric(10, 4) generated always as (transaction_item_calc_gross_profit_decimal(
unit_cost => cost, quantity => quantity, price => price, discount => discount)) stored,
migration_id text,
pack_size integer
constraint pack_size_null_or_greater_than_0
check ((pack_size IS NULL) OR (pack_size >= 0))
);
Raw
transactions.sql
create table transactions
(
amountreceived numeric(38, 2),
id integer generated by default as identity
constraint transactions_pk
primary key,
transactiontotal numeric(38, 2),
createddate timestamp with time zone default CURRENT_TIMESTAMP,
paymentform varchar(255),
registernumber integer not null,
creditcardamountreceived numeric(38, 2) not null,
checkamountreceived numeric(38, 2) not null,
otheramountreceived numeric(38, 2) not null,
closingsid integer
constraint transactions_transactionclosings_id_fk
references transactionclosings
on update cascade on delete set null,
payinconfigid varchar,
entity_id bigint not null
constraint transactions_entity_id_fk
references entity
on update cascade,
void_reason varchar(255),
customer_id bigint
constraint transactions_customer_id_fk
references customer
on update cascade on delete set null,
updated_at timestamp with time zone not null,
payin_id2 varchar,
payin_id3 varchar,
drizly_amount_received numeric(38, 2) not null,
doordash_amount_received numeric(38, 2) not null,
online_amount_received numeric(38, 2) not null,
government_id_number text,
government_id_jurisdiction text,
status text default 'CREATED'::text not null,
paid_at timestamp with time zone,
gift_card_amount_received numeric(38, 2) not null,
last_four_1 varchar(4),
last_four_2 varchar(4),
last_four_3 varchar(4),
brand_1 varchar(255),
brand_2 varchar(255),
brand_3 varchar(255),
payin_amount_1 numeric(10, 2),
payin_amount_2 numeric(10, 2),
payin_amount_3 numeric(10, 2),
parent_id integer
constraint transactions_transactions_id_fk
references transactions
on update cascade on delete cascade,
migration_id varchar(255),
migration_closing_id varchar(255),
gift_card_id bigint
constraint transactions_gift_card_id_fk
references gift_card
on update cascade on delete set null,
payment_processor text,
changed_by text,
version bigint default 1 not null,
sales_channel text not null,
payment_fee_percent numeric(10, 4),
payment_fee_fixed numeric(10, 4),
card_present boolean,
points_amount_received numeric(38, 2),
cash_amount_received numeric(38, 2) generated always as (
CASE
WHEN (amountreceived = (0)::numeric) THEN (0)::numeric
ELSE ((((((((transactiontotal - creditcardamountreceived) - checkamountreceived) - otheramountreceived) -
drizly_amount_received) - doordash_amount_received) - online_amount_received) -
gift_card_amount_received) - points_amount_received)
END) stored not null,
credit_card_id text,
credit_card_fee numeric(10, 4),
constraint transactions_pk_entity_migration_id
unique (entity_id, migration_id),
constraint transactions_government_id_fk
foreign key (government_id_number, government_id_jurisdiction) references government_id
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment