Build a Constituent Issue Reporter

Build a full-stack web app with Node.js, Express, and SQLite.

Introduction

30 Second Summary

Every community has local issues that need attention. Potholes, broken streetlights, overgrown parks. Residents want a simple way to report them, and local representatives need a way to track and respond. Without a system in place, reports get lost in email inboxes or forgotten phone calls.

In this project, you will build a full web application from scratch where community members submit issues through a form and administrators view and respond to them through a dashboard. Everything runs locally on your machine, powered by Node.js, Express, and SQLite.

What You'll Build

You will open your browser, fill out a quick form to report a pothole on your street, and get a reference number back instantly. Then you will switch to the admin dashboard, see your report listed, type a response, and watch the status flip from open to resolved.

By the end of this project, you'll have:

  • A public submission page where you report a community issue and get a reference number back instantly, proving your server can accept and process data.
  • An admin dashboard where you view every reported issue, type a response, and watch the card flip from orange (open) to green (resolved).
  • A SQLite database that permanently stores all issues on disk. Restart the server as many times as you want. Nothing is lost.
  • Secret Mission: a public status lookup page where constituents enter their reference number and check whether their issue has been addressed.

Are there any prerequisites?

None. This guide walks you through installing everything you need, including Node.js and all project dependencies. You do not need prior experience with servers or databases.

Before We Start

Before diving in, take a moment to commit to what you are building and why it matters.

Set Up the Project

Every web application starts with a project folder and its dependencies. Before you can write any server code, you need a structured directory and the right packages installed.

In this step, you will create the project folder, initialize it with npm, and install two packages: Express (the web framework) and better-sqlite3 (the database library).

In this step, get ready to:
  • Create the project directory with npm initialization.
  • Install Express and better-sqlite3.
  • Verify both packages load correctly.
Create the project directory

Your project needs its own folder on your Desktop so you can find it easily.

  • Open a terminal in VS Code by pressing Ctrl+` (backtick key, usually above Tab).
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir community-reporter && cd community-reporter
  • Initialize the project with a package.json file by running:
npm init -y

You should see a JSON output printed in your terminal showing the generated package.json contents.

What is package.json?

Every Node.js project has a package.json file that stores the project's name, version, and list of dependencies.

The -y flag tells npm to accept all defaults instead of asking you questions one by one.

  • Create a public folder for your HTML files by running:
mkdir public
  • Confirm the folder was created by running:
ls

You should see package.json and public listed in the output.

Don't see the public folder?

Make sure you ran mkdir public from inside the community-reporter directory. Your terminal prompt should show community-reporter as the current folder.

Help me figure out why my folder wasn't created.

Install dependencies

Your project needs two packages: Express to run the web server, and better-sqlite3 to store data in a database file.

  • Install both packages by running:
npm install express@5.2.1 better-sqlite3@13.0.3

You should see install progress output followed by a summary showing the number of packages added.

What are these packages?

Express is a web framework for Node.js. It handles incoming requests from browsers and sends back responses like HTML pages or JSON data.

better-sqlite3 is a database library that stores data in a single file on your computer. It uses SQLite, which means zero configuration and no separate database server to install.

Seeing install errors?

If you see permission errors, make sure you are running the terminal from inside your community-reporter folder (not the system root).

If better-sqlite3 fails to build, confirm your Node.js version is v22 or higher by running node --version.

Help me fix my npm install error.

  • Now update package.json to describe your project properly. Open it in VS Code by clicking on package.json in the Explorer sidebar on the left.
  • Replace the entire contents of the file with this:
{
  "name": "community-reporter",
  "version": "1.0.0",
  "description": "Constituent issue reporting portal",
  "main": "server.js",
  "scripts": {
    "start": "node server.js"
  },
  "dependencies": {
    "express": "5.2.1",
    "better-sqlite3": "13.0.3"
  }
}

What did we change?

  • "main": "server.js" tells Node.js which file starts your app.
  • "start": "node server.js" lets you run npm start to launch the server later.
  • The dependencies section records the exact package versions your project uses.
  • Save the file with Ctrl+S.
Verify the installation

Before moving on, you need to confirm that both packages actually load without errors.

Before you run this next command, what do you think will happen if one of the packages failed to install?

  • Verify both packages load by running this command in your terminal:
node -e "const e = require('express'); console.log('Express OK'); const D = require('better-sqlite3'); console.log('SQLite OK');"

You should see both Express OK and SQLite OK printed with no errors.

What does this command do?

The node -e flag runs a snippet of JavaScript directly from the terminal without needing a file.

The snippet tries to load each package with require(). If a package is missing or broken, Node.js throws an error instead of printing the OK message.

Seeing 'Cannot find module' errors?

This means the package did not install correctly. Run npm install express@5.2.1 better-sqlite3@13.0.3 again from inside the community-reporter folder.

Make sure a node_modules folder exists in your project. If it does not, the install did not complete successfully.

Help me fix the 'Cannot find module' error.

✔️ Awesome, I've got everything!

Great. Double check you have saved package.json and that both OK messages printed in the terminal.

ⓧ I'd like to double check the full code

Here is what your package.json should look like:

{
  "name": "community-reporter",
  "version": "1.0.0",
  "description": "Constituent issue reporting portal",
  "main": "server.js",
  "scripts": {
    "start": "node server.js"
  },
  "dependencies": {
    "express": "5.2.1",
    "better-sqlite3": "13.0.3"
  }
}

Your project folder should also contain a public folder and a node_modules folder.

Your project is ready to go. Next up, you will build the Express server and create the public submission form that residents will use to report issues.

Build the Server and Public Form

Your project folder has its packages installed. Now you need something a user can actually see.

In this step, you will create an Express server that delivers web pages to the browser. You will also create the HTML form and CSS stylesheet that make up the public-facing issue submission page.

In this step, get ready to:
  • Create an Express server that serves static files from a public folder.
  • Build a CSS stylesheet with layout and form styles.
  • Build an HTML page with a submission form for community issues.
Create the Express server

Express is the framework that listens for browser requests and responds with your web pages. This server file is the entry point for your entire application.

  • Create a new file called server.js in the root of your community-reporter folder and add the following code:
const express = require('express');
const path = require('path');

const app = express();
const port = 3000;

// Serve HTML, CSS, and JS files from the public folder
app.use(express.static(path.join(__dirname, 'public')));

app.listen(port, () => {
  console.log(`Community Reporter running at http://localhost:${port}`);
});

