Your team

You will do Part 3 of Project 1 with the same team as for Parts 1 and 2. If your team partner dropped the class and you did not submit a contingency plan for this with your Part 1 submission, then unfortunately you will still have to complete the whole project by yourself. If your team partner dropped the class and you did submit a contingency plan for this with your Part 1 submission, then you are welcome to switch to this reduced version of your project.

Overview of Part 3 of Project 1

Important notes

• You should make sure that you loaded sufficient data into your database to show off all functionality of your application.
• You can make (hopefully small) changes to the SQL schema that you created in Part 2. If for some reason you feel the need to make any substantial changes, please check with your project mentor ahead of time to avoid any last-minute surprises.

---

In this final part of Project 1, you will finish building the application that you proposed in Part 1, on top of the database that you created in Part 2. For the final evaluation of Project 1, you will need to submit your code for your application and a README file on Gradescope by Thursday November 19 at 5 p.m. ET (see below for further instructions). Also, both team members will meet with your project mentor on Friday November 20 or Monday November 23. Your project mentor will contact you shortly to schedule a 15-minute meeting for those days. Your implementation will be on Python 3 using Flask, and should satisfy these requirements:

• Your application must execute SQL query strings on your database on our class's PostgreSQL server. You cannot use an Object-Relational Mapper, or ORM. An important goal of this project is that you practice writing and debugging SQL queries as part of your application, so tools that attempt to make this "too easy" are not permitted.
• Your application must provide a way to view or interact with all the entities and relationships in your final E/R diagram.
• Your application's web interface does not need to be beautiful or sophisticated. Plain text pages are acceptable. You will not get additional credit for fancy interfaces, as this is not the focus of our course.
• In general, you can use any third-party libraries you want except for ORMs or other libraries that simplify database access, which are not allowed. If you are unsure if a library is permitted, ask your project mentor.

The following resources may be helpful for learning both Python and Flask:

• Java-to-Python Primer
• Python tutorial
• Flask documentation
• Flask tutorial
• Jinja template documentation
• Jinja tutorial

Getting started

Your job is to implement your proposed web application. To help you out, we have provided a bare-bones Flask web application, server.py, available here. It provides code that connects to a database URL, and a default index page. Take a look at the comments in server.py to see how to use or modify the server. In particular, note that you will need to configure your PostgreSQL username and password in a private .env file for local development, and through Secret Manager on Cloud Run, as described below, rather than include them in server.py. You will need to connect to your database from Part 2. Please read all these directions. Once you get it running, you should start working on your custom logic.

Important: The starter uses Python 3.14. Please keep requirements.txt, Procfile, and .python-version at the root of your repository, so that Google Cloud can build and run your application.

A short explanation of SQLAlchemy

We use a Python package called SQLAlchemy to simplify our work for connecting to the database. For example, the following code loads useful functions from the package:

from sqlalchemy import *

SQLAlchemy is able to connect to many different types of DBMSs, including SQLite, PostgreSQL, MySQL, and Oracle. Each such DBMS is called an "engine." The create_engine() function sets up the configuration for the specific DBMS we want to connect to:

engine = create_engine(DATABASEURI)

Here, DATABASEURI denotes the database connection URL. The starter builds this URL from your database settings.

