Build a Cloud Database Backend on AWS

Deploy a secure PostgreSQL database on AWS RDS inside a custom VPC.

Introduction

30 Second Summary

Every manufacturing team tracks quality checks. When those records live in a spreadsheet or a SharePoint list, there is no way to enforce data rules, run complex queries, or build real applications on top of them.

In this project, you will build a cloud-hosted PostgreSQL database on AWS, secured inside a custom network, and migrate your real QC audit data into it from a CSV export.

What You'll Build

You will open DBeaver on your laptop, connect to a live PostgreSQL database running in AWS through a firewall rule scoped to your IP address alone, and run SQL queries against your actual production data.

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

  • A secure database connection from your laptop to a cloud-hosted PostgreSQL instance, where you can run SQL queries against your real QC audit records through an IP-restricted firewall.
  • A custom network architecture with public and private subnets spanning two Availability Zones, demonstrating enterprise-grade network segmentation you can walk someone through.
  • A proven migration path from a flat SharePoint export to a normalized relational schema. You can run a count query and see all 241 rows of historical data living in a properly constrained database.
  • Secret Mission: Create a restricted database user that can read and insert data but gets "permission denied" when attempting to delete or drop tables, proving least-privilege access control at the database layer.

Are there any prerequisites?

No prior AWS or database experience is required. This guide walks you through every setup step from scratch. You just need a Windows 11 machine and a CSV file exported from your SharePoint QC system (241 rows).

Before We Start

Before We Start...

Before you dive into provisioning infrastructure, let's lock in what you're building and why it matters.

You have a working QC Audit system built on Microsoft 365 low-code tools. Your mission is to rebuild its data layer as cloud-native infrastructure on AWS, proving you can architect backend systems from scratch.

Set Up Your AWS Account and Cost Protection

Every piece of infrastructure you build in this project lives inside AWS. Before you can provision a network or launch a database, you need an account to work in.

You also need a safety net. AWS charges for resource usage, and a cost budget ensures you get an alert before spending anything unexpected. In this step you will create your AWS account, set up that budget, and install the database client you will use throughout the project.

In this step, get ready to:
  • Create an AWS Free Tier account and sign into the Management Console.
  • Set up a monthly cost budget and earn $20 in bonus credits.
  • Install DBeaver Community Edition for connecting to your database later.
Create an AWS Free Tier account

AWS offers a Free Plan that gives you $100 in credits at sign-up and charges nothing until you explicitly upgrade to a paid plan. The account expires after 6 months or when credits run out.

  • Open the AWS Free Tier sign-up page by navigating to https://aws.amazon.com/free/ in your browser.
  • Choose Create a free account.
  • Select the Free Plan (you will not be charged).
  • Complete the sign-up form: enter your personal information, a credit card for identity verification only, and complete phone verification.
  • Select the Basic (Free) support plan when prompted.
  • Wait for the activation confirmation email. This usually arrives within a few minutes but can take up to 24 hours.

Why do I need a credit card?

AWS uses your credit card solely for identity verification. The Free Plan ensures you will not be charged unless you manually upgrade to a paid plan. Your $100 in credits covers all resources in this project.

You should see the Console Home dashboard with an Explore AWS widget. This confirms your account is active.

Activation email not arriving?

Check your spam folder. If it has been more than 30 minutes, try signing in at the console URL above anyway. Some accounts activate before the email arrives.

If you cannot sign in after 24 hours, contact AWS Support from the sign-up page. Help me troubleshoot my AWS account activation.

Set up a cost budget via AWS Budgets

AWS Budgets monitors your spending and sends an email alert if costs exceed your budget. Setting this up also earns you $20 in bonus credits from the Explore AWS activity program.

  • On the Console Home dashboard, find the Explore AWS widget.
  • Choose Set up a cost budget using AWS Budgets. This redirects you to the AWS Billing and Cost Management console.
  • On the Choose budget type page, choose Use a template (simplified) and then Monthly cost budget.
  • Enter QC-Audit-Phase1-Budget as the budget name.
  • Set the budgeted amount to 100.00.
  • Enter your email address to receive spend updates.
  • Choose Create budget.

What does this budget do?