What does this code do?

  • express() creates your application instance. Every route and middleware attaches to this object.
  • express.static() tells Express to serve any file inside the public folder directly to the browser. HTML, CSS, images, and JavaScript files all get delivered automatically.
  • app.listen() starts the server on port 3000 and prints a confirmation message to your terminal.
  • Save server.js.
  • Start the server by running this command in your terminal:
node server.js

You should see this message printed in your terminal:

Community Reporter running at http://localhost:3000
  • Open your browser and visit http://localhost:3000.

You will see the text "Cannot GET /" in the browser. That is expected. The server is running, but there are no files in the public folder yet.

  • Stop the server by pressing Ctrl+C in your terminal. You will restart it after creating the HTML and CSS files.

Don't see the message in your terminal?

  • Make sure your terminal is inside the community-reporter folder. Run ls and confirm you see server.js and node_modules listed.
  • If you see an error about "Cannot find module 'express'", the packages did not install correctly. Run npm install again and retry.

help me debug why my Express server won't start

Create the stylesheet

Before adding the HTML page, you will create all the CSS styles your application needs. This includes styles for the form you are building now and styles for features in later steps.

  • Create a new file called public/style.css and add the base layout styles:
* {
  margin: 0;
  padding: 0;
  box-sizing: border-box;
}

body {
  font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
  background-color: #f0f2f5;
  color: #1a1a2e;
  line-height: 1.6;
}

.container {
  max-width: 800px;
  margin: 0 auto;
  padding: 2rem 1rem;
}

header {
  text-align: center;
  margin-bottom: 2rem;
}

What does this code do?

The * selector resets all default browser margins and padding so every element starts from a clean slate. The .container class centers content at a max width of 800px, giving the page a readable column layout.

  • Continue adding the heading and form element styles below the existing code:
header h1 {
  color: #16213e;
  margin-bottom: 0.5rem;
}

header p {
  color: #555;
}

.form-group {
  margin-bottom: 1.2rem;
}

label {
  display: block;
  font-weight: 600;
  margin-bottom: 0.4rem;
}

input, textarea {
  width: 100%;
  padding: 0.8rem;
  border: 1px solid #ddd;
  border-radius: 6px;
  font-size: 1rem;
  font-family: inherit;
}

What does this code do?

These rules style the form fields. Each .form-group adds spacing between fields. Inputs and textareas stretch to full width with rounded corners and consistent padding.

  • Continue adding focus states, button styles, and utility classes:
input:focus, textarea:focus {
  outline: none;
  border-color: #0f3460;
  box-shadow: 0 0 0 2px rgba(15, 52, 96, 0.1);
}

button {
  background-color: #0f3460;
  color: white;
  border: none;
  padding: 0.8rem 1.5rem;
  font-size: 1rem;
  border-radius: 6px;
  cursor: pointer;
  width: 100%;
}

button:hover {
  background-color: #16213e;
}

.hidden {
  display: none;
}

#success-message {
  text-align: center;
  padding: 2rem;
  background: #e8f5e9;
  border-radius: 8px;
}

What does this code do?

Focus states add a subtle blue glow when users click into a field. The button uses a dark navy background that shifts slightly on hover. The .hidden class hides elements until JavaScript reveals them later.

  • Add the success message and issue card styles. These are used by features in later steps:
#success-message h2 {
  color: #2e7d32;
  margin-bottom: 0.5rem;
}

