Practice Lab Guide

Project 10: End-to-End IoT Project Planning & Deployment Package

Draft a technical spec sheet, generate an itemized Bill of Materials (BOM), model SQL relations, and define STRIDE threat mitigations.

Domain

Project Planning & Architecture

Difficulty

⭐⭐⭐☆☆ (Intermediate)

Course Module

Capstone Preparation

Deliverables

Technical Spec Sheet, BOM Table, database Schemas

1. Capstone Deployment Topology Architecture

Building a multi-tier enterprise IoT solution requires careful planning before writing code. The architecture diagram below illustrates the deployment topology for the final capstone project. Wireless ESP32 nodes acquire local telemetry and connect via Wi-Fi to a local router. An Express.js backend containerizes routing processes, saving incoming payloads to a PostgreSQL/TimescaleDB time-series database. A real-time web dashboard subscribes to data streams to display live updates.

2. Part 1: Step-by-Step Individual Planning Commands & Configs

Follow these detailed steps to build the planning directory structure inside your VM guest, write SQL schemas, and define security constraints.

STEP 1

Launch VM Terminal and Create Capstone Workspace

Boot up your VirtualBox Ubuntu machine. Open the terminal (Ctrl+Alt+T) and create a directory to organize your planning assets.

ubuntu@iot-vm:~$ mkdir -p ~/workspace/capstone_planning && cd ~/workspace/capstone_planning

We run `mkdir -p` and `cd` to generate and enter a dedicated workspace directory named `~/workspace/capstone_planning` to isolate our planning documents.

STEP 2

Open Text Editor to Draft Project Specifications

Launch the graphical text editor in a background thread to write your system specifications.

ubuntu@iot-vm:~/workspace/capstone_planning$ gedit specs.md &

We open `specs.md` in `gedit` using the `&` operator to run the text editor in the background, keeping the terminal prompt active.

STEP 3

Create Database Schema Definition SQL File

Open a new file in gedit to define the relational SQL schema for the capstone project.

ubuntu@iot-vm:~/workspace/capstone_planning$ gedit schema.sql &

We open `schema.sql` to define the database schema, including the tables and fields for storing device telemetry.

STEP 4

Create Security Threat Model Document

Open a new markdown file to document your security plan and STRIDE threat modeling mitigations.

ubuntu@iot-vm:~/workspace/capstone_planning$ gedit security.md &

We open `security.md` to document the security plan, mapping STRIDE threat categories to specific system mitigations.

3. Database Schema Entity Relationship Diagram

The entity-relationship model below maps the database schema, detailing the relationships between the devices lookup table and the telemetry time-series partition table.

4. Part 2: Complete Spec Sheets & Database DDLs

Below is the itemized Bill of Materials for the capstone project, followed by the SQL commands to configure the database schema.

Asset 1: Capstone Bill of Materials (BOM) Table

Component Item	Estimated Unit Cost	Quantity	Estimated Total Cost	Sourcing Notes
ESP32-WROOM-32E NodeMCU	$6.00	2	$12.00	Authorized Espressif distributor
DHT22 sensor module	$4.50	1	$4.50	Includes pull-up resistor
LDR photoresistor	$0.50	2	$1.00	Used with 10k divider resistor
5V Relay driver module	$2.50	1	$2.50	Optocoupler isolated
Active buzzer 5V	$1.20	1	$1.20	Active high trigger
HT7333 low-power regulator	$0.80	2	$1.60	SOT-89 packaging
LiPo battery 1000mAh	$7.50	1	$7.50	Includes JST-PH2.0 plug
Total BOM Cost	—	—	$30.30	Tax and shipping excluded

Asset 2: Time-Series SQL Schema Definition (`schema.sql`)

Line-by-Line Code Breakdown

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";: Installs the UUID library to enable automated generation of unique primary keys.
CREATE TABLE devices ( ... ): Creates the devices lookup table to catalog metadata for active sensor nodes.
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(): Configures the `id` field as a UUID primary key, setting it to auto-generate values on insert.
CREATE TABLE telemetry ( ... ): Creates the telemetry table to store raw sensor readings.
time TIMESTAMPTZ NOT NULL: Configures the `time` field to store timestamps with time zone info. This is required for time-series partitioning in TimescaleDB.
device_id UUID REFERENCES devices(id): Configures `device_id` as a foreign key referencing the devices lookup table.
SELECT create_hypertable('telemetry', 'time');: Invokes the TimescaleDB hypertable function to partition the telemetry table into time-based chunks, maintaining fast queries as the database grows.

-- Capstone Database Schema Definition SQL Script -- Enable UUID generator extensions CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- 1. Devices lookup table CREATE TABLE devices ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name VARCHAR(100) NOT NULL UNIQUE, location VARCHAR(100), status VARCHAR(50) DEFAULT 'inactive', created_at TIMESTAMPTZ DEFAULT NOW() ); -- 2. Telemetry time-series database table CREATE TABLE telemetry ( time TIMESTAMPTZ NOT NULL, device_id UUID NOT NULL REFERENCES devices(id) ON DELETE CASCADE, temperature REAL, light INTEGER ); -- Convert telemetry table to hypertable partitioned by time SELECT create_hypertable('telemetry', 'time'); -- Create indexes to optimize time-series queries CREATE INDEX idx_telemetry_device_time ON telemetry(device_id, time DESC);

5. Deliverables Summary

Created Planning Assets

Capstone system architecture specification: specs.md.
TimescaleDB database DDL script: schema.sql.
STRIDE security threat model document: security.md.

Verification Checklist

Itemized BOM pricing matches the estimated $30.30 target.
TimescaleDB configuration verified using hypertable indexing templates.
STRIDE threat matrix completed, detailing security mitigations for all attack vectors.

6. Closing Explanation: Why We Did This & What It Accomplishes

Architectural Intent & Operational Impact

Why We Did This

Drafting a spec sheet and BOM defines clear hardware and budget boundaries before starting development on the capstone project.
Partitioning the telemetry table into time-series hypertables in TimescaleDB optimizes query performance, keeping indexes small enough to fit in RAM as data grows.
Conducting a STRIDE threat modeling assessment identifies security risks early, allowing us to build in mitigations like authenticated APIs and TLS encryption from the start.

What This Accomplishes

Establishes the technical blueprints and data models for your capstone project, providing a roadmap for containerized backend and web dashboard development.