Project Description to Actionable Plan and DB Schema

First, let's talk a bit about project management. Boring, I know. We want to dive into coding, right? But that's exactly the most typical mistake developers make, especially freelancers.

The beginning stage of the project is when the client is still actively involved, and that's the best chance for us to ask them questions and minimize the risks of something unplanned happening.

When starting the project, my first goal is to turn the client's initial description into an actionable plan with milestones.

So, here's the description of the project client (me) gave to us.

Project Description from Client

LinksLetter: General idea

LinksLetter will be a system for people to manage their bookmark links saved from the internet and then compile them into newsletter issues to be sent via external email campaign provider(s).

No need for homepage, it will be a semi-internal system, so the homepage should immediately redirect to the login form.

Also no need for any fancy design, any pre-built template or kit will be fine, as long as it doesn't look amateur.

The system will work with three "objects":

• Users
• Links
• Newsletter issues

Users

Users can register, login and reset password: typical Auth functionality.

There should be functionality to register / log in with Google.

After logging in, there should be two menu items: Links and Issues. More on them below.

Managing Links

Users can add links either via manual CRUD form.

Links should have these fields:

• URL (string, required)
• Title (string, required)
• Description (text, optional)
• Issue ID (nullable, foreign key to "issues", null until assigned to a specific issue)
• Position (integer, optional, later automatically assigned but reorderable)
• User ID (foreign key to users, automatically filled-in)

Another option is to import links from external API. We will use Pocket for this: https://getpocket.com/home Every user may connect their own Pocket account. On the "Links" page, there should be a button "Import from Pocket" that would import all links with a certain tag from Pocket.

Creating Newsletter Issues

Issue, or in other words, newsletter "campaign" or "curated list" is a list of links to be sent to the newsletter subscribers.

The menu "Issues" should contain the list of all issues of that user, and a button "Compile new issue".

There are three steps in sending a newsletter issue:

1. Compile the issue links from the list (in "Links" menu)
2. Add subject/header/footer texts
3. Click "Send" or "Schedule"

Issues should have these fields:

• Subject (string, required)
• Links (one-to-many relationship, by default all non-used links are added to the next issue)
• Header text (text, optional)
• Footer text (text, optional)
• Links HTML text (nullable, automatically constructed but can be edited)
• Sent at (timestamp, default NULL which then means it's a "draft")
• User ID (foreign key to users, automatically filled-in)

I think the best workflow would be to manage the links in the "Links" section and then click on "Compile new issue" which would contain all the un-used links automatically with the preview of that list, user would have to just add subject/header/footer and click "Send" or "Schedule for Later".

"Send" or "Schedule" would form the HTML code of the issue (just a simple <ul> + <li> list with header/footer added), save it into DB and call the external API to send the issue immediately or at a chosen time if "Schedule" is clicked (there should be a datetimepicker).

To send the newsletter, we will use external Mailchimp API: https://mailchimp.com

After newsletter is sent, the user is back to the "Issues" menu item to see the list of the past issues. The table should show the subject and the number of links included, with ability to preview the issue HTML code. Newsletter issues cannot be edited or deleted, if the user wants to cancel the scheduled newsletter, they do it on Mailchimp directly.

Project Description into Action Plan

Here's a typical broad plan I use in many projects:

• Milestone 1: Show SOMETHING to client as early as possible
• Milestone 2: Feature ABC
• Milestone 3: Feature XYZ
• ...
• Milestone X: Public Launch

Each milestone marks showing something to the client and discussing the progress to get feedback.

The list above is for the CLIENT to agree with. Next, you need to detalize it with technical details for YOURSELF.

Inside the milestone, there are actual tasks to be done. Example of the first milestone:

• Laravel Install + Starter Kit
• Database schema and models/migrations
• CRUD for Links (with tests)
• Setting up GitHub and automation with GitHub actions
• Configuring Pint
• Configuring Larastan
• Deploying on the Staging server and giving the URL to the client

These are technical details. Clients don't (necessarily) need to know about them, but in my experience, it's still beneficial to show it to the client to impress with the fact that you actually prepared that list.

Further Milestones? DB Schema to the Rescue!

Of course, it's much easier to plan and detalize the first milestone, but the further down the list, the harder it is to predict the "unknowns".

So, you need some level of clarity about the entire project.

A one-page diagram.

And I found over the years that for our back-end developer mind, the best way to describe and grasp the whole project is to have the DB schema.

As a side effect, when you see the tables/columns, you will come up with questions for the client about the unclear parts of the application.

So, this is exactly what I usually do as the project's first task.

You can create a DB schema on paper or in some DB tool, but why not install Laravel right away and create migrations immediately?

So, let's first install Laravel.

laravel new linksletter --breeze --git
cd linksletter

I've chosen two parameters:

• My preferred starter kit is Laravel Breeze: it has all the Auth, with simple design, and without adding any complexity. Perfect for showing "something" to the client.
• I also immediately initialize local Git repository, for convenience. Git commit structure will be very important throughout this course, but we will discuss it in more detail later.

The installation wizard will ask you a few questions, like testing framework or DB driver. Choose the ones you prefer. It doesn't matter that much for this course. For the record, I went for Pest and MySQL.

Next, we will create two Models with their Migrations: links and issues.

php artisan make:model Issue -mf
php artisan make:model Link -mf

The -f flag stands for Factories: we generate them immediately but will fill them later.

So, here are the migrations I came up with:

Issues:

Schema::create('issues', function (Blueprint $table) {
    $table->id();
    $table->string('subject');
    $table->text('header_text')->nullable();
    $table->text('footer_text')->nullable();
    $table->text('links_html')->nullable();
    $table->timestamp('sent_at')->nullable();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->timestamps();
});

Links:

Schema::create('links', function (Blueprint $table) {
    $table->id();
    $table->string('url');
    $table->string('title');
    $table->text('description')->nullable();
    $table->foreignId('issue_id')->nullable()->constrained()->nullOnDelete();
    $table->integer('position')->nullable();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
    $table->timestamps();
});

The thing is that I've spent quite a lot of thinking about each field. For example:

• Should title be a string or a text?
• Should links or issue migration be the first?
• Can description be NULL?
• Should it be nullOnDelete() or cascadeOnDelete() for foreign keys?
• Do we use soft deletes or not?
• etc.

And that's the main point of this exercise:

While working on DB schema, you will come up with a set of questions FOR CLIENT.

After we execute those migrations with php artisan migrate, we already have a real DB and can take any DB tool (I used DBeaver) to export it to a visual schema, like this:

I tend to send this schema to the client. Yes, I know they will not understand the technical DB terms, but they will roughly understand the names of the objects and will appreciate your "full picture" view.

Then, with that DB schema, you add a set of follow-up questions in a "human language":

1. Is the link "description" field required or optional?
2. What about newsletter issue "header text" and "footer text"? Required or optional?
3. Do you need the feature of "archived" issues or links so users can delete and restore them? ("soft deletes")
4. etc.

From here, you have three goals accomplished:

• More technical clarity for yourself
• More project clarity if the client answers those questions
• More client satisfaction as they see quick progress and your dedication to details

Of course, this example with three tables is elementary. But I want you to understand the main idea and later apply it to your projects. In reality, your schema and the list of questions would be much more complex, maybe even discussed over several different meetings, calls, or email chains.

Now, as the DB schema gets "signed off", we can proceed to actually build the features.

At the end of this step, don't forget to commit your changes to the Git repository, which is local for now, but in the next lesson we will push it to the GitHub.

Here's the link to the repository if you want to follow the commits.