AWS will send you an email alert if your spending exceeds or is forecasted to exceed $100.00. It will also notify you when 50%, 25%, or 10% of your credits remain, and when you have 15, 7, or 2 days left in your 6-month free period.

Now confirm the $20 bonus credit has appeared.

  • In the left navigation pane of the Billing and Cost Management console, choose Credits.
  • Confirm the page shows your $20 credit. It can take up to 10 minutes to appear, so refresh if needed.

You should see a credit entry for $20.00 on the Credits page. This confirms the Budgets activity completed successfully.

Credits page shows nothing?

Wait 10 minutes and refresh the page. The credit is processed asynchronously after you create the budget.

If the credit still does not appear after 10 minutes, sign out and sign back in, then navigate back to the Credits page. Help me find my AWS credits.

Install DBeaver Community Edition

DBeaver is a free, open-source database management tool. You will use it later to connect to your PostgreSQL database in AWS and run SQL queries against your QC audit data.

  • Open the DBeaver download page at https://dbeaver.io/download/ in your browser.
  • Download the Windows installer (the .exe file labelled for x86_64).
  • Run the downloaded installer with the default settings. Click through each page and choose Next until installation completes.

Do I need Java installed?

No. DBeaver bundles its own Java runtime, so you do not need to install Java separately. The installer handles everything.

  • Launch DBeaver after installation finishes. Press the Windows key, type DBeaver, and press Enter.

You should see the DBeaver welcome screen. This confirms the installation succeeded.

Windows blocks the installer?

If Windows SmartScreen shows a warning, choose More info and then Run anyway. DBeaver is a trusted open-source application.

Help me get DBeaver installed on Windows.

Your AWS account is live, your cost budget is protecting you, and your database client is ready. Next up, you will build the network foundation that your database will live inside.

Provision Your Network Foundation (VPC)

Your AWS account is set up with cost protection, and DBeaver is ready on your machine. Now you need somewhere for your database to live.

Every AWS resource needs a network. Without a custom VPC, your database would sit in the default VPC with overly permissive settings that anyone in your account could access. A custom VPC gives you explicit control over network segmentation, which is the first layer of enterprise security.

In this step, get ready to:
  • Create a custom VPC with public and private subnets across two Availability Zones.
  • Create a DB Subnet Group that tells RDS which private subnets to use for your database.
Create a VPC using the "VPC and more" wizard

AWS provides a wizard that creates an entire network stack in one step. You will use it to provision your VPC, subnets, route tables, and an internet gateway all at once.

  • In the AWS Management Console, type VPC in the top search bar and select VPC from the results.
  • On the VPC dashboard, click Create VPC.
  • For Resources to create, select VPC and more.

Why "VPC and more"?

This option creates your VPC along with subnets, route tables, and an internet gateway in one step. Choosing "VPC only" would force you to create each of these manually.

  • Keep Name tag auto-generation selected and enter qc-audit in the auto-generation field.
  • For IPv4 CIDR block, enter 10.0.0.0/16.
  • For Number of Availability Zones (AZs), select 2.
  • For Number of public subnets, select 2.
  • For Number of private subnets, select 2.
  • For NAT gateways, select None.
  • For VPC endpoints, select None.
  • Leave all other settings at their defaults and click Create VPC.

What is a CIDR block?

A CIDR block defines the range of IP addresses available inside your VPC. 10.0.0.0/16 gives you 65,536 addresses, which is far more than you need but leaves room for future expansion.

Wait for all resources to show a green status. The wizard creates your VPC, 4 subnets, route tables, and an internet gateway in about 30 seconds.

  • In the left navigation pane, click Your VPCs.
  • Confirm you see a VPC named qc-audit-vpc with CIDR showing 10.0.0.0/16.
  • Click Subnets in the left navigation pane.
  • Confirm you see 4 subnets: 2 with "public" in the name and 2 with "private" in the name, spread across two different Availability Zones.

Why two Availability Zones?

Availability Zones are physically separate data centres within an AWS Region. Spreading subnets across two zones means that if one data centre loses power, your infrastructure in the other zone keeps running.

RDS requires subnets in at least two Availability Zones before it will let you create a database. This is a hard requirement, not optional.

Don't see 4 subnets?