#success-message button {
  margin-top: 1rem;
  width: auto;
}

.issue-card {
  background: white;
  border-radius: 8px;
  padding: 1.5rem;
  margin-bottom: 1rem;
  box-shadow: 0 2px 4px rgba(0, 0, 0, 0.05);
  border-left: 4px solid #ff9800;
}

.issue-card.resolved {
  border-left-color: #4caf50;
}

.issue-header {
  display: flex;
  justify-content: space-between;
  margin-bottom: 0.5rem;
}

What does this code do?

The .issue-card styles create white cards with an orange left border for open issues. Resolved cards switch to a green border. These cards appear on the admin dashboard you will build in Step 5.

  • Add the issue status badge and metadata styles:
.issue-id {
  font-weight: 600;
  color: #555;
}

.issue-status {
  text-transform: uppercase;
  font-size: 0.8rem;
  font-weight: 600;
  padding: 0.2rem 0.6rem;
  border-radius: 4px;
  background: #fff3e0;
  color: #e65100;
}

.issue-card.resolved .issue-status {
  background: #e8f5e9;
  color: #2e7d32;
}

.issue-description {
  margin: 0.5rem 0;
}

.issue-meta {
  font-size: 0.85rem;
  color: #777;
  margin-bottom: 1rem;
}

What does this code do?

Status badges get an orange background for open issues and switch to green when resolved. The metadata text (reporter name and date) is styled smaller and lighter to stay secondary to the issue content.

  • Add the final styles for response display and response forms:
.issue-response {
  background: #e3f2fd;
  padding: 1rem;
  border-radius: 6px;
  margin-top: 0.5rem;
}

.respond-form textarea {
  margin-bottom: 0.5rem;
}

.respond-form button {
  width: auto;
}

What does this code do?

Response boxes get a light blue background to distinguish them from the issue description. The respond form button shrinks to auto-width instead of stretching full-width like the main submit button.

  • Save public/style.css.
  • Confirm the file exists by running:
ls public/

You should see style.css listed in the output.

Don't see style.css listed?

  • Make sure you created the file inside the public folder, not in the project root. The path should be public/style.css.
  • Check that you saved the file in your editor before running ls.

help me find where my style.css file ended up

Create the HTML submission form

This is the page community members will use to report local issues. It needs a form with fields for their name, the issue location, and a description of the problem.

  • Create a new file called public/index.html and add the page structure with the form fields:
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Report an Issue - Community Reporter</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <div class="container">
    <header>
      <h1>Community Issue Reporter</h1>
      <p>Help us improve our community. Report issues you see in your local area.</p>
    </header>

    <form id="issue-form">
      <div class="form-group">
        <label for="name">Your Name</label>
        <input type="text" id="name" name="name" required placeholder="e.g. Jane Smith">
      </div>

      <div class="form-group">
        <label for="location">Location</label>
        <input type="text" id="location" name="location" required placeholder="e.g. Corner of Main St and Oak Ave">
      </div>

      <div class="form-group">
        <label for="description">Describe the Issue</label>
        <textarea id="description" name="description" required rows="5" placeholder="e.g. There is a large pothole that has been growing for weeks..."></textarea>
      </div>

What does this code do?

  • The <link rel="stylesheet" href="style.css"> tag connects your CSS file so styles apply automatically.
  • Each form field sits inside a .form-group div with a label and input. The required attribute prevents submission if a field is empty.
  • Placeholder text gives users example values so they know what to type.
  • Continue adding the submit button and a hidden success message div below the form fields:
      <button type="submit">Submit Issue</button>
    </form>

    <div id="success-message" class="hidden">
      <h2>Thank you!</h2>
      <p>Your issue has been submitted. Reference number: <span id="ref-number"></span></p>
      <button onclick="document.getElementById('success-message').classList.add('hidden'); document.getElementById('issue-form').classList.remove('hidden');">Submit Another Issue</button>
    </div>
  </div>
</body>
</html>

What does this code do?

The success-message div starts hidden (using the .hidden CSS class). In a later step, JavaScript will show this div after a successful submission and display the issue's reference number. The "Submit Another Issue" button resets the view so the user can file another report.

  • Save public/index.html.

Before you start the server, what do you expect to see in the browser at http://localhost:3000?

  • Start the server by running:
node server.js
  • Open your browser and visit http://localhost:3000.

You should see a styled form with "Community Issue Reporter" as the heading, three input fields (Your Name, Location, Describe the Issue), and a dark navy Submit Issue button.

Still seeing Cannot GET /?

  • Confirm your file is named exactly index.html (not Index.html or index.htm). Express looks for index.html by default.
  • Make sure the file is inside the public folder. Run ls public/ and check that both index.html and style.css appear.
  • If you changed the files while the server was running, stop it with Ctrl+C and restart with node server.js.

help me figure out why my HTML page isn't loading in the browser

✔️ Awesome, I've got everything!

Great. Double check that you have saved both public/style.css and public/index.html.

ⓧ I'd like to double check the full code

