root problem is a slow migration

unique connectiontype id constraint wasn’t a problem bc it didn’t crash server

models.sequelize.sync crashed server bc it didn’t have a catch

so the belongsto relation between connection and connectionType wasn’t being enforced, so it was possible to assign a connection’s connectionType id to a type that didn’t exist in the db

which would cause crashes

lucas wrote a script to look for connections with non-existent type. and then deleted those

there was also a similar error on the notifications table

this allowed the server to start, and to properly enforce the connection type constraint

the notification cardids foreignkey constraint was missing / not enforced

server crashed bc sync took very long, bc of foreign key constraints

CleanShot-2024-03-24-at-20.59.37.mp4.zip lucas’ walkthrough of the problem

so i need to prevent this from happening

Remove cardid constraint

heroku kills server if this doesn’t complete after 30s

This is the script to find corrupted notifications:

select notifications.* from notifications
left outer join cards on notifications."cardId" = cards.id
where notifications."cardId" is not null and cards.id is null order by "createdAt" desc;
Now

long running process, from sync alter:true

ALTER TABLE "cards" ALTER COLUMN "x" DROP NOT NULL;
ALTER TABLE "cards" ALTER COLUMN "x" DROP DEFAULT;
ALTER TABLE "cards" ALTER COLUMN "x" TYPE INTEGER;

This is the script to create the foreign key constraint: ALTER TABLE “notifications” ADD FOREIGN KEY (“cardId”) REFERENCES “cards” (“id”) ON DELETE SET NULL ON UPDATE CASCADE; feel free to set ‘ON DELETE CASCADE’

And associations. Equalize assisiations now do nothing and can be removed from models.js

if needed, may not be needed after sync success

w alter: true app starts by dropping all constraints and rebuilding thenm DROP CONSTRAINT followed by ALTERs

write a migration that specifies all the existing table names, columns, contraints, and indexes

edit models to create new dbs

this gets real slow eventually

initialize all the existing tables in your first script and from then on just use it to alter stuff

migrations needed for new columns

so when you git pull for the first time you can just run the migrations and start developing

so the model files are like a bridge: the migrations change the actual db, the model files tell sequlize about the current state of the db

basically it’s just that extra migration step on top, but the model parts are the same as before right?

add new indexes

sequelize CLI will create a new table where it will keep a log of all migrations it already executed. This will ensure that no migration is executed twice.

I would develop this script with a completely empty database.

> does anything get deleted or overwritten ?

maybe there is a way to duplicate heroku dbs for prod testing

It shouldn’t because the migration script will usually use a ‘create table’ query which will fail and stop the whole migration.

handles fails without removing failed items from queue?

Once the script is complete we can figure out how to fake the migration log in the prod DB so it doesn’t start with the first initial migration.

personalBlogSubscribers_email_key in personalBlogSubscribers table

users table: duplicate users_email_key and users_appleAppAccountToken_key indexes