00:00:00 In this lesson, we'll architect the data model.
00:00:03 That's the next step that we had to do.
00:00:06 If you take a look at the scope, you'll see that the coding standards and tooling has been done and data models is pending.
00:00:13 And that means architecting or designing the core entities that everything else stands on.
00:00:19 Users, movies, rating, taste profiles, feed picks with their reasons, watch list entries and import runs.
00:00:26 This is only done when the entities and their relationships carry ratings from both onboarding paths, a store taste profile,
00:00:33 and generated picks reasons without a breaking change later.
00:00:38 What's important is that for now you can see that the only checkbox right here is the design or the specification for it that The Architect will generate.
00:00:48 And then after it, we'll be able to develop the data model based on the specification.
00:00:54 So let's simply start by telling it to architect the Data Model.
00:00:57 It immediately recognized that we made a PR and we actually merged that PR back into main.
00:01:04 so it's going to pull the latest changes before it starts writing anything.
00:01:08 it is reading this as a feature design on existing tech stack with the approach tracer bullet and the workflow tier set to full.
00:01:15 It's scope feature 3, so the spec feature will be linked.
00:01:18 And now we can start with the requirements and the shape of the model.
00:01:22 How much of a model should this spec design now, given the tracer bullet approach?
00:01:27 Full target model, migrations sliced, which means that we want it to design every entity all 11 features need as one coherent target,
00:01:35 then land migations slice by slice.
00:01:38 This matches the scopes without a breaking change later bar while keeping the Tracer Bullet thin.
00:01:43 or only the tracer bullet, like the users, movies, ratings, taste profiles and more, and we can add watchlist imports quotas later on.
00:01:52 This is actually what I prefer for now.
00:01:54 How should a user's taste profile be stored?
00:01:57 A taste vector stable, n rows per user, one row per taste facet, label plus vector, start with a single row the average for the Tracer Bullet,
00:02:06 then grow to clustered facets later with no schema change.
00:02:10 Keeps the cheap path open and the good path free.
00:02:13 Or one vector column on the profile row, which is simplest to read and teach.
00:02:17 Broad taste averages into a mushy center and moving the fastest later is a breaking change.
00:02:23 I much prefer the taste vector stable with N rows per user.
00:02:27 Ratings arrive from two paths, a swipe deck and a letterbox export.
00:02:31 So how should they be stored?
00:02:33 One rating column plus a source column.
00:02:35 We want to normalize both paths into a numeric scale.
00:02:39 And finally, a user row.
00:02:40 Where does the app's own user data hang off?
00:02:43 Super based on odd.user and drizzle does not.
00:02:46 So we want have a profile table keyed by odd that user ID.
00:02:51 Next, a few more questions.
00:02:53 Which tables land in the first migration?
00:02:56 The thin thread is sign in, swipe a starter deck, get a feed of picks with reasons.
00:03:01 So maybe we want to get profiles, movies, ratings, taste vectors and picks.
00:03:06 Yep, definitely.
00:03:08 Plus model usage.
00:03:09 No, we don't need that yet.
00:03:11 This is going to come in later.
00:03:12 plus watchlist entries.
00:03:14 this is feature 10 plus import runs.
00:03:16 feature seven.
00:03:17 so all of this can happen later on.
00:03:20 But since we know that we're going to add these features later on, why not go ahead and architect the data model in a way that supports them from the get-go?
00:03:27 So I'll just stick all of them.
00:03:29 A bit more work ahead, but we'll move faster later.
00:03:33 Movie identity.
00:03:34 How should a movie be identified and how is its embedding stored?
00:03:37 UUID primary key, unique TMDB ID, embeding column or movies.
00:03:42 How should the generated feed be represented?
00:03:44 Standalone pick rows, unique per user and movie.
00:03:47 Yep, each row is one movie recommended to one user, carrying its reason, score and create a field.
00:03:52 Yup, this works.
00:03:54 And a swipe deck gives like, dislike and skip.
00:03:57 Does a skip become a row?
00:03:59 Yep.
00:03:59 A rating row with a skipped state.
00:04:01 Just so we know to not show the same movies again.
00:04:04 Okay, so it said all eight tables then.
00:04:07 So the first migration stands up the whole target model.
00:04:10 I will design it around the thin thread first and then mark which tables sit idle until it's their feature's turn.
00:04:16 How should the daily model quota be counted?
00:04:19 This is for the feature number six.
00:04:21 One row per user per day per model with a counter.
00:04:24 Yep, this works.
00:04:25 Rating history, should a rating be one row for user and movie or a history?
00:04:30 One, row, per, user, and, movie, updated in place.
00:04:34 Pick a state, feature eight adds in feed feedback, seen or dismissed, and feature 10 adds saving.
00:04:40 Should the pick stable carry that state now?
00:04:42 Yeah, why not?
00:04:44 And spec 00001 requires audit logging for import runs and account deletions.
00:04:48 Where does that live?
00:04:49 It can live in a generic audit events table, add it now.
00:04:53 Then it's asking me about the shape of the scoped query helper.
00:04:57 Rule one says that it must apply the user ID itself, not trust the caller to.
00:05:02 So generic operations over a registry of user-owned tables.
00:05:06 Yep, I think this works.
00:05:08 Schema layout.
00:05:09 How should drizzle schema be laid out?
00:05:11 One file per entity under source DB schema, re-exported from an index, or a single source DBSchema, everything visible at once.
00:05:20 Well, it's better to have separate files for each table.
00:05:23 how should the fixed value sets be represented?
00:05:26 Like rating source, rating state, pick state and so on.
00:05:29 It can be done via Postgres enums.
00:05:32 And a movie row is created from TMDB before its embedding exists.
00:05:36 How should that be modeled?
00:05:37 Nullable embeding plus a partial index on the pending rows.
00:05:40 Yep, this sounds good to me.
00:05:42 Then two model questions left, plus one the scope says to settle before any migration runs.
00:05:48 A swipe gives like, dislike, skip.
00:05:50 Letterbox gives rating from 0.5 to 5 stars.
00:05:53 how do they share one rating stable?
00:05:55 Well, A swipe row is state like dislike skipped with score null, an import row carries the real rating, nothing is invented,
00:06:04 and the weight a like is worth stays a scoring decision feature 6 can tune without a migration.
00:06:10 Yep, I agree.
00:06:11 Starter Deck.
00:06:12 Feature 6 needs a fixed starter deck of all well-known films.
00:06:16 Where does that live?
00:06:17 A bullion flag, yep.
00:06:18 And the scope says to confirm the SuperBase project region and match Vercel to it before the first migration.
00:06:24 It's going to be based in Europe as that's where I'm based.
00:06:27 You want to choose the one that is closest to you.
00:06:29 and it came back with the assembled model.
00:06:31 nine tables, one migration with the four idle ones marked for the upcoming features.
00:06:37 So there's the profiles table, the movies, ratings, taste vectors, picks, model usage, watchlist entries, import runs and audit events.
00:06:45 These are the relationships, fields and indices.
00:06:49 Does the model match what you described?
00:06:51 Yep, this is exactly what I wanted to get.
00:06:54 We can confirm it.
00:06:55 And with that, the architecture of the data model is coming to an end.
00:07:00 Now that the model has been locked, we have to focus on the surface, enforcement, and failure cases.
00:07:06 What makes a difference between a project and an application?
00:07:09 Let's talk about the deliverables.
00:07:11 What ships with this feature besides the schema and the scope helper?
00:07:14 A seed script for the starter deck from a checked in list.
00:07:18 Yep.
00:07:19 20 well-known films as committed JSON of DMDB IDs.
00:07:22 Yeah, this is going to be useful to quickly see if the app is working.
00:07:26 Row level security deny all written in the migration.
00:07:29 Yup.
00:07:30 A throwaway route that proves the pipe.
00:07:33 A dev-only page that writes and reads only one row through the helper, so the tracer bullet is visibly working, before feature 6. Uh,
00:07:40 not needed, I would say.
00:07:41 And Zod schemas for each identity alongside drizzled tables.
00:07:45 Not now, we can do that later.
00:07:47 Regarding the migrations, how should they be generated and run?
00:07:50 Would DrizzleKit generate?
00:07:51 SQL committed?
00:07:52 Apply over direct URL?
00:07:53 Yep.
00:07:54 rule once remaining gap is that nothing checks a new user own table was registered with the helper.
00:07:59 How should that be closed?
00:08:00 A typed registry.
00:08:01 The helper is the only consumer of plus a documented step.
00:08:04 Yeah, I think this is going to be fine.
00:08:07 And finally, cold start.
00:08:08 What should happen when the taste vector is missing or stale at feed time?
00:08:12 Model it as a state.
00:08:13 No taste vectors row means onboarding is incomplete.
00:08:16 Yep.
00:08:16 We definitely need to go through the onboard or the import for the tastes vector to build.
00:08:23 And the last round of questions, SuperBaseAuth creates a row in AuthUsers.
00:08:27 Who creates the matching profiles row?
00:08:29 A Postgres trigger on AuthUser's insert?
00:08:32 Yep.
00:08:33 And references?
00:08:34 Do we need any?
00:08:35 No, we don't need anything at this point in time.
00:08:39 After some thinking, the spec has been written.
00:08:42 The spec for the data model, alongside the rationale of how we came to build it.
00:08:48 It did the self check, but it's asking me whether we want to cross check it with another model.
00:08:52 And since the data model of the application is one of most important things, I'll say definitely go for it.
00:08:58 Hunt for values and action must produce who sourced this spec never names and decisions the builder would otherwise invent.
00:09:05 Go for, it flag some things and then we'll check out together.
00:09:08 This time it found not one, two, three or four findings.
00:09:13 It found 10. Three blocking, six real and cheap to fix.
00:09:19 So first, nothing writes the account deleted audit row.
00:09:22 AC4 requires it to survive, but only an insert trigger is planned.
00:09:26 Recommended, keep it in the TypeScript rather than adding a second hidden trigger.
00:09:29 Account deletion is not a feature yet, so reward AC 4 to prove the cascade and prove audit events does not cascade.
00:09:36 Yeah, this makes sense.
00:09:37 The migration role may not be allowed to attach a trigger to auth users, and the function needs a security definer with a pinned search path To be fully honest,
00:09:46 I'm not sure exactly what this is referring to, but the recommended approach is to name the exact function shape in the build plan and confirm the roles
00:09:54 grant in step one, which sounds good to me.
00:09:57 and again things with the raw level security, unsure why it's still struggling with how this is handled between Drizzle and SuperBase.
00:10:04 And then some simple cheap fixes, like numeric 2-1 plus a rain check for half steps, maintaining updated add, and so on.
00:10:12 This all is looking good to me, so I will apply the recommended fixes and just how good it is that a secondary model was able to find all of these things
00:10:20 that the initial one didn't.
00:10:22 So let's apply all 10, and then we'll be able to develop our data model.
00:10:26 That's it, 10 fixes applied, ten gaps found, three of them blocking, others not so much, but all of the are now resolved in the spec.
00:10:34 So, let us accept it.
00:10:36 And we're done.
00:10:37 Here's the quick version.
00:10:39 We decided that we are going to do nine tables in one migration and a scoped query helper that applies the user ID itself so no later feature can write
00:10:47 a query that forgets whose data is it reading.
00:10:49 Why?
00:10:50 The project has no test suite, and on this stack, the database cannot check ownership, so the only real defense is removing the unsafe path rather than
00:10:57 remembering to avoid it.
00:10:59 Alongside that, the taste profile is stored as rows rather than one column, so the weak averaged vector that the spec warned us about can grow into taste
00:11:08 facets later with no schema change.
00:11:10 And now we can further read this scope.
00:11:13 It includes the summary of everything that's going to happen, The requirements, user stories, acceptance criteria, and the decisions that we made alongside
00:11:21 the rationale of why we make specific choices.
00:11:24 Here you can see the data model sketch, are we going to include it?
00:11:27 Are we gonna not include?
00:11:29 Relationship between the profiles, columns that we're going include, enums, everything about our database and data models is right here in this file.
00:11:38 You can go through it, read it make some changes if you want to, but here it is with the build plan as well.
00:11:45 So now the next thing is to run clear and then just develop the Data Model.
00:11:50 Once again, just to be safe, let's quickly check how much context did this spend.
00:11:55 Once, again below 150k tokens.
00:11:58 And if I check the usage, it looks like I am at about 5% of my Claude max subscription.
00:12:05 So let us run clear.
00:12:06 Then we will run git pull to pull the latest changes and finally we'll develop the data model.
00:12:12 I'll do it right here.
00:12:13 Clear.
00:12:16 the latest changes.
00:12:18 I could have just done the git pull within the terminal, but this is easier.
00:12:22 It says that it's up to date.
00:12:24 And now we can run develop data model to develop everything that we said we would when architecting the feature.
00:12:31 Before it starts writing any SQL, it says the build plan step one is marked blocking.
00:12:36 We have to confirm the region.
00:12:37 And specifically, it's asking us about the DB access.
00:12:40 It says, I can't reach a real SuperBase project.
00:12:43 So the specs blocking prerequisites, step one, and the apply and confirm, steps six and 10, can be done by me.
00:12:48 How do you want to proceed?
00:12:50 It can build a code, we'll apply it, or we can add the credentials first from Superbase, Or we could authorize the Superbase MCP.
00:13:00 That way, you can confirm everything on its own.
00:13:03 So I'll try to do it through the MCP.
00:13:06 So it stopped developing here as we chose.
00:13:09 And now we have to authorize the SuperBase MCPs.
00:13:12 We have run MCPS and then pick Superbase to authorized it.
00:13:16 Or you can just head over here, search for MCPCervers, find Superbases and authenticate it manually right here.
00:13:23 When I do that, I get a message saying resource must be a valid MCPN point.
00:13:28 I will copy that and I tell it, When I click authenticate, I get this.
00:13:34 It was quickly able to find a real bug in our mcp.json file, where it pointed to the wrong URL.
00:13:40 And we can now copy this mpc reconnect superbase and try to run it.
00:13:45 But then I'd get MCP controls aren't available now.
00:13:49 I'll tell it what I got.
00:13:51 And let's see what it says.
00:13:52 Setting up MCP sometimes takes a bit of time, but trust me, it's worth it in the long run because you don't have to then copy and paste things from SuperBase,
00:14:01 But rather it is communicating with your project and knows exactly what the state of the project is.
00:14:06 So it speeds up development significantly later on.
00:14:10 So what it says is the config change was never made.
00:14:13 We have to modify the MCP JSON to drop the read only.
00:14:17 So, what we can do is copy this URL thing, head over into MCPC JSON and update the URL part.
00:14:25 Then the server is not approved.
00:14:29 If we run Claude MCPList, We can do that in a terminal, but I think it'll tell us that right here as well.
00:14:38 We should be able to see SuperBase pending approval.
00:14:42 And what we have to do is run Claude in the new terminal window, head over into MCP, Superbase, and authenticate.
00:14:50 So let's do.
00:14:51 I'll open up a new Terminal.
00:14:53 Run Claud.
00:14:54 Trust the folder.
00:14:56 and it says two new MCPs found in project.
00:14:58 Three need authentication.
00:15:00 so we can just run MCB.
00:15:03 Choose SuperBase, authenticate.
00:15:07 It'll open up the browser.
00:15:08 We can now properly sign in, select an organization and authorize cloud code.
00:15:14 That's it.
00:15:15 Authentication has been successful and we can continue developing a data model now that SuperBase has connected.
00:15:22 we check that by rerunning the MCP list and now it should say connected, so let's give it a second.
00:15:29 There we go.
00:15:29 Superbase now is connected so we develop the data.
00:15:34 And even though the MCP server is connected, it still doesn't have access to some of its tools.
00:15:39 So what we have to do is restart Claude code and then rerun the command to give it access.
00:15:46 We can do that easily by just closing the current chat window, opening a new Claud code window and just telling it to develop the data model once again.
00:15:56 The beautiful thing about our agentic engineering workflow is that nothing is lost.
00:16:01 Because of the scope and the specs and way it's tracking everything, it can just continue right where it left off, even though we completely cleared the
00:16:11 chat history.
00:16:12 Then it says one blocking prerequisite in the engineer's call, which superbase project this app uses.
00:16:18 It's suggesting some of the active projects I have, but these belong to previous Vibe coding tries of building this movie app.
00:16:25 In this case, we want to make a new project.
00:16:28 And it's pretty cool that we can do it directly through the MCP.
00:16:31 in this, case it saying that a New SuperBase project costs about $10 per month and that, that can create it or use an existing project instead.
00:16:40 I, have many projects already, and I didn't think that creating them would cost anything.
00:16:45 So let me try creating it through, the UI right here.
00:16:48 New project, we can optionally connect GitHub.
00:16:51 We can choose a project name.
00:16:53 we Can use the same one that we use for our repo.
00:16:56 Agenting engineering workflow movies.
00:17:00 So make sure to create one and copy it.
00:17:02 Make sure not to lose this.
00:17:04 That matters.
00:17:05 and then we can create a new project.
00:17:08 We can have up to two free projects across all organizations.
00:17:11 So if we want to create free project, we need to Create a Free Organization.
00:17:16 That's fine.
00:17:16 I'll just enter my JS Mastery, Agentic Engineering.
00:17:23 It can be a personal organization on a free plan.
00:17:26 Now if you head back, We can just create it one more time, create a new strong password, which you need to copy and save,
00:17:34 and now you can create new project for free.
00:17:36 And then you'll be redirected to your new product right here.
00:17:39 Now we can connect it through one of the many ways.
00:17:42 In this case, I think we could just connect through the MCP.
00:17:45 We've already added it.
00:17:46 The only thing I thing our MCPs needs is the name of project.
00:17:50 So I can copy this part right and head back over here and tell it, I created a new free project.
00:18:00 This is the name and the organization.
00:18:02 Can you link that?
00:18:03 And then just paste the names of the project right here, JS Mastery, Agentic Engineering, agentic engineering workflow movies,
00:18:10 and it should be able to connect it on its own.
00:18:13 There we go.
00:18:14 You made it yourself.
00:18:15 Let me find it.
00:18:16 Super baseless projects.
00:18:18 list organizations as well.
00:18:20 The only thing that I'm scared of is whether I connected the right account, because it's possible that, I connect to the wrong one.
00:18:28 Yep, so it cannot find this organization.
00:18:30 So for now, it'll actually proceed with installing Drizzle, Postgres, DrizzleKit and everything else.
00:18:36 And then at the end, It'll tell me how to reconnect it.
00:18:39 Because while connecting the MCP, i used my other account and while creating the project on the web, used another account.
00:18:46 But that's okay, we can sort all of that out.
00:18:48 Having the MCP is still going to prove to be super useful.
00:18:52 And after quite a bit of time, the spec2 is now almost built.
00:18:57 The build passes too, so there's two decisions before we wrap up.
00:19:01 Do we want to save the verify steps derived from the acceptance criteria to this specific feature's verifyMD?
00:19:07 Here, that's recommended because this is the data and ownership feature and most criteria can only be proven against a live database.
00:19:15 So let's save it until we can verify it later.
00:19:18 And also let us commit this to Git.
00:19:20 Then we'll want to connect that SuperBase MCP to the right account.
00:19:24 There we go.
00:19:25 The data model has been developed.
00:19:28 You can see all the files right here.
00:19:30 there's a schema that has tons of different tables.
00:19:34 the database handle, the prepare thing.
00:19:37 The migration is right here, query helper, and more.
00:19:41 Now what is not done is everything needing a live database.
00:19:45 The SuperBase MCP doesn't see the project that we have.
00:19:49 So let's update it.
00:19:50 I'll head over to MCPs, servers, Superbase, clear authentication, And then I will try to authenticate one more time.
00:20:00 This time with the JS Mastery Gentic Engineering.
00:20:04 authorize it, it has been successful.
00:20:07 So if I check connection, It is successfully reconnected.
00:20:11 so now I'll say, I updated the access through the SuperBase MCP connection.
00:20:17 Recheck it now and do everything that you can now that have a successful connection or it's possible that we'll have to restart or clod for it to get access.
00:20:28 But no, it looks like it has it already.
00:20:31 And it is connected.
00:20:33 The project agentic engineering workflow movies, Postgres 17, the region is EU West, which is completely fine.
00:20:39 It found all the extensions, PG vector is available.
00:20:43 it granted the auth user's trigger privilege and Postgress owns the tables.
00:20:48 Yep, is doing everything it needs to.
00:20:51 Applying the migration right now in one transaction exactly as checked in file stands.
00:20:56 Successful applied and now recording it in Drizzle's own journal.
00:21:01 Verifying.
00:21:02 Yep.
00:21:03 Schema is live and correct.
00:21:05 Seeding the starter deck from the checked-in deck file.
00:21:09 AC10 holds, 20 rows after both runs.
00:21:12 Now the real test, creating a user through the actual SuperBaseAuthSignUpPath to see whether the trigger fires.
00:21:19 Sign up to two users through public auth endpoint, and it'll retry it once again.
00:21:23 It looks like we're getting a 429, but that's not really concerning me right now.
00:21:28 What I care most about are the tables and migrations.
00:21:32 So it'll try to directly insert within auth users.
00:21:35 And I love all of these tests that it's performing to check whether everything has been implemented properly.
00:21:40 All of this is coming from the ACs, the acceptance criteria that are held within the spec file.
00:21:46 Already, if you head back over within SuperBase on your projects tables editor, you should be able to see all these tables created for you.
00:21:54 Most of them will be completely empty, but if we head over to movies, you'll see 20 movies that it's seeded right here alongside their TMDB IDs.
00:22:03 And you should be able to see some profiles too, because it inserted them as well.
00:22:07 This is good.
00:22:08 Two auth users, two profiles, IDs matching, no application code involved, and now it'll look at the rating constraints.
00:22:15 Then it went ahead and tried to go through a few more acceptance criteria to fully test it.
00:22:20 AC8 is good.
00:22:22 This is great.
00:22:23 And on keys, zero rows from all nine, including movies, which has 20. That's great AC4 proven completely.
00:22:31 All seven own tables emptied.
00:22:33 Great.
00:22:34 Advisor surfaced one real problem in my own migration.
00:22:38 Okay.
00:22:39 So it fixed it.
00:22:40 The revoke could plausibly break the trigger itself.
00:22:42 Trigger still fires.
00:22:44 Okay.
00:22:45 Cleaning the verification data and checking the advice and rechecking the advisors.
00:22:49 Boat security, the finer warnings are gone.
00:22:52 That's great.
00:22:52 Updating the spec scope and verify checklist.
00:22:55 Yep.
00:22:56 This is great!
00:22:57 Most of it is already proven during development.
00:23:01 that's exactly what I want to see.
00:23:03 So, it'll mark this verify steps in the Spec.
00:23:07 and list what remains and what the next steps are.
00:23:10 It's going to flag that we are at EU West instead of EU Central.
00:23:14 Again, I really don't care.
00:23:15 Both of these are super close to me, so their location doesn't really matter.
00:23:20 But again, it just shows you just how detailed it is and how it doesn' let anything pass.
00:23:24 And there we go, connection restored and everything that could be done without the app running is now done.
00:23:29 The migration is applied and verified, proven by exercise, not by reading.
00:23:34 There's one defect about handling the new user.
00:23:37 We can look into this later on.
00:23:39 And, there's 1 thing that failed, which is the region.
00:23:42 Um, specifically, it says that the project is in EU West, the spec pins EU Central, still EU, so it doesn't matter.
00:23:49 We can either amend the architecture data model to say that, that project, is EU west, not central.
00:23:56 Again, super simple to do that.
00:23:58 So that's exactly what I'll do.
00:24:00 I tell it architect data, model the product is the West not EU central, And then after that is done, we can basically run everything in the verify.md.
00:24:10 These are the steps to test it.
00:24:12 I mean, most of these are already done.
00:24:15 Yep.
00:24:16 Most of this have been completed.
00:24:18 So I think we're good.
00:24:19 Once we modify this little thing, just so it doesn't bug us about it later on, We are done with developing the data model,
00:24:26 which we could also verify right here in this scope.
00:24:29 If you scroll down, you can see data models in progress.
00:24:33 Specifically, the build is, well, almost fully done.
00:24:37 We just have to tell it that the region is good and that everything has been verified.
00:24:41 Uh, we need to finally review it and then document it if needed.
00:24:45 Since this is a big feature, I'll be very thorough and I will run it through the check verify skill, which is something that we're doing for the first
00:24:53 time in this project.
00:24:54 So this just about to finish.
00:24:56 And then in the next lesson, We can focus on final checking and testing of everything that it developed.