Here is the full content of each file. Compare yours against these.

server.js:

const express = require('express');
const path = require('path');

const app = express();
const port = 3000;

app.use(express.static(path.join(__dirname, 'public')));

app.listen(port, () => {
  console.log(`Community Reporter running at http://localhost:${port}`);
});

public/style.css (scroll up through your file and confirm each section is present, in order):

  • Global reset (*), body, .container, header styles.
  • Form styles: .form-group, label, input, textarea, focus states.
  • Button styles, .hidden, #success-message styles.
  • Issue card styles: .issue-card, .issue-header, .issue-status, .issue-meta, .issue-response, .respond-form.

public/index.html:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Report an Issue - Community Reporter</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <div class="container">
    <header>
      <h1>Community Issue Reporter</h1>
      <p>Help us improve our community. Report issues you see in your local area.</p>
    </header>

    <form id="issue-form">
      <div class="form-group">
        <label for="name">Your Name</label>
        <input type="text" id="name" name="name" required placeholder="e.g. Jane Smith">
      </div>

      <div class="form-group">
        <label for="location">Location</label>
        <input type="text" id="location" name="location" required placeholder="e.g. Corner of Main St and Oak Ave">
      </div>

      <div class="form-group">
        <label for="description">Describe the Issue</label>
        <textarea id="description" name="description" required rows="5" placeholder="e.g. There is a large pothole that has been growing for weeks..."></textarea>
      </div>
      <button type="submit">Submit Issue</button>
    </form>

    <div id="success-message" class="hidden">
      <h2>Thank you!</h2>
      <p>Your issue has been submitted. Reference number: <span id="ref-number"></span></p>
      <button onclick="document.getElementById('success-message').classList.add('hidden'); document.getElementById('issue-form').classList.remove('hidden');">Submit Another Issue</button>
    </div>
  </div>
</body>
</html>

Your server is live and the submission form is visible in the browser. Next up, you will wire the form to actually send data to the server so reported issues do not just disappear when the page refreshes.

Accept Issue Submissions

Your form looks polished, but clicking Submit Issue does absolutely nothing right now.

The server only knows how to serve files. It has no idea what to do when data arrives from the form. In this step, you will teach it to accept submissions and give each one a reference number.

In this step, get ready to:
  • Add an API endpoint that accepts issue submissions.
  • Wire the form to send data to the server and show a success message.
  • Discover why in-memory storage is not enough for real data.
Add a POST route to accept submissions

Right now, server.js only serves static files. You need to add middleware that lets it understand incoming JSON data, and a route that processes it.

  • Switch back to the terminal where your server is running.
  • Press Ctrl+C to stop the server.
  • Open server.js in VS Code and replace its entire contents with this updated version:
const express = require('express');
const path = require('path');

const app = express();
const port = 3000;
// Temporary in-memory storage
const issues = [];
let nextId = 1;

// Parse incoming JSON request bodies
app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

// Accept a new issue submission
app.post('/api/issues', (req, res) => {
  const { name, location, description } = req.body;
  if (!name || !location || !description) {
    return res.status(400).json({ error: 'All fields are required' });
  }
  const issue = { id: nextId++, name, location, description };
  issues.push(issue);
  res.json({ success: true, id: issue.id });
});

app.listen(port, () => {
  console.log(`Community Reporter running at http://localhost:${port}`);
});

What does this code do?

  • const issues = [] creates an empty array to hold submitted issues in memory.
  • app.use(express.json()) tells Express to automatically parse JSON in incoming request bodies.
  • app.post('/api/issues', ...) defines a route that responds only to POST requests at that path.
  • The route checks that all three fields are present, assigns an auto-incrementing ID, pushes the issue into the array, and returns { success: true, id } as JSON.
  • Save server.js.
  • Restart the server by running:
node server.js

You should see Community Reporter running at http://localhost:3000 printed with no errors.

Seeing a syntax error?

  • Check that every opening brace { has a matching closing brace }. The route handler function and the if block each need their own pair.
  • Make sure app.use(express.json()) appears before the route definition. Order matters.
  • help me fix a syntax error in my Express server.js
Wire the form to submit data via JavaScript

The server can now accept submissions, but the form still tries to do a traditional page reload when submitted. You need client-side JavaScript to intercept the form, send the data as JSON, and show a success message.

  • Open public/index.html in VS Code.
  • Find the closing </div> tag just before </body>. Add this script block between them:
  <script>
    document.getElementById('issue-form').addEventListener('submit', async (e) => {
      e.preventDefault();

      const form = e.target;
      const data = {
        name: form.name.value,
        location: form.location.value,
        description: form.description.value
      };

      const response = await fetch('/api/issues', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(data)
      });

      const result = await response.json();

      if (result.success) {
        document.getElementById('ref-number').textContent = '#' + result.id;
        document.getElementById('issue-form').classList.add('hidden');
        document.getElementById('success-message').classList.remove('hidden');
        form.reset();
      }
    });
  </script>

