PaulieScanlon

// ./services/google-spreadsheet.ts

import { GoogleSpreadsheet } from 'google-spreadsheet';
import { JWT } from 'google-auth-library';

const serviceAccountAuth = new JWT({
  email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
  key: process.env.GOOGLE_PRIVATE_KEY.split(String.raw`\n`).join('\n'),
  scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

PaulieScanlon

npm install google-spreadsheet google-auth-library

PaulieScanlon

name: Backup Neon | paulie-dev (us-east-1)

on:
  schedule:
    # Runs at midnight ET (us-east-1)
    - cron: '0 0 * * *'
  workflow_dispatch:

jobs:
  db-backup:

PaulieScanlon

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::627917386332:role/paulie-dev-2023-github-action"
            },
            "Action": [
                "s3:ListBucket",

PaulieScanlon

import type { FunctionComponent } from 'react';
import type { AnalyticsType } from '../../schema';

type PickedProps = Pick<AnalyticsType, 'city' | 'country' | 'flag'>;

interface Props {
  analyticsData: PickedProps[];
}

const VisitorsList: FunctionComponent<Props> = ({ analyticsData }) => {

PaulieScanlon

<VisitorsList analyticsData={results} />

PaulieScanlon

const results = await db
  .select({
    city: analytics.city,
    country: analytics.country,
    flag: analytics.flag,
  })
  .from(analytics)
  .where(eq(analytics.country, 'US'))
  .orderBy(analytics.date)
  .limit(10);

PaulieScanlon

import { pgTable, serial, varchar, timestamp, decimal} from 'drizzle-orm/pg-core';

+ import { type InferSelectModel } from 'drizzle-orm';

export const analytics = pgTable('analytics', {
  id: serial('id').primaryKey(),
  date: timestamp('date').notNull(),
  slug: varchar('slug', { length: 255 }).notNull(),
  referrer: varchar('referrer', { length: 255 }),
  flag: varchar('flag', { length: 16 }),

PaulieScanlon

- import { pgTable, serial, varchar } from 'drizzle-orm/pg-core';
+ import { pgTable, serial, varchar, date } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  user_id: serial('user_id').primaryKey(),
  first_name: varchar('first_name', { length: 100 }),
  last_name: varchar('last_name', { length: 100 }),
  email: varchar('email', { length: 80 }).unique(),
  role: varchar('role', { length: 20 }),
+  date_of_birth: date('date_of_birth')

PaulieScanlon

.select({
  city: analytics.city,
  country: analytics.country,
  flag: analytics.flag,
  count: sql`COUNT(${reactions.id})`.as('count'),
})
.from(analytics)
.innerJoin(reactions, eq(analytics.slug, reactions.slug))
.where(and(eq(reactions.reaction, 'happy'), gte(analytics.date, new Date(new Date().getTime() - 30 * 24 * 60 * 60 * 1000))))
.groupBy(analytics.city, analytics.country, analytics.flag)