Make sure you are filtering by the correct VPC. At the top of the Subnets page, use the filter bar to select the VPC named qc-audit-vpc. If you still see fewer subnets, delete the VPC and re-run the wizard, double-checking you set the number of public subnets to 2 and private subnets to 2.

help me troubleshoot my VPC subnet setup

Create a DB subnet group

RDS needs to know which subnets inside your VPC it can place the database in. A DB Subnet Group is how you tell RDS exactly which private subnets to use.

  • In the top search bar, type RDS and select RDS from the results.
  • In the left navigation pane, click Subnet groups.
  • Click Create DB Subnet Group.
  • For Name, enter qc-audit-db-subnet-group.
  • For Description, enter Private subnets for QC Audit RDS.
  • For VPC, select qc-audit-vpc from the dropdown.
  • In the Add subnets section, select both Availability Zones that appear in the dropdown.
  • For Subnets, select only the two subnets that have "private" in their auto-generated name.
  • Click Create.

Why only private subnets?

Private subnets have no direct route to the internet. Placing your database in private subnets means it cannot be reached from the public internet by default. The only way in is through the rules you explicitly define in a Security Group.

Your new DB subnet group appears in the Subnet groups list on the RDS console.

  • Click qc-audit-db-subnet-group in the list to verify it shows two subnets across two Availability Zones.

Seeing an error about not enough AZs?

This means you selected subnets from only one Availability Zone. Go back and make sure you selected both Availability Zones in the dropdown before choosing your private subnets.

help me fix my DB subnet group AZ error

Before you move on, do a final check. Do you expect to see all these resources in a healthy state?

  • Switch back to the VPC Console by typing VPC in the top search bar.
  • Click Your VPCs and confirm qc-audit-vpc shows with CIDR 10.0.0.0/16.
  • Click Subnets and confirm 2 public and 2 private subnets across two Availability Zones all show Available status.

You now have a complete network foundation. Your VPC is segmented into public and private zones, and RDS knows exactly which private subnets to use for your database.

Next up, you will deploy a PostgreSQL database inside this network and see enterprise security in action when you try to connect to it.

Deploy Your RDS PostgreSQL Database

Your network foundation is in place. You have a custom VPC with isolated subnets spanning two Availability Zones, ready to host your database securely.

Now comes the core deliverable of this project. You're replacing a flat SharePoint list with a managed PostgreSQL engine that supports SQL queries, constraints, transactions, and indexing. RDS handles patching, backups, and high availability so you focus on architecture, not maintenance.

In this step, get ready to:
  • Launch a managed RDS PostgreSQL instance inside your VPC.
  • Lock down the database's Security Group so only your IP address can connect.
Launch the RDS instance

The RDS creation wizard walks you through engine selection, sizing, and connectivity. The Free tier template auto-selects cost-safe defaults so you stay within your credits.

  • Return to the RDS Console (Services > RDS).
  • In the left navigation, choose Databases.
  • Choose Create database.

You're now in the creation wizard. Configure each section as follows:

  • Under Engine options, select PostgreSQL.
  • Under Templates, select Free tier.
  • Under Settings, enter qc-audit-db as the DB instance identifier.
  • Set the Master username to qc_admin.
  • Choose a strong Master password and record it somewhere safe (you will need it later).
  • Confirm your password in the Confirm master password field.

Record your password now

Write your master password down in a secure location. You cannot retrieve it from AWS after this point. You will need it to connect from DBeaver in the next step.

  • Under Instance configuration, confirm it shows db.t4g.micro (auto-selected by the Free tier template).
  • Under Storage, confirm it shows 20 GiB of gp2 General Purpose SSD (auto-selected).

Now configure the network connectivity. This is where you connect the instance to your VPC.

  • Under Connectivity, set Virtual Private Cloud (VPC) to qc-audit-vpc.
  • Set DB subnet group to qc-audit-db-subnet-group.
  • Set Public access to Yes.
  • Under VPC security group, select Create new.
  • Enter qc-audit-db-sg as the New VPC security group name.
  • Leave all other settings at their defaults.
  • Scroll to the bottom and choose Create database.

The Databases page now shows your qc-audit-db instance with a status of Creating. This process takes 5-10 minutes.

Why set Public access to Yes?

Public access means the instance receives a public DNS name that your laptop can resolve. It does not mean anyone can connect. The Security Group you configure next acts as a firewall, restricting access to your IP address only.