What does this script do?

  • e.preventDefault() stops the browser from reloading the page when the form is submitted.
  • fetch('/api/issues', ...) sends the form data as a JSON POST request to your server route.
  • On success, the script hides the form and reveals the hidden #success-message div with the reference number filled in.
  • Save public/index.html.
  • Refresh http://localhost:3000 in your browser.
  • Fill in the form with any test data. For example: name Jane Smith, location Main St and Oak Ave, description Large pothole near the intersection.
  • Click Submit Issue.

You should see a green success panel appear with the text "Reference number: #1".

Form submits but nothing happens?

  • Open your browser's DevTools console (press F12) and check for errors. A 404 means the server does not have the POST route registered. Make sure you saved server.js and restarted the server.
  • Check that the <script> tag is placed after the closing </div> of .container and before </body>.
  • help me debug my form submission
Discover the data loss problem

Submissions are working. But there is a critical flaw hiding in this approach. The issues array lives in your server's process memory.

Before you test what happens next, predict: if you stop the server and start it again, will your submitted issue still be there?

  • Switch to the terminal running your server.
  • Press Ctrl+C to stop the server.
  • Restart it by running:
node server.js
  • Go back to your browser and refresh http://localhost:3000.
  • Submit a new issue with any data and click Submit Issue.

Notice the reference number is back to #1. Your previous submission is gone. When the server process stopped, everything stored in the array vanished with it.

Why did the data disappear?

JavaScript variables only exist while the program is running. When you press Ctrl+C, Node.js shuts down and all memory is released. The next time you run node server.js, it starts fresh with an empty issues array.

This is exactly the problem a database solves. It stores data on disk so it survives restarts.

✔️ Awesome, I've got everything!

Great. Make sure both files are saved before moving on.

ⓧ I'd like to double check the full code

Here is server.js as it should look now:

const express = require('express');
const path = require('path');

const app = express();
const port = 3000;
const issues = [];
let nextId = 1;

app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

app.post('/api/issues', (req, res) => {
  const { name, location, description } = req.body;
  if (!name || !location || !description) {
    return res.status(400).json({ error: 'All fields are required' });
  }
  const issue = { id: nextId++, name, location, description };
  issues.push(issue);
  res.json({ success: true, id: issue.id });
});

app.listen(port, () => {
  console.log(`Community Reporter running at http://localhost:${port}`);
});

Here is public/index.html as it should look now:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Report an Issue - Community Reporter</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <div class="container">
    <header>
      <h1>Community Issue Reporter</h1>
      <p>Help us improve our community. Report issues you see in your local area.</p>
    </header>

Scroll up and confirm your file has:

  • The <form id="issue-form"> block with all three input fields.
  • The <div id="success-message" class="hidden"> block.
  • The <script> block with the fetch call placed after the closing </div> of .container and before </body>.

You have a working form that submits data and shows a confirmation. But every restart wipes the slate clean. In the next step, you will add a SQLite database that writes submissions to disk so they survive forever.

Add Database Persistence

You proved in the last step that your form collects data and returns a reference number. But you also discovered a serious flaw.

Every time you stop and restart the server, all submitted issues vanish because they only live in a JavaScript array in memory. A SQLite database stores data permanently on disk, so nothing is lost between restarts.

In this step, get ready to:
  • Create a database module that sets up persistent storage.
  • Update the server to write submissions into the database instead of an array.
  • Prove that data survives a server restart.
Create the database module

Your application needs a dedicated file that opens the database connection, creates the table structure, and exports the connection for other files to use.

  • Create a new file called database.js in your community-reporter folder (the same folder where server.js lives).
  • Paste the following code into database.js:
const Database = require('better-sqlite3');
const path = require('path');

// Open (or create) the database file
const db = new Database(path.join(__dirname, 'issues.db'));

// Enable WAL mode for better performance
db.pragma('journal_mode = WAL');

// Create the issues table if it doesn't exist yet
db.exec(`
  CREATE TABLE IF NOT EXISTS issues (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT NOT NULL,
    description TEXT NOT NULL,
    status TEXT DEFAULT 'open',
    response TEXT DEFAULT '',
    created_at TEXT DEFAULT (datetime('now', 'localtime'))
  )
`);

module.exports = db;

What does this code do?

  • new Database(path.join(__dirname, 'issues.db')) opens a file called issues.db in your project folder. If the file does not exist yet, better-sqlite3 creates it automatically.
  • db.pragma('journal_mode = WAL') enables Write-Ahead Logging, which improves read and write performance.
  • db.exec(...) runs the SQL that creates the issues table with columns for every piece of data you need to track: the reporter's name, location, description, status, response text, and a timestamp.
  • module.exports = db makes the database connection available to any file that uses require('./database').
  • Save database.js.
  • Verify the module works by running this command in your terminal:
node -e "require('./database'); console.log('Database ready')"

You should see Database ready printed with no errors. The command also creates the issues.db file in your project folder.

  • Confirm the file was created by running:
