Database

The backend uses Cloudflare D1, a serverless SQLite database.

Schema

users

Stores admin users.

Column	Type	Description
id	INTEGER	Primary Key
email	TEXT	Unique email
password_hash	TEXT	Hashed password
name	TEXT	User’s name
role	TEXT	User role (admin, editor)
status	TEXT	Account status (active, disabled)
last_login_at	INTEGER	Last login timestamp
last_login_ip	TEXT	Last login IP address
created_at	INTEGER	Creation timestamp
updated_at	INTEGER	Last update timestamp

sessions

Stores user sessions.

Column	Type	Description
id	INTEGER	Primary Key
user_id	INTEGER	Foreign key to users
token	TEXT	Unique session token
ip_address	TEXT	Client IP address
user_agent	TEXT	Client user agent
expires_at	INTEGER	Token expiration timestamp
created_at	INTEGER	Creation timestamp

blog_posts

Stores blog content.

Column	Type	Description
id	INTEGER	Primary Key
title	TEXT	Post title
slug	TEXT	Unique URL slug
excerpt	TEXT	Post excerpt
content	TEXT	Full post content
featured_image	TEXT	Featured image URL
status	TEXT	Post status (draft, published, archived)
published_at	INTEGER	Publication timestamp
author_id	INTEGER	Foreign key to users
meta_title	TEXT	SEO meta title
meta_description	TEXT	SEO meta description
reading_time_minutes	INTEGER	Estimated reading time
created_at	INTEGER	Creation timestamp
updated_at	INTEGER	Last update timestamp

post_views

Tracks blog post views.

Column	Type	Description
post_id	INTEGER	Foreign key to blog_posts
viewed_at	INTEGER	View timestamp
user_hash	TEXT	Anonymous user identifier

portfolio_projects

Stores portfolio projects.

Column	Type	Description
id	INTEGER	Primary Key
title	TEXT	Project title
slug	TEXT	Unique URL slug
description	TEXT	Project description
content	TEXT	Full project content
featured_image	TEXT	Featured image URL
status	TEXT	Project status (draft, published, archived)
published_at	INTEGER	Publication timestamp
category	TEXT	Project category
client_name	TEXT	Client name
project_url	TEXT	Live project URL
github_url	TEXT	GitHub repository URL
meta_title	TEXT	SEO meta title
meta_description	TEXT	SEO meta description
display_order	INTEGER	Display order
created_at	INTEGER	Creation timestamp
updated_at	INTEGER	Last update timestamp

portfolio_gallery

Stores project gallery images.

Column	Type	Description
id	INTEGER	Primary Key
project_id	INTEGER	Foreign key to portfolio_projects
image_url	TEXT	Image URL
alt_text	TEXT	Alt text for accessibility
sort_order	INTEGER	Display order

resources

Stores useful resources.

Column	Type	Description
id	INTEGER	Primary Key
title	TEXT	Resource title
description	TEXT	Resource description
url	TEXT	Resource URL
type	TEXT	Resource type
category	TEXT	Resource category
thumbnail	TEXT	Thumbnail image URL
is_featured	INTEGER	Whether featured (0/1)
status	TEXT	Status (active, inactive)
created_at	INTEGER	Creation timestamp
updated_at	INTEGER	Last update timestamp

testimonials

Stores client testimonials.

Column	Type	Description
id	INTEGER	Primary Key
author	TEXT	Author name
position	TEXT	Author position
company	TEXT	Author company
quote	TEXT	Testimonial content
rating	INTEGER	Rating (1-5)
image	TEXT	Author image URL
status	TEXT	Status (active, inactive)
display_order	INTEGER	Display order
project_id	INTEGER	Associated project
created_at	INTEGER	Creation timestamp
updated_at	INTEGER	Last update timestamp

resume

Stores resume information.

Column	Type	Description
id	INTEGER	Primary Key
user_id	INTEGER	Foreign key to users
title	TEXT	Resume title
full_name	TEXT	Full name
email	TEXT	Contact email
phone	TEXT	Phone number
location	TEXT	Location
website	TEXT	Personal website
summary	TEXT	Professional summary
pdf_url	TEXT	Resume PDF URL
is_active	INTEGER	Whether active (0/1)
created_at	INTEGER	Creation timestamp
updated_at	INTEGER	Last update timestamp

resume_experience, resume_education, resume_skills, resume_certifications, resume_languages, resume_achievements

Detailed resume sections for experience, education, skills, certifications, languages, and achievements.

tags, blog_tags, portfolio_tags, portfolio_technologies

Tag management system for categorizing content.

contact_submissions

Stores contact form submissions.

newsletter_subscribers

Stores newsletter subscribers.

Management

We use Wrangler to manage the database.

Migrations

Migrations are stored in the migrations folder.

# Create a new migration
pnpm wrangler d1 migrations create mdanamulhasan-db <migration_name>

# Apply migrations locally
pnpm wrangler d1 migrations apply mdanamulhasan-db --local

# Apply migrations to production
pnpm wrangler d1 migrations apply mdanamulhasan-db --remote

Querying

You can run SQL queries directly against the database.

# Local query
pnpm wrangler d1 execute mdanamulhasan-db --local --command "SELECT * FROM users"

# Production query
pnpm wrangler d1 execute mdanamulhasan-db --remote --command "SELECT * FROM users"