In Phase 2, when your REST API lives inside the VPC, you will flip this to private-only. For now, public access with a tight Security Group lets you connect from DBeaver without needing a bastion host.

Lock down the Security Group to your IP only

While the database is creating, you can configure its firewall. A Security Group acts as a virtual firewall controlling which traffic can reach your database on a specific port.

  • Navigate to the EC2 Console (Services > EC2).
  • In the left navigation under Network & Security, choose Security Groups.
  • Find and select the Security Group named qc-audit-db-sg.
  • Open the Inbound rules tab at the bottom of the page.
  • Choose Edit inbound rules.

You'll see a default rule that was auto-created. Delete it so you can configure precise access.

  • Click Delete next to any existing rule to remove it.
  • Choose Add rule.
  • Set Type to PostgreSQL (this auto-fills port 5432).
  • Set Source to My IP.

The console auto-fills your current public IP address with a /32 suffix, meaning only your exact IP can connect.

  • Choose Add rule again to add a second rule.
  • Set Type to PostgreSQL (port 5432).
  • Set Source to Custom, then select the qc-audit-db-sg Security Group ID from the dropdown.
  • In the Description field, enter Future REST API Security Group.
  • Choose Save rules.

Your inbound rules list now shows two rules. Both allow PostgreSQL traffic on port 5432.

What do these two rules do?

The first rule allows your laptop (and only your laptop) to connect directly to the database. This is how DBeaver will reach the instance.

The second rule is a placeholder for Phase 2. When your REST API runs inside the same VPC, it will belong to this Security Group. The self-referencing rule means anything in the group can talk to the database on port 5432.

Don't see qc-audit-db-sg in the list?

The Security Group is created when you click Create database in the RDS wizard. If it hasn't appeared yet, wait a minute and refresh the Security Groups page. Confirm you selected Create new (not Choose existing) in the RDS connectivity section.

Help me find my Security Group in the EC2 Console.

Verify the database is available

Before you check, predict what you expect to see. Has enough time passed for the instance to finish creating? What status do you think it will show?

  • Navigate back to the RDS Console (Services > RDS > Databases).
  • Look at the Status column for qc-audit-db.

The status should show Available. If it still says Creating, wait another minute and refresh the page.

  • Click on qc-audit-db to open the instance details.
  • In the Connectivity & security tab, find the Endpoint field.
  • Copy the full endpoint address (it looks like qc-audit-db.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com).
  • Save this endpoint somewhere accessible. You will need it in the next step to connect from DBeaver.

Still showing Creating after 10 minutes?

RDS provisioning occasionally takes longer in high-traffic regions. Confirm you selected the Free tier template (not Production or Dev/Test) as those can have additional configuration steps. Refresh the page every 30 seconds.

My RDS instance is stuck in Creating status. Help me troubleshoot.

Your database is live and locked down. Next up, you'll connect to it from DBeaver and hit the dual-layer security model face to face.

Connect DBeaver to Your Database

Your RDS instance is up and running. Now for the real test. Can you actually reach it from your laptop?

DBeaver will connect to your RDS endpoint over the internet. Multiple networking layers need to align for this to work: DNS resolution, Security Group rules, and subnet routing.

In this step, get ready to:
  • Configure a PostgreSQL connection in DBeaver using your RDS endpoint.
  • Diagnose and resolve any networking issues blocking the connection.
  • Verify end-to-end connectivity from your laptop to the database.
Attempt connection from DBeaver

Your DBeaver installation is ready. Time to point it at your RDS instance and see if traffic flows.

  • In DBeaver, click Database in the menu bar.
  • Click New Database Connection.
  • Select PostgreSQL from the driver list and click Next.

Driver download prompt

If this is your first PostgreSQL connection in DBeaver, it will prompt you to download the JDBC driver. Click Download and wait for it to finish. This only happens once.

  • In the Host field, paste the RDS endpoint you recorded from Step 3 (it looks like qc-audit-db.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com).
  • Set Port to 5432.
  • Set Database to postgres.
  • Enter qc_admin in the Username field.
  • Enter your master password (the one you set in Step 3) in the Password field.