ls

You should see issues.db listed alongside server.js, database.js, and package.json.

Seeing an error about better-sqlite3?

If you see a "Cannot find module" error, make sure you ran npm install in Step 1 inside the community-reporter folder. Your terminal must also be in that folder when you run the command.

help me fix this better-sqlite3 module not found error

Update server.js to use the database

Now that the database module exists, the server needs to import it and use prepared statements instead of the in-memory array.

  • Open server.js in VS Code.
  • Replace the entire contents of server.js with this updated version:
const express = require('express');
const path = require('path');
const db = require('./database');

const app = express();
const port = 3000;

app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

// Submit a new issue
app.post('/api/issues', (req, res) => {
  const { name, location, description } = req.body;

  if (!name || !location || !description) {
    return res.status(400).json({ error: 'All fields are required' });
  }

  const stmt = db.prepare(
    'INSERT INTO issues (name, location, description) VALUES (?, ?, ?)'
  );
  const result = stmt.run(name, location, description);

  res.json({ success: true, id: Number(result.lastInsertRowid) });
});

app.listen(port, () => {
  console.log(`Community Reporter running at http://localhost:${port}`);
});

What changed?

  • const db = require('./database') imports your new database module. This replaces the old issues array and nextId counter.
  • db.prepare(...) creates a prepared statement. The ? placeholders safely insert user values without risk of SQL injection.
  • stmt.run(name, location, description) executes the INSERT and returns an object containing lastInsertRowid, which is the auto-generated ID for the new row.
  • Number(result.lastInsertRowid) converts the ID from a BigInt to a regular number so it works in JSON responses.
  • Save server.js.
  • Start the server by running:
node server.js

You should see Community Reporter running at http://localhost:3000 printed in the terminal.

  • Open http://localhost:3000 in your browser and submit a test issue (any name, location, and description).
  • Confirm you see the success message with a reference number.

Server won't start?

If you see "address already in use", the old server process from the previous step is still running. Press Ctrl+C in the terminal to stop it, then run node server.js again.

help me fix my server startup error

Verify persistence across restarts

The whole point of adding a database is that data survives when the server stops. Time to prove it.

  • Stop the server by pressing Ctrl+C in your terminal.
  • Start it again by running:
node server.js

Before you run the next command, think about what you expect to see. Do you think your submitted issue is still in the database, or has it disappeared like before?

  • Stop the server again with Ctrl+C.
  • Query the database directly by running:
node -e "const db = require('./database'); console.log(db.prepare('SELECT * FROM issues').all())"

You should see your submitted issue printed as a JavaScript object in the terminal, complete with its id, name, location, description, status, and timestamp. The data survived the restart.

Why does this work now?

The old array lived in your server's process memory. When the process stopped, the memory was freed and the data vanished.

SQLite writes to a real file on disk (issues.db). Stopping the server has no effect on that file. When the server starts again, it opens the same file and reads everything back.

Seeing an empty array?

If the output is [], you may not have submitted an issue after updating server.js. Issues submitted with the old array-based code were never written to the database. Start the server, submit a new issue from the browser, then stop and re-run the query.

help me figure out why my database query returns empty

✔️ Awesome, I've got everything!

Great. Double check you have saved both database.js and server.js.

ⓧ I'd like to double check the full code

Here is database.js in full:

const Database = require('better-sqlite3');
const path = require('path');

const db = new Database(path.join(__dirname, 'issues.db'));

db.pragma('journal_mode = WAL');

db.exec(`
  CREATE TABLE IF NOT EXISTS issues (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    location TEXT NOT NULL,
    description TEXT NOT NULL,
    status TEXT DEFAULT 'open',
    response TEXT DEFAULT '',
    created_at TEXT DEFAULT (datetime('now', 'localtime'))
  )
`);

module.exports = db;

Here is server.js in full:

const express = require('express');
const path = require('path');
const db = require('./database');

const app = express();
const port = 3000;

app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

// Submit a new issue
app.post('/api/issues', (req, res) => {
  const { name, location, description } = req.body;

  if (!name || !location || !description) {
    return res.status(400).json({ error: 'All fields are required' });
  }

  const stmt = db.prepare(
    'INSERT INTO issues (name, location, description) VALUES (?, ?, ?)'
  );
  const result = stmt.run(name, location, description);

  res.json({ success: true, id: Number(result.lastInsertRowid) });
});

app.listen(port, () => {
  console.log(`Community Reporter running at http://localhost:${port}`);
});

Your data is now permanent. Next up, you will build an admin dashboard so you can actually view and respond to all those submitted issues.

Build the Admin Dashboard

Your database is safely collecting community issues. But right now, the only way to see what has been submitted is to query the database directly from the terminal.

In this step, you will build an admin dashboard that displays all reported issues and lets you type a response to each one. This closes the loop between the community member reporting a problem and the politician addressing it.

