Project: Freebie Finder

Freebie Finder is an app that allows members of a local community swap items for free. Similar systems in real life include BuyNothing, parts of Craigslist, and parts of Nextdoor. Freebie Finder is much more streamlined than systems like Nextdoor, however, in that it only focuses on specific items.

A neighbor will be able create posts of something that they either want or are willing to give away. These posts are shown to various nearby communities, and neighbors can communicate in the system to make logistical arrangements. This being the internet, the tool will also include moderation features.

Note: when we say “neighbor”, we mean it in the general sense as a member of the community. People don’t have to live adjacent to each other to be neighbors.

DB Design & Style Expectations

Be sure to check out our expectations page. Be sure to consult these when you are trying to think of helpful feedback.

DB0: Setup

DB0 is the same for everyone, regardless of project.

For setting up in the lab or on your own machine, follow our DB Project Setup Instructions.

Grading DB0

• 10pts. All steps implemented
• 10pts. CI fully passes

Grading notes:

• Make sure you tagged the commit as db0 so we can find it easily.
• We grade GitLab only, unless an instructor or TA has given explicit written instructions that you can bypass the CI (which is rare)

DB1: Initial Schema, Test Data Set

In this iteration, please name your topic branch db1-dev. Once you have merged this into the master branch, tag the version that you consider to be your submission with db1.

The main purposes of this twofold:

• Get your initial schema going
• Set up your test data for unit testing

Build a table in your database and populate it with some test data. We will be building this database schema incrementally, so let’s just start with two or three tables.

This iteration you will build some initial tables that relate to each other. Don’t worry about storing all the columns you can think of - just get the general concept of the table and its foreign/primary keys.

Generally speaking, you will not call SQL directly from your unittest code. Instead, you will have APIs that will do the heavy lifting for SQL. Your APIs will be methods in src/ that will be called by your unittests. The APIs will make the calls to SQL (e.g. using CREATE and SELECT etc.). Keep your test data loading separate from the APIs in src/ to keep test code and production code distinct. See your project domain for specifics.

We will give you test cases and you will need to adapt them into Python unit tests. You also must add 3 additional tests (i.e. test_* methods). You are welcome (encouraged!) to add to your test dataset too.

Lesson: Learn the value of deleting out of date code. Delete any trace of example_table from your code. We’re done with the example setup, so adapt your code accordingly. That code will live on in your repository history anyway. Don’t comment it out like a packrat. Delete. The sooner you get used to the idea of revising code instead of continually adding to it, the better your software will be on so many levels.

Grading DB1

By lab day:

• 5 pts. Set up merge request by lab day
• 10pts. Enough functionality finished such that it can be thoroughly reviewed

By submission day:

• 10pts. Directions followed. e.g. Example removed, git branch, merge, tag, etc.
• 10pts. Provided useful feedback to others (merge request feedback)
• 5pts. Responded to feedback on your own project
• 15pts. Test cases implemented
• 10pts. CI Succesfully runs
• 15pts. Overall spirit of the feature implemented

Feature Overview

The main stakeholders in this system are a community of neighbors who want to exchange items. A smaller group of neighbors will have moderation capabilities.

This iteration, we want our database API to cover the following features:

• A neighbor can only be part of one community
• A neighbor can post an item they want to give away to others in their community
• A neighbor can post a wish for an item they want someone else to give away in their community
• Neighbors can get a feed of items and wishes from their own community
• Expiration - a post or wish can have an optional expiration date. Expired posts do not show up in the feed. They are not deleted from the database.
• Neighbors can see their expired posts
• Moderators will see expired posts from others in their feeds

Key decisions

• How many API methods do you need? Flexibility and simplicity always trade off!
• How will you handle the concept of a community?
• How will you handle expirations? Your API should NOT require a batch process to periodically update records.
• How will you do repeatable tests when dealing with dates?
• Should posts and wishes be two tables, or one?

Test Data Seeds

You will need to maintain a set of test data to seed your database before every test. This can take multiple forms, such as:

