Network Inventory Management API

This project provides a functional network inventory system for tracking sites, hardware, phone numbers, and circuits across an organization. The system is built with a PostgreSQL relational database and a Python Flask REST API that implements CRUD operations.

Project Overview

Where I work today, we often struggle to keep network equipment accurate across multiple locations.
This database helps to solve that problem by centralizing that information into a relational database. While enforcing data integrity and the use of API’s for easy access to the information.

Database Schema

The database is implemented in PostgreSQL using raw SQL.

Tables

Table	Description
sites	Physical locations (data centers, branch offices, etc.)
hardware	Network devices associated with a site
circuits	Telecommunications circuits terminating at sites
phone_numbers	Site-level voice service numbers

Relationships

Relationship	Type
Each site has many hardware	1 : Many
Each site has many circuits	1 : Many
Each site has many phone_numbers	1 : Many

Table States

All child tables include foreign keys referencing sites.site_id with:

ON DELETE CASCADE
This maintains correctness by preventing orphaned records.

Performance Enhancements
A B-tree index was added on hardware.site_id:

CREATE INDEX idx_hardware_site_id ON public.hardware (site_id);
This speeds up frequent joins and lookups by site.
 
## API Reference
Base URL: http://127.0.0.1:3000
Method Path                 Body Description
GET	   /api/hardware	    Returns all hardware
GET	   /api/hardware/<id>	Single hardware by ID
POST   /api/hardware	    Creates hardware record
PUT    /api/hardware/<id>	Updates hardware record
DELETE /api/hardware/<id>	Deletes hardware record

Postman collection is included demonstrating all four HTTP verbs.
Example request body:
Request: GET /api/hardware/1
Body: 
{
  "hostname": "rtr-01",
  "hardware_type": "Router",
  "model": "ISR4451",
  "serial_number": "FTX12345ABC",
  "site_id": 1
}

Running the API

python app_hardware.py Test using Postman or Insomnia

ER Diagram

Created in pgAdmin (Week 1 exercise, updated): erDiagram.mmd included in project directory.

Migrations

Initial schema created via SQL script: my_blog.sql (renamed for network inventory project use) Future improvements planned with Alembic or Flask-Migrate for: Controlled versioning Safer schema evolution Data migrations

Retrospective Reflection

How did the design evolve over time?

• The first draft only tracked hardware.
• Through brainstorming + ERD work, I recognized the need to normalize the schema:
• Avoid duplicate site information in multiple tables
• Represent circuits and phone numbers as first-class entities
• Enforce cascading deletion to maintain data hygiene
• The design became more scalable, relational, and realistic.

Raw SQL vs ORM — Why?

I intentionally used raw SQL via psycopg2 to:

• Build real SQL expertise
• Directly control tables and indexes
• I would consider SQLAlchemy ORM in life if I was to become a DBA, but as I just wanted a deeper understanding of SQL (my systems use SQL, but I can't edit or manipulate it):

Future Improvements if I where to continue buildilng it

• Planned enhancements include:
• More indexes based on query analytics
• Dashboards
• This database is now a solid foundation for network tooling and automation.

Included Artifacts (for final submission)

• app_hardware.py — Flask API
• my_blog.sql — Database schema + constraints
• erDiagram.mmd — Data model diagram
• Postman Collection export
• README.md (this file)