In this step, get ready to:
  • Add API routes for retrieving issues and saving responses.
  • Create an admin dashboard page that displays all submitted issues.
  • Respond to an issue and watch its status change from open to resolved.
Add the admin API routes

The admin dashboard needs two things from the server: a way to fetch all issues, and a way to save a response to a specific issue. Each of these is a separate API route.

  • In server.js, find the closing }); of your app.post('/api/issues', ...) route.
  • Add both admin routes below it by copying the following code:
// Get all issues (admin)
app.get('/api/issues', (req, res) => {
  const issues = db.prepare('SELECT * FROM issues ORDER BY created_at DESC').all();
  res.json(issues);
});

// Respond to an issue (admin)
app.post('/api/issues/:id/respond', (req, res) => {
  const { response } = req.body;
  const { id } = req.params;

  if (!response) {
    return res.status(400).json({ error: 'Response is required' });
  }

  const stmt = db.prepare(
    'UPDATE issues SET response = ?, status = ? WHERE id = ?'
  );
  const result = stmt.run(response, 'resolved', Number(id));

  if (result.changes === 0) {
    return res.status(404).json({ error: 'Issue not found' });
  }

  res.json({ success: true });
});

What do these routes do?

  • GET /api/issues queries all rows from the issues table, ordered newest first, and returns them as a JSON array.
  • POST /api/issues/:id/respond takes a response from the request body and updates the matching issue, setting its status to resolved.
  • result.changes === 0 checks whether the UPDATE actually affected a row. If no row matched the ID, the issue does not exist.
  • Save server.js.
  • Stop your running server with Ctrl+C, then restart it by running:
node server.js
  • Open http://localhost:3000/api/issues in your browser. You should see a JSON array containing the issues you submitted in the previous step.

Seeing an empty array or an error?

  • An empty array [] means no issues exist in the database. Go to http://localhost:3000 and submit one first, then refresh the /api/issues page.
  • If you see a "Cannot GET" error, check that you placed the new routes AFTER the existing app.post('/api/issues', ...) route and BEFORE the app.listen call.
  • help me debug my GET /api/issues route

✔️ Awesome, I've got everything!

Great. Make sure you have saved server.js and your server is running.

ⓧ I'd like to double check the full code

Your server.js should have the following structure, in order:

  • The require imports for express, path, and db.
  • The app and port declarations.
  • The express.json() and express.static middleware.
  • The POST /api/issues route (from the previous step).
  • The GET /api/issues route you just added.
  • The POST /api/issues/:id/respond route you just added.
  • The app.listen call at the bottom.
Create the admin dashboard page

The API routes are ready. Now you need a page that calls them and displays the results as a visual dashboard.

  • Create a new file called admin.html inside your public folder with the following HTML structure:
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Admin Dashboard - Community Reporter</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <div class="container">
    <header>
      <h1>Admin Dashboard</h1>
      <p>View and respond to community issues.</p>
    </header>

    <div id="issues-list">
      <p>Loading issues...</p>
    </div>
  </div>

  <script>
  </script>
</body>
</html>

What does this page do so far?

This is the skeleton of the admin dashboard. The issues-list div shows a "Loading issues..." placeholder. The empty <script> tag is where you will add the JavaScript that fetches and displays actual issues.

  • Save public/admin.html.
  • Open http://localhost:3000/admin.html in your browser. You should see the heading "Admin Dashboard" and the text "Loading issues..." below it.

Page not loading?

  • Make sure the file is saved inside the public folder, not in the project root. Express only serves files from public/.
  • Check that your server is still running in the terminal. If it stopped, run node server.js again.
  • help me figure out why my admin.html page is not loading

Now add the JavaScript that fetches issues from your API and renders them as cards. Inside the empty <script> tag in admin.html, add the loadIssues function:

    async function loadIssues() {
      const response = await fetch('/api/issues');
      const issues = await response.json();
      const container = document.getElementById('issues-list');

      if (issues.length === 0) {
        container.innerHTML = '<p>No issues reported yet.</p>';
        return;
      }

      container.innerHTML = issues.map(issue => `
        <div class="issue-card ${issue.status}">
          <div class="issue-header">
            <span class="issue-id">#${issue.id}</span>
            <span class="issue-status">${issue.status}</span>
          </div>
          <h3>${issue.location}</h3>
          <p class="issue-description">${issue.description}</p>
          <p class="issue-meta">Reported by ${issue.name} on ${issue.created_at}</p>
          ${issue.response ? `<div class="issue-response"><strong>Response:</strong> ${issue.response}</div>` : `
            <form class="respond-form" onsubmit="respondToIssue(event, ${issue.id})">
              <textarea name="response" required placeholder="Type your response..."></textarea>
              <button type="submit">Send Response</button>
            </form>
          `}
        </div>
      `).join('');
    }