• An SQL file with many INSERT statements
• A hardcoded Python file calling SQL commands

How you do this is up to you. You are welcome to add more data than what we ask for any tests you write.

For DB1, you need the following data to be pre-loaded for your tests:

• Neighbors Holden, Naomi, and Alex are members of a community called Rocinante.
• Another neighbor named Miller is a member of a community called Ceres.
• Holden and Miller are moderators
• Alex posts a wish for a spaceship, never expiring
• Miller posts a wish for whiskey, never expiring
• Holden posts an item called “protomolecule”, expiring on January 25, 2022
• Naomi posts an item called “water”, expiring on January 25, 2022

Test Case Sketches

• Naomi checks her feed on January 2nd, 2022, and sees:
  • Naomi’s post for free water
  • Alex’s wish for a spaceship
  • Holden’s post for the protomolecule
• Naomi checks her feed on January 26th, 2022, and sees:
  • Alex’s wish for a spaceship
  • (Holden’s post for the protomolecule is NOT here because it expired)
  • (Naomi’s post for free water IS here because it it’s hers, but expired)
• Holden checks his feed on January 2nd, 2022, and sees:
  • Naomi’s post for free water
  • Alex’s wish for a spaceship
  • Holden’s post for the protomolecule
• Holden checks his feed on January 26th, 2022, and sees:
  • Alex’s wish for a spaceship
  • Holden’s post for the protomolecule is here (it’s expired, but he’s a moderator)
  • Naomi’s post for free water is here (it’s expired, but he’s a moderator)
• Miller checks his feed and only sees his own post.

Note: make sure your tests are actually making calls to your API, not just directly to the database. Data will be seeded directly, but your tests will be testing the API. For example, there should be an API method for “checks feed…” and the test calls that.

Not Necessary (yet)

For this iteration, you do NOT need to implement:

• An API call for creating new accounts, or creating new posts. We can assume that the data will be in our data set for this iteration.
• Handling data related to location (e.g. GPS)
• Handling the concept of communities being near other communities

You may need to do these in the future, but for now just test against your pre-seeded test data.

DB2: CRUD & Design iteration

Please call your topic branch db2-dev and your final tag db2.

Our goal here is to add basic CRUD operations to our persistence API. In this iteration, you will be:

• Adding some more tables (if needed)
• Adding columns/ changing columns in existing tables (if needed)
• Implementing some baseline methods for doing CRUD operations on your data
• Add some useful search/ query methods

Remember, CRUD operations usually refer to a specific row. So need to ensure you identify the specific row uniquely.

Keep your old unit tests and update them accordingly.

We do not need CRUD for everything. In “real life”, you can usually come up with a reason to set up CRUD for every entity in your schema. That would be too repetitive for us. Focus on what we are asking for.

We also need to begin providing reference documentation for how to use these DB methods. We would like you to use the Python Docstring methodology for documenting your API methods. For each method in your API, you must include:

• A useful, single sentence about what the method does.
• Name of each argument, what it means, and any default value
• Access control assumptions you are making
• What is returned? (e.g. python dict? psycopg2 result set?)

Grading DB2

By lab day:

• Set up merge request by lab day (5 pts)
• Enough functionality finished such that it can be thoroughly reviewed (5 pts)

By submission day:

• Directions followed. e.g. Example removed, git branch/ tag, etc. (5 pts)
• Provided useful feedback to others (merge request feedback) (5 pts)
• Responded to feedback on your own project (merge request) (5 pts)
• Requirements implemented (20 pts)
• CI Succesfully runs (5 pts)
• Documentation complete (10 pts)
• Test cases implemented and pass (20 pts)

DB2 Freebie Finder

In this iteration, you will add API methods that do the following:

• Create new accounts for neighbors
• Elevate a regular neighbor to moderator. Only other moderators can do this.
• A neighbor should be able to change what community they are in
• Neighbors should be able to modify profile information, including:
  • Display name
  • Birthday
• Create new posts and wishes.
• Communities need to have some information about them as well:
  • A US zip code
  • Text describing community rules
  • Display name for the community
