TU Wien Chor Dashboard

This is a prototype for an application for everyday tasks concerning life in the university choir of TU Wien.

Features

The main feature is a choir admin dashboard. It includes the following:

• Event overview (choir practice, concerts, etc.)
  • The calendar is automatically synced daily with the TU Wien Google calendar
  • If needed, users can also trigger sync from the UI
• Attendance management for events (e.g. to keep track of how often each choir member attends choir practice)
• Choir member management
  • Import members (name and voice group) from CSV
• 🚧 WIP: Choir repertoire management (list of songs, linked files, etc.)
• Stats page (choir members, attendances, members by voice group, etc.)
• Admin account managment
  • Admin accounts with email and password
  • Invite system for new admins: existing admin users can create invite links for new admins (custom account creation links with invite token, tied to specific email)

I plan to add public-facing sites for choir members as well. Currently, only a page for the current program is available. It includes a list of songs and links to the corresponding files (sheet music, lyrics, etc.).

Live demo with dummy data

Go to the this site and log in with test.admin@example.com and password asdfasdf.

Installing the project

1. Make sure Node and yarn are installed on the machine. On Ubuntu, this should be as simple as running sudo apt update && sudo apt install nodejs && sudo apt install yarn. Check online what the equivalent commands for Mac/Windows are. Note: Prisma apparently crashes on Debian systems when using Node 18 and PostgreSQL (prisma/prisma#10649). You can use the Node Version Manager to install another version of Node and switch to it for this project.
2. Run yarn. This will install all the Node modules used by this NextJS project.

Set up DB (postgres)

Local

1. Make sure postgres is installed and running (psql command works - this guide might help)
2. Run psql (or sudo -u postgres psql if that doesn't work) to login into the "postgres console" (not sure what proper term for it is)
3. In the postgres console, run the following SQL commands (replacing yourpass with a password of your choice):

   CREATE DATABASE choir_manager;
   CREATE USER choir_admin WITH PASSWORD 'yourpass';
   GRANT ALL PRIVILEGES ON DATABASE choir_manager TO choir_admin;
   

   This will create a choir_manager database and a database user choir_admin that essentially has "super-user" access to the database. NOTE: I am not entirely sure about the security implications of not having an encrypted password. I guess it should be fine as the user is only "reachable" locally anyway?
4. Now, the database should be accessible via the connection string postgresql://choir_admin:yourpass@localhost/choir_manager, replacing yourpass with the password you chose earlier. More details on how connection URIs work can be found here.

Remote

I currently I use Supabase's postgres cloud DB. Check out the Supabase docs or other sources on the web for details on how to set things up.

Setup Prisma

Prisma is an ORM for Node.js and Typescript. It is used to interact with the database from the application code. It also features a typesafe schema, which is used to generate a client that maps entities to DB tables and relations and can be used to interact with the database.

To set up Prisma, follow these steps:

1. Create a .env file in the root folder of this project, containing a single line:

   DATABASE_URL=<connection-string>
   NEXTAUTH_URL=http://localhost:3000
   NEXTAUTH_SECRET=<some-secret-string>
   

   where<connection-string> is the connection DB connection string from the previous step. NEXTAUTH_URL and NEXTAUTH_SECRET are needed by nextauth (for details click here). Replace <some-secret-string> with a random string generated by running openssl rand -base64 32 on the command line.
2. Run npx prisma db push. This will use Prisma, an ORM for Node.js and Typescript, to sync the created database with the database schema defined in prisma/schema.prisma. As the database will be empty before we run this command, it essentially just creates all the tables mentioned in the schema. Then, run npx prisma generate to create the Prisma client, which is used to interact with the database from the application code. The client will then have access to all the entities of the schema (mapped from DB tables and columns).
3. (Optional) Run npx prisma studio. This will launch Prisma Studio (Prisma's "database explorer") on your machine and make the GUI available via your browser (per default on localhost:5555). You can use it to check out the models used in the database - no records will exist at this point.

Setup drizzle

I don't like the Prisma client interface. I find the syntax quite hard to learn (especially when querying/updating/deleting relations) and too different from regular SQL. Also, Prisma does NOT use SQL joins when combining data from different tables. Tbh, this isn't that much of a problem for this project. However, I still wanted to try something different. Hence, I went with drizzle, a lightweight ORM that uses SQL-like syntax and allows for joins. It's still rapidly evolving but I like the API a lot. It is used by the newer parts of this app. Some stuff, like the authentication logic builds on Prisma though, so migrating away from Prisma completely is not an option atm.

Similar to Prisma, drizzle also features a typesafe schema. However, instead of generating a client from the CLI, we generate a schema. Tables, relations etc. from this schema can then be imported in any file that interacts with the DB. The schema is generated from the DB schema using drizzle-kit's introspect command.

To configure drizzle, create a file called drizzle.config.ts in the root folder of this project (check out the drizzle.config.example.ts file for reference). Then you should be able to run npx drizzle-kit introspect:pg to generate the drizzle client schema file.

Add other required secrets and API keys

In the env file mentioned above, you also need to add other environment variables:

INVITE_TOKEN_SECRET=<another-secret-string>

The INVITE_TOKEN_SECRET is needed for generating invite tokens for new application users (admin accounts). Replace <another-secret-string> with another random string generated by running openssl rand -base64 32.

GOOGLE_API_KEY=<your-api-key>

The GOOGLE_API_KEY is needed to allow the applcation to use the Google Calendar API for syncing the choir practice events in the database with the choir practice events of the TU Wien Chor public calendar. You need to generate it in the Google Developer console. Once you have it, replace <your-api-key> with the key you obtained. For details on how to obtain your key, see here.

Launch the application

Now that we've installed the dependencies and the database stuff, environmnent variables etc. are set up, the application is ready. Type yarn dev to start it.

Then, navigate to localhost:3000/register to create a user with a name, email and password of your choice. The credentials will be stored in the database (passwords are encrypted/hashed using bcrypt, so they should be quite safe, even if the database were compromised).

After registering, you should be logged in and see an admin page. All other admin users that try to register will need an invitation link. To generate those, you can click on 'Invites'. There, you will see an overview page for all invite links that have been generated (there will be none, initially). To generate a new one, just click the button on that page. You can send the generated link to someone you would like to add as a user.

Add data

The application will look weird in its initial state, as neither events nor songs or choir members will exist yet.

You can add choir members from the 'Mitglieder' page in the admin dashboard. To get started with some fake choir members quickly, upload the file located in mock-data/fake_choir_members.csv by dragging it into the designated area and clicking the 'Bestätigen' button. This will add all the choir members from the file to the database.

Similarly, songs can be imported from a text file on the 'Lieder' page. However, this time a simple file with one song title per line is expected. You can find an example file in mock-data/songs.txt.

Events are synced from the choirs' public Google calendar. Go to the 'Termine' page and click the button that says 'Mit Google Kalender synchronisieren'. This will fetch all events from the calendar and add them to the database. Note that this will only work if you have set up the GOOGLE_API_KEY environment variable as described above.

Other developer notes

Schema/Model changes

The Prisma schema file prisma/schema.prisma is the source of truth for the database schema (and hence the whole 'data model' of this application). It is used to generate the Prisma client and sync the database with the schema. The drizzle DB client also relies on it indirectly: its schema is generated from the DB schema using drizzle-kit's introspect command. So, whenever you change the Prisma schema, you need to

• run npx prisma db push to sync the "push" the changes in the Prisma schema to the DB (updating its schema),
• run npx prisma generate to update the Prisma client, and
• run npx drizzle-kit introspect:pg to update the drizzle client schema from the updated DB schema.

Conveniently, there's a script in package.json that does all of that: update-schema-and-clients. So, whenever you change the Prisma schema, just run yarn update-schema-and-clients and you should be good to go.

Note: because I had an issue with the infer mode for timestamp columns being set to mode: 'string' in the introspection script (see this GitHub issue), I had to manually remove all instances of that string from the output schema file with sed -i '' \"s/mode: 'string'//g\" drizzle/schema.ts (to make replacement work on Mac - this might not work on Linux, and surely doesn't on Windows - find an equivalent if needed).

Database ER Diagram (as of 2023-08-05)

This is an ER Diagram I created from the public DB schema of the database (which is the schema used by the core application; Prisma, Supabase etc. create other schemas in the DB). It was created with DBeaver. Check this StackExchange discussion for details on the notation/symbols used in the diagram.

TODOs and nice-to-haves I will probably never find the time for

This project is very much a playground for me to learn how to make things happen. I was still figuring things out as I added features. As a consequence of this, some of this code is a bit of a sh*tshow. Here I keep track of and prioritize stuff, so that I could fix it if I were to find the time to work on this.

• More control about admin accounts (changing passwords, emails, names, deleting accounts, etc.)
• cool new tech
  • use drizzle instead of Prisma for DB access in new parts of the app
  • use tRPC routers instead of Next.js API routes in new parts of the app
  • use new Next.js 13 app directory instead of pages directory
  • full transition from pages/api to tRPC routers
  • transition from Prisma to drizzle
• code smells and changes required to reduce them
  • unify data fetching (some routes use getStaticProps, some getServerSideProps, some fetch on client side - even that is done with different approaches lol)
  • refactor ugly backend stats collection code to just use SQL lol
  • find a simpler and more bullet-proof way to handle authentication (or rather, actually understand what nextauth does lol)
• UI
  • redesign (really not that beautiful)
  • replace Material UI with more lightweight custom components