Add Agency, Managing Code, and Project Drizzle schemas to Zoning API

[TylerMatteo]

Implement the following tables as Drizzle schemas in Zoning API per @TangoYankee's ERD seen here. You can also find a rough implementation of the tables in raw sql here for reference.

• [x] Define schemas for the project, managing_code, and agency tables as drizzle schemas in /src/schema. Note that the
• [x] Table columns match names and data types from ERD. Note that the ERD defines snake_case columns names as they should appear in the database. The properties on the typescript schemas should have the equivalent camelCase name. (example of how to have custom name for composite key available here)
• [x] Table foreign/primary key relationships are defined as per the ERD. Note that the project table has a composite primary key made up of id and managing_agency
• [x] For now, do not implement geospatial columns - these are the ones that start with mercator or li_ft in the ERD
• [x] Define project_category enum and use it in project table schema

[TylerMatteo]

@TangoYankee I wasn't exactly sure how best to handle generating the drizzle migrations so I left it out of the task list for now. Should those be in the PR alongside defining the Drizzle schemas themselves?

[TangoYankee]

TangoYankee I wasn't exactly sure how best to handle generating the drizzle migrations so I left it out of the task list for now. Should those be in the PR alongside defining the Drizzle schemas themselves?

Let's get all the schemas written and then create the migration as its own ticket. All of the tables need to work together. Waiting until all the schemas are written means that we can ensure we have a single migration that gets the schemas correct within that migration.

[TangoYankee]

Resource for composite primary keys in Drizzle

[TangoYankee]

@TylerMatteo I would like to make the project_category enum an explicit acceptance criteria.

I would like to also highlight that the difference in naming style between the javascript variable and the database column. The database columns use snake_case and the javascript variables use camelCase. In this example authorId is the camelCase variable and author_id is the snake_case column name

[TylerMatteo]

@TangoYankee I updated the AC to note the enum and casing. I'll have someone get started on the schemas, you and I can discuss migrations when you're back.

[TangoYankee]

@bmarchena and anyone working on tickets #212 #213 #214 or #215

If you're referencing existing schemas in the zoning-api, you may notice that tax-lots also have a taxLotRelations that makes use of a relations function. Adding relations functions is out of scope for this series of tickets. This has to do with a subtle difference between "database relationships" and "drizzle relationships".

"Database relationships" refer to schema updates that are truly encoded into the database tables. The indicator of this relationship is the ability to connect to the database and actually see one column reference the key of another column. "database relationships" are defined in drizzle using the Foreign key constraint. The keyword to implement a foreign key is "references". This series of tickets is limited in scope to only defining these "database relationships". So what are "drizzle relationships"?

Drizzle has a couple different approaches to building SQL queries. One of these approaches is literally called "Queries". This is frustratingly confusing because it clashes with the general idea of writing a sql query. Let's call the drizzle-specific approach "drizzle queries" and the more general idea of querying "sql queries". "Drizzle queries" provide an abstract syntax for creating "sql queries". This abstract syntax includes functionality to join data across tables. However, before using this abstract syntax for the "drizzle queries", drizzle needs to be told about these relationships, independent of the "database relationships". This is the job of the "relations" function. So, the "relations" function can give us access to more complex "drizzle queries". However, it is creating "drizzle relationships" that are independent of the "database relationships". As we only care about the "database relationships" for these tickets, it is out of scope.

Addendum: After writing this summary, I found drizzle's explanation of the difference. It's under "queries -> foreign keys". In this explanation, they're using "foreign keys" for what I called "database relationships" and "relations" for "drizzle relationships".

[bmarchena]

@bmarchena and anyone working on tickets #211 #212 #213 #214 or #215

If you're referencing existing schemas in the zoning-api, you may notice that tax-lots also have a taxLotRelations that makes use of a relations function. Adding relations functions is out of scope for this series of tickets. This has to do with a subtle difference between "database relationships" and "drizzle relationships".

"Database relationships" refer to schema updates that are truly encoded into the database tables. The indicator of this relationship is the ability to connect to the database and actually see one column reference the key of another column. "database relationships" are defined in drizzle using the Foreign key constraint. The keyword to implement a foreign key is "references". This series of tickets is limited in scope to only defining these "database relationships". So what are "drizzle relationships"?

Drizzle has a couple different approaches to building SQL queries. One of these approaches is literally called "Queries". This is frustratingly confusing because it clashes with the general idea of writing a sql query. Let's call the drizzle-specific approach "drizzle queries" and the more general idea of querying "sql queries". "Drizzle queries" provide an abstract syntax for creating "sql queries". This abstract syntax includes functionality to join data across tables. However, before using this abstract syntax for the "drizzle queries", drizzle needs to be told about these relationships, independent of the "database relationships". This is the job of the "relations" function. So, the "relations" function can give us access to more complex "drizzle queries". However, it is creating "drizzle relationships" that are independent of the "database relationships". As we only care about the "database relationships" for these tickets, it is out of scope.

Addendum: After writing this summary, I found drizzle's explanation of the difference. It's under "queries -> foreign keys". In this explanation, they're using "foreign keys" for what I called "database relationships" and "relations" for "drizzle relationships".

This was a really helpful explanation, thanks @TangoYankee

[TangoYankee]

Regarding composite primary keys, they are by-default "unnamed" in sql and drizzle. We are going to stick with this unnamed approach. The documentation for drizzle composite primary keys shows both the unnamed and named approach. Below is a snippet that simplifies the example to only show only how to do unnamed keys. I'm not sure whether "pk" is a special name or if we could technically call it anything. But for this first pass, we'll stick with the "pk" convention.

import { serial, text, integer, primaryKey, pgTable } from "drizzle-orm/pg-core";

export const booksToAuthors = pgTable("books_to_authors", {
  authorId: integer("author_id"),
  bookId: integer("book_id"),
}, (table) => {
  return {
    pk: primaryKey({ columns: [table.bookId, table.authorId] }),
  };
});