• Moderators should be able to delete posts or wishes, and any messages associated with them.
• Moderators should be able to delete messages on any post or wish
• Posts and wishes should be able to have a conversation around them, so neighbors can post messages and communicate on that item. Messages should be returned in chronological order
• Posts and wishes should be able to be marked as “fulfilled”. Fulfilled posts and wishes will show up in the feed for 24 hours after it was fulfilled.

Key Questions

• Deactivate or delete accounts?
• How will you model the conversation on a post? And deleting posts with conversations?

A note about timestamps and APIs. In this project, we ask that your APIs allow for setting timestamps, which makes our testing setup easier. In practice, a more common approach is to have the database be the source of generating timestamps. For example, if we send a new message, then the database would set the current timestamp using the CURRENT_DATE (see Postgresql docs) or something similar. To make your tests repeatable, web engineers have library calls that can make the system “pretend” the current time is what we expect in our test data. An example of this is the travel_to method in Ruby on Rails, with a good explanation here. For us, rather than bringing in more libraries, you can just provide the timestamp in your API and assume that the front-end developer will get the proper times.

Test Case Sketches

Adapt these test case sketches into your tests, creating new test data where appropriate:

• Using the API, create a new account called Bobbie, and add it to the Rocinante
• Holden elevates Bobbie to moderator
• Bobbie can modify her profile information via API
• Bobbie fulfills a wish:
  • Creates a wish called Goliath
  • Alex makes comments on the wish
  • Bobbie fulfills the wish
  • The wish shows up in her feed 24 hours after the fulfillment
  • The wish does not show up in her feed 36 hours after the fulfillment
• Holden deletes the post about the protomolecule
• Holden posts an item called Canterbury
  • Amos comments on it
  • Miller deletes the Canterbury post, and Amos’ post is also deleted too.

Add three additional tests to this.

DB3: Expanding Your Schema

Please call your topic branch db3-dev and your final tag db3. All lowercase, hyphenated.

We have the core of our system down and our development infrastructure set up. Now it’s time to accelerate on features.

First, it’s time for a DTR: Define The Relationships. After reviewing these features, we recommend you sit down and determine your relationships on a piece of paper. Some people like to use the notation from Entity-Relationship Diagrams - we will not require this of you. Boxes for tables and lines with arrows for foreign keys would suffice. The minimum information you must show is: Each table; the fields in the table(s); the PK <-> FK relationships. Your “DTR” can be a picture of your hand-drawn diagram (but make sure it’s legible), or use any drawing tool to create a file. Make sure the name starts with DTR. If it’s a file, convert it to PDF, so we can read it. Add your document to your repo, and make sure it’s pushed to the repo, in the root of the directory.

Key decision for everyone. Continually ask yourself: “Should I do this in SQL or in Python?” Most things can be done in either. (Heck you can do inner joins in Python by doing nested loops, but… blech… don’t do that.) If your answer is “I know how to do it in Python but not SQL” - that’s a bad answer. If your answer is about what is simpler, more readable, maintainable, and performant - that’s a good answer. This project is a database API project, not just an SQL project.

Keep Your Old Tests Passing! These new features might involve revising past features. You must keep your old test cases running, but you may need to adapt them in spirit to these new features. It’s okay if you need to change your old test cases, as long as the spirit of the test remains.

Good luck! This might be a tough one. Don’t be afraid to scrap your schema design ideas when they don’t work. That’s why we do branches, unit tests, and merge requests.

Grading DB3

By lab day:

• Set up merge request by lab day (5 pts)
• Enough functionality finished such that it can be thoroughly reviewed (5 pts)

By submission day:

• Directions followed. e.g.git branch/ tag, etc. (5 pts)
• Provided useful feedback to others (merge request feedback) (5 pts)
• Responded to feedback on your own project (merge request) (5 pts)
• Requirements implemented (20 pts)
• CI Succesfully runs (5 pts)
• Documentation/ DTR complete (10 pts)
• Test cases implemented and pass (20 pts)