Before you click Test Connection, think for a moment. Your database is in a private subnet. Do you think your laptop can reach it on the first try?

  • Click Test Connection.

The connection times out. This is expected and intentional.

Why did the connection time out?

Your RDS instance sits in a private subnet. The "VPC and more" wizard you used in Step 2 created private subnets without a route to the internet gateway. Even though the instance has "Publicly Accessible" set to Yes and the Security Group allows your IP, traffic cannot flow if the subnet's route table has no path back to the internet.

This is AWS's defense-in-depth model at work. Security Groups alone are not enough. The route table must also permit the traffic path. Two independent layers must both say "yes" before a connection succeeds.

Already connected?

If your Test Connection succeeded on the first try, your VPC routing is already configured. Skip ahead to the "Verify connection" substep below.

Diagnose and fix the connection

The timeout exposed a missing route. You need to add an internet gateway route to the route table that governs your private subnets.

  • Switch back to the AWS Management Console in your browser.
  • Open the VPC Console (search for VPC in the top search bar and select it).
  • In the left navigation pane, click Route tables.
  • Find the route table associated with your private subnets. It will have "private" in its auto-generated name from the qc-audit prefix.
  • Select that route table by clicking its checkbox.
  • Click the Routes tab in the lower panel.

You should see only a single local route (10.0.0.0/16 targeting local). There is no route to 0.0.0.0/0. That missing route is why your connection timed out.

  • Click Edit routes.
  • Click Add route.
  • In the Destination field, enter 0.0.0.0/0.
  • In the Target field, select Internet Gateway, then select the internet gateway attached to your VPC (it will start with igw-).
  • Click Save changes.

What does this route do?

The route 0.0.0.0/0 means "all IPv4 addresses not matched by a more specific route." Pointing it at the internet gateway tells AWS to send outbound internet traffic (including responses to your laptop's connection request) through the gateway.

Next, verify your Security Group still has the correct IP. Your public IP can change if your network restarted since you created the rule.

  • Open a new browser tab and navigate to https://checkip.amazonaws.com.
  • Note the IP address displayed.
  • In the AWS Console, navigate to EC2 > Security Groups.
  • Find and select qc-audit-db-sg.
  • Click the Inbound rules tab.
  • Confirm the first rule's Source matches the IP you just saw. If it does not match, click Edit inbound rules, update the source to My IP, and click Save rules.

Still timing out after adding the route?

Double-check that you edited the correct route table. The private subnet route table (not the public one) is the one associated with your RDS subnets. Click the Subnet associations tab on the route table to confirm it lists your private subnets.

Also confirm your RDS instance shows Publicly accessible: Yes in the RDS Console's Connectivity & security tab. If it says No, modify the instance and enable public access.

Need more help? Why is my DBeaver connection to RDS still timing out after I added the internet gateway route?

Verify connection

With the route table fixed and the Security Group confirmed, try the connection again.

  • Switch back to DBeaver.
  • Click Test Connection again.

This time you should see a success dialog confirming the connection. DBeaver runs a quick query against the server to verify.

  • Click Finish to save the connection.
  • In the Database Navigator panel on the left, expand the connection tree: qc-audit-db > Databases > postgres > Schemas > public.

You should see a green "Connected" indicator and the full tree structure. This confirms end-to-end connectivity: your laptop reaches the internet, passes through the Security Group firewall on port 5432, enters the VPC via the internet gateway, and arrives at your RDS instance.

What did this prove?

You just experienced AWS's dual-layer security model firsthand. The Security Group (layer 1) allowed your IP on port 5432. The route table (layer 2) needed an explicit path to the internet gateway for traffic to flow. Both layers had to align before the connection succeeded.

In a production environment, this defense-in-depth means a misconfigured Security Group alone cannot expose a database. The route table acts as an independent safety net.

Connection shows an authentication error instead of timeout?

An authentication error means the network path works but your credentials are wrong. Double-check that you entered qc_admin as the username and the exact master password you set during RDS creation in Step 3.

Need help? How do I fix a PostgreSQL authentication error in DBeaver when connecting to my RDS instance?

Your connection is live. Next up, you'll design a proper relational schema and migrate your 241 rows of real QC data from CSV into your new cloud database.

Design Your Schema and Migrate Your Data

You have a live, connected database. But right now it is an empty shell. Your SharePoint list stored QC audit data as a flat file with no type enforcement, no constraints, and no protection against duplicate or malformed entries.

In this step, you will design a relational schema that enforces data integrity at the database layer. Then you will migrate your 241 historical audit records from CSV into the new structure, proving the migration path from low-code to cloud-native.

In this step, get ready to:
  • Create a dedicated database and define the table schema with constraints and indexes.
  • Import your 241 historical QC audit records from CSV using DBeaver's import wizard.
  • Validate that the migration completed correctly with SQL queries.
Create the database

PostgreSQL lets you host multiple databases on a single instance. You will create a dedicated database for your QC audit system rather than dumping tables into the default postgres database.

  • In DBeaver, right-click your qc-audit-db connection in the Database Navigator panel.
  • Select SQL Editor, then New SQL Script.
  • Create the new database by executing this SQL statement:
CREATE DATABASE qc_audit_system;
  • Place your cursor on the line and press Ctrl+Enter to execute the statement.

You should see a success message in the output panel at the bottom of DBeaver.

Seeing a "database already exists" error?

If you ran this statement before and it succeeded, the database already exists. You can safely continue to the next instruction.

help me fix a CREATE DATABASE error in DBeaver

Reconnect to the new database

DBeaver is still connected to the default postgres database. You need to switch your connection to target the new qc_audit_system database before you can create tables in it.

  • In the Database Navigator, right-click your qc-audit-db connection.
  • Select Edit Connection.
  • In the Database field, change postgres to qc_audit_system.
  • Click Finish.
  • Right-click the connection again and select Reconnect.

After reconnecting, expand the connection tree. You should now see qc_audit_system as the active database with Schemas > public underneath it.

Create the table schema

Your SharePoint list stored everything as flat text. The schema you are about to create enforces types, constraints, and a primary key so that invalid data cannot enter the system.

  • Open a new SQL script by right-clicking your connection and selecting SQL Editor then New SQL Script.
  • Paste the following schema creation script into the editor:
-- QC Audit System Schema
-- Phase 1: Backend Infrastructure & Database Architecture
-- Replaces flat SharePoint list with normalized relational structure

CREATE TABLE qc_audits (
    id SERIAL PRIMARY KEY,
    po_number INTEGER NOT NULL UNIQUE,
    loom_check VARCHAR(20) NOT NULL DEFAULT 'Pending',
    construction_check VARCHAR(20) NOT NULL DEFAULT 'Pending',
    packaging_check VARCHAR(20) NOT NULL DEFAULT 'Pending',
    status VARCHAR(30) NOT NULL DEFAULT 'Pending Review',
    manager_comments TEXT,
    created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Index on status for filtering by audit state (used by Management by Exception logic)
CREATE INDEX idx_qc_audits_status ON qc_audits(status);

-- Index on created_date for chronological queries
CREATE INDEX idx_qc_audits_created_date ON qc_audits(created_date);

-- Comment documenting the business logic
COMMENT ON TABLE qc_audits IS 'QC Audit records for Electric Blanket manufacturing. Migrated from SharePoint list.';
COMMENT ON COLUMN qc_audits.po_number IS 'Unique Purchase Order number identifying the production batch.';
COMMENT ON COLUMN qc_audits.status IS 'Audit status: Pending Review, Approved, Exception - Requires Manager Review, Rejected.';
  • Select all the SQL text (Ctrl+A) and press Ctrl+Enter to execute the entire script.

What does this schema do?

  • SERIAL PRIMARY KEY auto-generates a unique ID for every row. No more relying on SharePoint's internal list IDs.
  • NOT NULL and UNIQUE constraints on po_number prevent duplicate or missing purchase orders from entering the system.
  • DEFAULT 'Pending' values mean new records get sensible starting states without requiring the caller to specify them.
  • Indexes on status and created_date speed up the queries your Management by Exception dashboard will run most often.
  • In the Database Navigator, right-click your connection and select Refresh.
  • Expand qc_audit_system > Schemas > public > Tables.

You should see qc_audits listed as a table. The schema is live.

Don't see the table after refreshing?

Make sure you reconnected to qc_audit_system (not postgres) before running the script. If you accidentally created the table in the wrong database, reconnect to qc_audit_system and run the script again.

help me find my qc_audits table in DBeaver

✔️ Awesome, I've got everything!

Great. Double check you see qc_audits under Tables in the navigator, then continue to the import.

ⓧ I'd like to double check the full code

Here is the complete schema script. Compare it against what you executed:

-- QC Audit System Schema
-- Phase 1: Backend Infrastructure & Database Architecture
-- Replaces flat SharePoint list with normalized relational structure

CREATE TABLE qc_audits (
    id SERIAL PRIMARY KEY,
    po_number INTEGER NOT NULL UNIQUE,
    loom_check VARCHAR(20) NOT NULL DEFAULT 'Pending',
    construction_check VARCHAR(20) NOT NULL DEFAULT 'Pending',
    packaging_check VARCHAR(20) NOT NULL DEFAULT 'Pending',
    status VARCHAR(30) NOT NULL DEFAULT 'Pending Review',
    manager_comments TEXT,
    created_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Index on status for filtering by audit state (used by Management by Exception logic)
CREATE INDEX idx_qc_audits_status ON qc_audits(status);

-- Index on created_date for chronological queries
CREATE INDEX idx_qc_audits_created_date ON qc_audits(created_date);

-- Comment documenting the business logic
COMMENT ON TABLE qc_audits IS 'QC Audit records for Electric Blanket manufacturing. Migrated from SharePoint list.';
COMMENT ON COLUMN qc_audits.po_number IS 'Unique Purchase Order number identifying the production batch.';
COMMENT ON COLUMN qc_audits.status IS 'Audit status: Pending Review, Approved, Exception - Requires Manager Review, Rejected.';
Import your CSV data using DBeaver

Your table structure is ready. Now you will fill it with the 241 rows of historical QC audit data from your SharePoint CSV export.

  • In the Database Navigator, expand qc_audit_system > Schemas > public > Tables > qc_audits.
  • Right-click qc_audits and select Import Data.
  • In the import wizard, select CSV as the source format.
  • Click Browse and navigate to your 241-row CSV file on your local machine.
  • Click Next to advance to the column mapping screen.

What is column mapping?

Your CSV headers (from SharePoint) probably use different names than your database columns. For example, your CSV might say "PO Number" while your database column is po_number. DBeaver lets you visually match each CSV column to the correct database column.

DBeaver shows a preview of the data so you can confirm the mapping looks right before running the import.

  • Map each CSV column header to the corresponding database column in the mapping screen.
  • Review the preview to confirm the data looks correct.
  • Click Proceed to execute the import.

DBeaver displays a transfer progress dialog and reports the number of rows imported when complete.

Import failed or row count is wrong?

Check that your column mappings are correct. A type mismatch (e.g., mapping a text column to po_number which expects an integer) causes the import to fail on those rows.

If your CSV has a header row, make sure the "Header" option is enabled in the CSV settings page of the wizard so DBeaver skips it.

help me fix a CSV import error in DBeaver

Validate the migration

Data is only useful if it arrived intact. You will now run three queries that prove the migration succeeded.

  • Open a new SQL script by right-clicking your connection and selecting SQL Editor then New SQL Script.
  • Paste the following validation queries:
SELECT COUNT(*) FROM qc_audits;
SELECT * FROM qc_audits ORDER BY created_date DESC LIMIT 5;
SELECT status, COUNT(*) FROM qc_audits GROUP BY status;
  • Select all and press Ctrl+Enter to execute.

Before you check the results, what do you expect the first query to return?

What should I see?

  • The first query returns 241. This matches your SharePoint export row count exactly.
  • The second query shows your five most recent audit records. Confirm dates and PO numbers look familiar from your original data.
  • The third query shows a status breakdown. You should see counts for statuses like Pending Review, Approved, and any exception states your QC process uses.

Count doesn't match 241?

If the count is lower, some rows failed during import. Check DBeaver's output log for error details. The most common cause is a duplicate po_number value in your CSV. The UNIQUE constraint rejects the second copy.

If the count is 0, you may have imported into the wrong database. Check that your connection is targeting qc_audit_system (not postgres).

help me diagnose a row count mismatch after CSV import

Your 241 historical QC audit records are now live in a managed PostgreSQL database, protected by type constraints, uniqueness rules, and indexed for fast querying. The migration from SharePoint to cloud-native is complete.

Secret mission

Create a Least-Privilege Application User

Your database is live and your admin connection works. But in production, your REST API should never connect as the all-powerful admin user. Can you create a restricted PostgreSQL role that can only read and insert data, proving you understand least-privilege access control at the database layer?

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. Your Free Tier credits cover the RDS instance for up to 6 months, so there are no ongoing charges while your account remains on the Free Plan.

Resources you used:

  • RDS PostgreSQL instance (qc-audit-db) on db.t4g.micro. Covered by Free Tier credits.
  • VPC (qc-audit-vpc) with 4 subnets, route tables, and internet gateway. No cost.
  • Security Group (qc-audit-db-sg). No cost.
  • DB Subnet Group (qc-audit-db-subnet-group). No cost.
  • DBeaver Community Edition (local install on your machine).
  • Local SQL files (schema.sql, create_api_user.sql).

Keep everything running

No action needed. Choose this if you are still actively building or plan to start Phase 2 (REST API) soon.

  • Your Free Tier credits cover the RDS instance for the duration of your 6-month free period.
  • The VPC, subnets, security group, and DB subnet group incur no charges.
  • DBeaver remains installed on your machine at no cost.

Pause - I'll come back to this later

Stop the RDS instance to conserve your Free Tier credits while preserving all your data. The VPC and networking resources remain in place at no cost.

  • Open the RDS Console and choose Databases in the navigation pane.
  • Select qc-audit-db.
  • Choose Actions, then Stop temporarily.
  • In the Stop DB instance temporarily window, select the acknowledgement that the instance will automatically restart after 7 days.
  • Choose Stop temporarily.

What happens after 7 days?

RDS automatically restarts your instance after 7 days of being stopped. If you still want it paused, repeat the stop process. Your data is preserved regardless.

Delete - I don't want to use this again

Remove all project resources completely. This is permanent and cannot be undone.

Follow these steps in order. The RDS instance must be deleted before the VPC because it holds network interfaces inside the VPC.

  • Delete the RDS instance:
  • Open the RDS Console. Choose Databases, then select qc-audit-db.
  • Choose Actions, then Delete.
  • Uncheck Create final snapshot? if you do not need a backup.
  • Type delete me in the confirmation box and choose Delete.
  • Wait for the instance to disappear from the Databases list. This can take a few minutes.
  • Delete the DB Subnet Group:
  • In the RDS Console, choose Subnet groups in the navigation pane.
  • Select qc-audit-db-subnet-group and choose Delete.
  • Confirm the deletion.
  • Delete the VPC (this removes subnets, route tables, security groups, and the internet gateway):
  • Open the VPC Console. Choose Your VPCs in the navigation pane.
  • Select qc-audit-vpc and choose Actions, then Delete VPC.
  • Type delete to confirm and choose Delete.

What does deleting the VPC remove?

When you delete a VPC through the console, AWS also deletes its subnets, route tables, internet gateways, network ACLs, and security groups for you. You do not need to delete these individually.

  • Uninstall DBeaver:
  • Open Windows Settings, then Apps, then Installed apps.
  • Search for DBeaver, click the three-dot menu, and choose Uninstall.
  • Delete local SQL files:
  • Delete schema.sql and create_api_user.sql from wherever you saved them on your machine.

Nice Work!

Nice Work!

You just built a production-grade cloud database backend from scratch. Your QC Audit data has officially graduated from a flat SharePoint list to a secured, queryable PostgreSQL database running inside enterprise-grade AWS infrastructure.

You've learned how to:

  • Architect a custom VPC with multi-AZ subnets and Security Groups that restrict database access to a single IP address, demonstrating network segmentation and least-privilege at the infrastructure layer.
  • Provision a managed RDS PostgreSQL database and diagnose a real connection failure caused by AWS's defense-in-depth defaults, proving you can troubleshoot cloud networking issues.
  • Design a normalized relational schema with constraints and indexing, then migrate 241 rows of real historical QC audit data from CSV into the cloud database using DBeaver.
  • Secret Mission: Created a least-privilege PostgreSQL role for your future REST API, proving you can enforce application-layer access control that blocks destructive operations at the database level.

Ready to quiz yourself?