What does loadIssues do?

  • It calls fetch('/api/issues') to get all issues from your GET route as a JSON array.
  • If the array is empty, it shows a "No issues reported yet" message.
  • For each issue, it builds an HTML card using a template literal. Open issues get a respond form. Resolved issues display the response text instead.
  • Below the loadIssues function (still inside the <script> tag), add the respond function and the initial call to load issues:
    async function respondToIssue(event, id) {
      event.preventDefault();
      const form = event.target;
      const responseText = form.response.value;

      const response = await fetch(`/api/issues/${id}/respond`, {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ response: responseText })
      });

      const result = await response.json();

      if (result.success) {
        loadIssues();
      }
    }

    loadIssues();

What does respondToIssue do?

  • It prevents the form from refreshing the page, grabs the response text, and sends it to your POST /api/issues/:id/respond route.
  • If the server confirms success, it calls loadIssues() again to refresh the card list. The responded issue now shows as resolved instead of displaying the form.
  • The final loadIssues(); call at the bottom runs immediately when the page loads, so the cards appear without the user clicking anything.
  • Save public/admin.html.
  • Refresh http://localhost:3000/admin.html in your browser. You should see your previously submitted issues displayed as cards, each with an orange left border and an "open" status badge.

Still seeing "Loading issues..." with no cards?

  • Open your browser's developer console (press F12 then click the Console tab). Look for error messages there.
  • Make sure the loadIssues(); call appears after the function definitions and before the closing </script> tag.
  • If you see no issues at all, go to http://localhost:3000 and submit a couple of test issues first.
  • help me debug why my admin dashboard is not displaying issues

✔️ Awesome, I've got everything!

Great. Make sure public/admin.html is saved and your cards are showing in the browser.

ⓧ I'd like to double check the full code

Your public/admin.html should have these sections in order:

  • The HTML boilerplate with a <link> to style.css.
  • A .container div with a header and an issues-list div.
  • A <script> tag containing the loadIssues function, then the respondToIssue function, then a loadIssues(); call.
Test the full submission-to-response loop

The dashboard is wired up. Time to prove the full loop works, from submitting an issue to responding to it.

  • Open http://localhost:3000 and submit 2-3 test issues (use different locations and descriptions so they are easy to tell apart).
  • Open http://localhost:3000/admin.html in a new tab. You should see all your submitted issues listed as cards.

Before you respond to an issue, what do you think will happen to the card when you click Send Response?

  • Pick one of the open issues. Type a response in the textarea (for example: "Repair crew dispatched, expected fix by Friday.").
  • Click Send Response.

The card reloads instantly. Its left border changes from orange to green, the status badge changes from "open" to "resolved", and your response text appears in a blue highlight box. The respond form disappears because the issue no longer needs one.

Clicked Send Response but nothing changed?

  • Open the browser console (F12 then Console tab). Look for a network error or JavaScript exception.
  • Check that your respondToIssue function uses backticks (template literal) around the URL, not regular quotes. The ${id} inside needs backticks to interpolate.
  • Make sure the POST respond route in server.js is saved and the server was restarted after adding it.
  • help me figure out why responding to an issue is not working

The full loop is complete. A community member reports an issue, it lands in the database, the admin sees it on the dashboard, and their response is saved permanently. Your constituent issue reporter is fully functional.

Secret mission

Add a Status Lookup Page

Constituents want to check whether their issue has been addressed without needing to ask the politician directly. Build a public lookup page where they can enter their reference number and see the current status and any response.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • The community-reporter project folder on your Desktop.
  • The issues.db SQLite database file inside the project folder.
  • The node_modules folder containing Express and better-sqlite3 packages.

Keep everything running

No action needed. Choose this if you are still actively building or want to keep experimenting with the app.

  • Your project files stay in the community-reporter folder on your Desktop.
  • Run node server.js from inside the folder anytime to start your app again.
  • The issues.db file retains all submitted issues and responses permanently.

Pause - I'll come back to this later

No action needed. Since this project runs locally with no background processes, there is nothing to pause.

  • If the server is currently running, press Ctrl+C in the terminal to stop it.
  • Your database and all project files remain intact until you come back.

Delete - I don't want to use this again

Remove the entire project folder to clean up everything. This deletes all code, the database, and installed packages.

  • If the server is currently running, press Ctrl+C in the terminal to stop it.
  • Delete the project folder by running:
cd ~/Desktop
rm -rf community-reporter
  • Confirm deletion by running:
ls ~/Desktop

The community-reporter folder should no longer appear in the output.

Nice Work!

Nice Work!

You just built a fully functional constituent issue reporter from scratch. Community members can submit issues, administrators can respond, and everything is stored permanently in a database.

Here's what you learned:

  • Built a Node.js web server using Express 5 that serves static files and handles REST API requests with POST and GET endpoints.
  • Persisted data permanently using SQLite via better-sqlite3, so submissions survive server restarts without any external database server.
  • Built an admin dashboard that displays all community issues and lets you type and save responses, completing the full submission-to-resolution loop.
  • Secret Mission: Added a public status lookup page where constituents can check their issue by reference number.

Ready to quiz yourself?