Given an engine, we can then connect to it (this is similar to how psql connects to our class's PostgreSQL database server):

conn = engine.connect()

At this point, the conn connection object can be used to execute queries to the database, which must be "wrapped" with text() as below. For queries that change data, follow the query with conn.commit() to save the changes. This is basically what psql is doing under the covers:

cursor = conn.execute(text("select 1"))

The execute function takes a SQL query string as input and returns a cursor object. You can think of this as an iterator over the result relation. This means you can run SELECT * on, say, a million-row table and not run out of memory. Instead of receiving the entire result at once, this object lets you treat the result as an iterator and call next() on it, or loop through it. See the SQLAlchemy documentation for a detailed description.

# this fetches the first row if called right after
# the execute function above. It also moves the
# iterator to the next result row.
record = cursor.fetchone()
# this will fetch the next record, or None if
# there are no more results.
second_record = cursor.fetchone()
# this loops through the results of the cursor one by one
for row in cursor:
  print (list(row))

The above description is a way to directly write and run SQL queries as strings, and directly manipulate the result relations. SQLAlchemy also includes an Object Relational Mapper that provides an interface that hides SQL query strings and result sets from you. In this project, you will directly write and run SQL queries, and cannot use any ORM functionality.

IMPORTANT NOTE: Your code should avoid the SQL injection vulnerability that we discussed in class. Specifically, your code should never build "raw" query strings for the database using user input, but rather you should use parameters, etc. as discussed in class. Your grade for the project will suffer if your code does not avoid SQL injection vulnerabilities.

(Optional) Using a toolkit for application front-end

You are welcome to use the Bootstrap framework to style the front-end of your application. Note that this is by no means necessary, and styling your application will not impact grading in any way; see "Grading for Project 1, Part 3" below. You are also welcome to use AJAX to create more complex interactions with your site. However, if you do so, you must use JQuery and you must add a section to your README file describing where each call occurs, what triggers the call, what endpoint it hits, and the call's purpose. Furthermore, any JavaScript you write must be well commented, and cannot rely on frameworks or libraries not mentioned here. Particularly, you cannot use any tools that alter the way you interact with the database (e.g., GraphQL). If you have any questions regarding specific frameworks, please contact your project mentor.

Working with a version control system

Since you are working collaboratively with your teammate, we require you to use the version control system git for your code on GitHub. You should use a private repository on GitHub for this project.

We cover below the basics of git and GitHub. With this setup, you can code on your own desktop, commit and push your changes to the GitHub repository, and then have the updated changes deployed automatically on Google Cloud Run. You can also code without your teammate being in the same room, and your teammate will be aware of the changes after "pulling" the code from GitHub. Conversely, your teammate will also be able to contribute to the same code repository, so you can both work collaboratively. Please follow these steps to get started with git and GitHub:

1. Register an account on GitHub. You will need to provide/verify your email address and provide a username. There is no requirement on what email or username you use here. Your teammate should also register for a different account, so that you both have access to the code repository.
2. Once your account is created, log into GitHub. Create a repository by clicking on Create a new repository.
3. Give your repository a name, such as "w4111-proj1". From here on we will use <projectname> to denote the name that you chose in this step for your project. Make sure that you make this repository Private by choosing the correct radio button; also do not check the README box. Use main as the default branch.
4. Give your teammate access to the repository: If your repository is at URL <REPO_URL>, go to the webpage <REPO_URL>/settings/access by manually adding "/settings/access" to the URL (e.g., if your repository is at https://github.com/abc234/cs4111, then go to https://github.com/abc234/cs4111/settings/access). Click "Add people" and then enter your teammate's GitHub username or email. Then click "Add <EMAIL/USERNAME> to this repository”. Your teammate will receive an invitation by email, which your teammate should accept.
5. Now run the following commands on your own computer to download and extract our skeleton Flask web application, initialize a local git repository, and push the starter files to GitHub. Run the commands one line at a time and replace all points that are surrounded by "< >" with your information (for example, "<your full name>" should be changed to "Luis Gravano" for the instructor).

   Use the HTTPS webpage address of your team's repository for <REPO_URL>, without a trailing .git. This does not require setting up an SSH key. If you do not already have HTTPS authentication configured, follow the instructions available here to create a personal access token with read and write access to your repository's contents. When Git asks for your password, use the token, not your GitHub account password. Do not put the token in the repository URL or in your source files.

   Important: Keep proxy-key.json outside the starter directory. Store your local PostgreSQL credentials only in the ignored .env file described below, not in your source code. Never commit credentials.

   # download and extract our skeleton Flask web application
   cd ~
   
   # Option 1: on MacOS, use the built-in curl command:
   curl -LO https://www.cs.columbia.edu/~gravano/cs4111/Proj1-3/webserver.tar
   # Option 2: Alternatively, if Homebrew is available on your computer, install wget:
   brew install wget
   # ... and then:
   wget https://www.cs.columbia.edu/~gravano/cs4111/Proj1-3/webserver.tar
   
   tar xf webserver.tar
   mv webserver <projectname>
   cd <projectname>
   # configure your git environment
   git config --global user.name "<your full name>"
   git config --global user.email "<email you used to register for GitHub>"
   # initialize git repository, and push the skeleton files up to the server
   git init -b main
   # use the HTTPS repository address from Step 4, not an SSH address
   git remote add origin <REPO_URL>.git
   # include hidden starter files, while respecting .gitignore
   git add .
   git status
   git commit -m "initial commit"
   git push -u origin main  # don't enter your GitHub password here; instead, use the personal access token as discussed above
   

   You have now created a local git repository, made your first commit, and pushed the starter files onto GitHub. Your usual workflow after these initial steps will be a bit different than this, as described in the next step.
6. Your workflow with git will typically comprise the following steps:
   1. Pull your changes from the GitHub repository so that your code is up-to-date: git pull. Note that this step is necessary because, when working on a project with a teammate, your teammate may have updated and pushed the code while you were not working on the project, so you should always pull the changes before beginning to work on a new revision.
   2. Work on the project, make some changes locally, and test them.
   3. See what git thinks you have changed: git status
   4. Add any modified files to the next "commit." For example, if you modified files server.py and README, then run: git add server.py README
   5. Commit your changes (locally) with an appropriate commit message: git commit -m "added feature X"
   6. Push your changes to the GitHub server: git push. Once you have completed the Cloud Run setup below, changes pushed to main will deploy automatically. If you are working on another branch, merge your changes into main when they are ready to deploy.
7. Step 5 is more or less all you need to get started if you are working on this project alone. However, if you are working as a two-person team, you might even be using different computers to do your coding. (You can use your desktop or laptop for writing the code, and Google Cloud Run will deploy changes from GitHub, as discussed in the "Running your application..." section below.) So if you carried out Step 5 above for your team, then your teammate will need to perform the following steps to get a copy of the code on their desktop or laptop. Similarly, you will have to perform the steps below for any new computer that you want to use to continue developing your code:

   # configure your git environment, as in Step 5
   git config --global user.name "<your full name>"
   git config --global user.email "<email you used to register for GitHub>"
   # clone (i.e., download) the repository onto your local machine
   # use the username of the owner of the repository here
   git clone <REPO_URL>.git
   # you're done setting up; you can now continue as in Step 6

git provides many other powerful functions that we haven't talked about. For more information, you can refer to this tutorial, and documentation on that website. If you encounter a problem with git, you can typically do a quick search with the error message and get a lot of helpful information online. Feel free also to come to office hours with any of your questions about git and GitHub.

Developing and testing your application locally

Each teammate should follow these steps on the computer they use for development, after downloading or cloning the starter. The commands below use a macOS or Linux terminal.

1. Install Python 3.14 if needed. Open a terminal in your project directory (the directory containing server.py), create a virtual environment, and install the required Python packages:

   python3.14 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements.txt

   Run source .venv/bin/activate again whenever you open a new terminal to work on the application.
2. Create a file named .env in the same directory as server.py, with your PostgreSQL credentials from Part 2:

   DB_USER='<your PostgreSQL username>'
   DB_PASSWORD='<your PostgreSQL password>'
   DB_HOST=127.0.0.1
   DB_PORT=5454
   DB_NAME=proj1part2

   Replace the placeholders with your own values, keeping the quotes. These are your PostgreSQL credentials, not your Google or GitHub credentials. Flask loads this file when you run python server.py. The starter's .gitignore excludes .env and .venv/; never commit or submit them. Each teammate creates their own local file. Cloud Run does not receive this file: configure its settings separately using the setup directions below.
3. Run the Cloud SQL Auth Proxy on the same computer as your local Flask application. (A proxy running on your Part 2 virtual machine is not reachable through your laptop's 127.0.0.1.) For this, follow the download instructions for your operating system to install the Cloud SQL Auth Proxy. Keep the proxy binary and your existing proxy-key.json from Part 2 (which you received by email from us for Part 2) outside the project directory; you do not need a new key. In a separate terminal, from the directory containing the proxy binary, run:

   ./cloud-sql-proxy --credentials-file="<full path to proxy-key.json>" --address=127.0.0.1 --port=5454 coms4111-db-ta:us-east1:server-4111-fall-2026

   Replace the key path with the location on your computer. Leave this terminal and the proxy running while you develop.
4. In your application terminal, with the virtual environment activated, start Flask:

   python server.py

   Open http://127.0.0.1:8111/ in your browser. If the example table is missing, use the Initialize example table button, then test the Add form. Do not initialize over meaningful existing data. Stop Flask with Control-C and restart it after changing your code or .env. This development server is only for local testing; Cloud Run uses the Procfile instead.

Running your application on Google Cloud Run

Once you have developed (a preliminary version of) your web application, you will deploy it to Google Cloud Run, as follows

1. Go to the Google Cloud Console and select the project that you used for Part 2. You have already set up your account, billing, and PostgreSQL access following our instructions for Part 2; you do not need to create another project or repeat those steps.
2. (One time setup) Follow our directions to connect your team's GitHub repository to Cloud Run, configure Secret Manager and the Cloud SQL Auth Proxy sidecar, and allow public access. These instructions use your existing project, Compute Engine default service account, proxy-key.json, and PostgreSQL credentials from Part 2. Enable the additional APIs and grant the required roles as directed.
3. Make sure you have committed and pushed all the latest changes to your code to your GitHub repository on main. (See Step 6 above.)
4. Wait for the automatic build and deployment to finish. You can check its progress under "Cloud Build" and then "History" in the Google Cloud Console. You do not need to start the Python server manually or copy your code to a virtual machine.
5. Go to the HTTPS URL displayed for your Cloud Run service in an incognito or private browser window to check that it worked. Follow the setup directions to check the database connection and initialize the example table, if needed, then test the example's Add form.

You will need this URL when presenting the project to your mentor. Please do not delete or disable your Cloud Run service when you are ready to submit, so that the URL that you include with your project submission works.

Keeping your application running for your meeting with your mentor: Cloud Run manages the application process for you. You do not need to leave a virtual machine running or use screen. Please check that your application is accessible without signing in to Google before your meeting.

What to submit and when

You will need to submit your code for your application and a README file on Gradescope by Thursday November 19 at 5 p.m. ET. Just as for Parts 1 and 2, you should submit your project exactly once per team, rather than once per student. (Click on "Add Group Member" after one of you has submitted your project.) Here are the rest of the instructions for your electronic submission:

• Create a directory named proj1-3.
• Copy all the Python source code files into the proj1-3 directory, and include all the other files that are necessary for your program to run. Do not include .env, .venv/, proxy-key.json, or your database passwords.
• Tar and gzip the proj1-3 directory, to generate a single file proj1-3.tar.gz, which is the first file that you will submit. For this, from outside the proj1-3 directory, run tar -cvf proj1-3.tar proj1-3 and then run gzip proj1-3.tar.
• Submit file proj1-3.tar.gz on Gradescope.
• Submit a separate uncompressed README file on Gradescope with the following information:
  • The PostgreSQL account where your database on our server resides. (This should be the same database that you used for Part 2, but we need you to confirm that we should check that database.)
  • The HTTPS URL of your web application on Cloud Run. Once again, please do not delete or disable your service when you are ready to submit, so that the URL that you include with your project submission works.
  • A description of the parts of your original proposal in Part 1 that you implemented, the parts you did not (which hopefully is nothing or something very small), and possibly new features that were not included in the proposal and that you implemented anyway. If you did not implement some part of the proposal in Part 1, explain why.
  • Briefly describe two of the web pages that require (what you consider) the most interesting database operations in terms of what the pages are used for, how the page is related to the database operations (e.g., inputs on the page are used in such and such way to produce database operations that do such and such), and why you think they are interesting.
  • A careful description of what AI tools you have used for the project, if any, and how.

In summary, you need to submit on Gradescope exactly two files: (1) your proj1-3.tar.gz file with your code and (2) your uncompressed README file. You need to submit these two files by Thursday November 19 at 5 p.m. ET.

IMPORTANT NOTE: You can use grace late days as usual, but you need to make sure that you schedule your meeting with your project mentor so that you have submitted your files on Gradescope before the meeting. The grace late days will be computed with respect to the time of your submission on Gradescope.

Additionally, both teammates in each team will meet together with their project mentor on Friday November 20 or Monday November 23. Your project mentor will email you shortly to schedule a 15-minute meeting for those days. (If you haven't received an email from your project mentor by Monday November 9, please contact your mentor.) During the meeting with your project mentor, you will show your mentor your application using a regular web browser, by sharing your screen during the online meeting:

• You should have your application up and running so that you and your project mentor can access it over the web simply by typing a URL in a regular browser. Your project mentor will be running Chrome.
• Your project mentor should be able to interact with your application and access the functionality that you specified in Part 1, over the database that you created for Part 2.
• The project mentor might ask to look at your code during your meeting. Please be prepared to screen-share a window to show relevant code segments for your application.
• Your web interface does not need to be fancy. (See "Grading for Project 1, Part 3" below.) However, you should not force users to type SQL. The less your web site looks like it is interacting with a relational database, the better. At the very least, the user should be completely shielded from having to type anything resembling SQL. Most interactions should involve some sort of input values in addition to the user pressing a "submit" button. Whenever possible, input values should be specified using menus, radio buttons, checkboxes, scrollers, etc. Text input boxes may also be appropriate.
• Sophisticated error handling is not necessary; however your web site definitely should not "lock up" (i.e., crash or become blocked) regardless of how the user chooses to interact with it.
• Your database should contain (at least) the data that you entered for Part 2. You can, of course, add extra tuples to your tables if you want to make interaction with your application more interesting and revealing.
• Your grade will suffer considerably if your application is not running properly when you access it from your project mentor's machine. It is your responsibility to ensure that your application is up and running when you meet with your project mentor.
• You should have a number of example interactions prepared so that you can use your meeting time efficiently. The more you can impress your project mentor during the 15-minute meeting, the better your grade is likely to be, so come to the meeting prepared.

Policies on Usage of AI Tools for Project 1, Part 3

The use of generative AI tools (e.g., ChatGPT, Claude, Gemini) is fully allowed for Part 3, including for "vibe coding," but it is your responsibility that the final code for your project satisfies all of the conditions and constraints that are described in this project description, including not using ORM, avoiding SQL injection vulnerabilities, etc.

Grading for Project 1, Part 3

Your grade for Part 3 of Project 1 will be a function of how well your application (which should be up and running) matches your specification that you submitted as Part 1, of how well you have incorporated any feedback that your project mentor has given you, and of how well you have followed the guidelines above. Part of your grade will be a function of how well you avoid the SQL injection vulnerability that we discussed in class (see also above). Your grade will not be influenced by how fancy the web-based user interface to your application is. It is sufficient and perfectly fine for this interface to be plain and simple as long as it supports the functionality that you indicated earlier, following the guidelines above about not having to type SQL commands, not "locking up" on unexpected input, etc.