New Features

These features likely involve revising your existing implementation.

• Nearby communities. Some communities can be marked as “near” each other. We don’t need to model distance or use geographical information. Two communities being “near” each other is not necessarily transitive (see test cases for examples). .
• Show posts from nearby communities. When listing from the feed, we’d like the option to be able to view items that are near your community.
• Nearby moderation. Only moderators can manage if two communities are near each other.
• Draft items. Neighbors can save their wishes or items to the system but it won’t show up in others’ feeds unless the author of the post says so
• Schedule items. Neighbors can save items (not wishes) to the system but it won’t show up in others’ feeds until a specified time. This needs to be accomplished without constantly polling the system on a scheduled job.
• Mute neighbor. A neighbor should be able to mute another neighbor so they don’t see items or wishes posted from that neighbor.

Test Data

• Two communities, Earth and Luna are considered near each other.
• Crisjen is a member of Earth. She is a moderator.
• Jules-Pierre is a member of Luna.

Test Case Sketches

• Crisjen posts a wish for tea, and Jules-Pierre sees that wish.
• Jules-Pierre makes a draft of an item called the Razorback.
  • Crisjen does not see it.
  • Then, he releases that item
  • Crisjen now can sees it
• Jules-Pierre schedules a post for Guanshiyin to to post in a day
  • Crisjen does not see it
  • Then, once the time has elapsed, Crisjen can see it.
• Crisjen mutes Jules-Pierre, then does not see any of his posts
• Crisjen cannot see any items from Bobbie
  • Then, Crisjen makes Earth near to Rocinante
  • Then, Crisjen can see Bobbie’s posts

DB4: Analytics

Grading DB4

Please call your topic branch db4-dev and your final tag db4. All lowercase, hyphenated.
By lab day:

• Set up merge request by lab day (5 pts)
• Enough functionality finished such that it can be thoroughly reviewed (5 pts)

By submission day:

• Directions followed. e.g. Example removed, git branch/ tag, etc. (5 pts)
• Provided useful feedback to others (merge request feedback) (5 pts)
• Responded to feedback on your own project (merge request) (5 pts)
• Requirements implemented (20 pts)
• CI Succesfully runs (5 pts)
• Documentation complete (10 pts)
• Test cases implemented and pass (20 pts)

New Features

• In case you haven’t done so already, add the ability to fulfill a wish or post by marking it as done. A fulfilled wish or post should not show up in anyone’s feed.
• Community Dashboard. We need to report how each community is doing. Provide an API method that gives a breakdown of each community, available to everybody. For each community, return the following:
  • How many posts
  • How many wishes
  • Number of community members
  • Number of nearby communities
  • Wish to Post ratio. If there are no posts then return null, tip: use an CASE/WHEN in your select clause to handle the divide-by-zero
  • Age of community in years (i.e. time elapsed since their first post)
• Moderation Dashboard. We need to report the health of the community to its moderators. For each muted user in a community, provide an API method that will give the following:
  • How many people have muted them (also: the method should return results ranked by this count, descending)
  • Who has muted them, in a comma-separated string of their usernames.
  • Wish to Post ratio. If there are no posts then return null, tip: use an CASE/WHEN in your select clause to handle the divide-by-zero case
  • Number of unfulfilled posts
• Future Plan. Create a file called FUTURE.md in the root of your repository. Using Markdown syntax, provide written answers to the following:
  • In the future, say we wanted to add the ability to attach a reputation system so we can track who is reliable and who isn’t.
    • What tables need changing and/or adding?
    • What API methods would you provide?
    • How might existing API methods change?
  • In the future, say we wanted to add the ability to provide categorization of wishes and items, like a set of user-defined tags or categories:
    • What tables need changing and/or adding?
    • What API methods would you provide?
    • How might existing API methods change?

Test Case Sketches

Your test data has likely diverged from ours at this point, so consider this more of an example than a copy-and-paste test case.

The format of the community dashboard would look something like this:

The format for moderation dashboard would look something like this: