Postgres Based timesheet application

How to use

Initial startup & usage

make docker-start  # just so that it doesnt muck with any other postgresql's
make createdb   # creates the "timesheet" repo, *"public" may or may not be deleted*
PROJECT=work make schema # creates the "work" timesheet schema
PROJECT=personal make schema  # createst the "personal" timesheet schema
make clockin REASON="Go to job" PROJECT="work"
make clockin REASON="Do some personal stuff" PROJECT="personal" # Won't work! you're still clocked into work!
make clockout REASON="go home" PROJECT="work"
make clockin REASON="Go shopping" PROJECT="personal"  # Good, don't mulitask, it's bad for you.

What was done:

make today PROJECT=work # what did I work on today
make all-tasks PROJECT=personal # show me ALL my personal tasks done...

Backing up & restore Email config:

cat <<EOF > .credentials.yml
email:
  username: some.email@gmail.com
  password: password
  recipients:
    - who.getsthis@email.com
    - who.else@eeeemail.com
EOF

Actual backup and restoration

make backup  # creates a file and tries to mail it to you...
make restore BACKUP=timesheet_$(sometimestamp).sql

debug

make psql  # logs you into the dockerized postgres. Our examples should have three schemas "work", "personal" and "common"
make resetdb # WIPE EVERYTHING! destroy everything and rebuild database (no schemas)

Goals

• learn postgres, pgsql
• learn Make
• have something useful from the experience
• learn Python3 asyncio
• be able to write a story about what happened on what day..

Features

• commandline tool to log timesheets
• backup functionality (email for now, until we can figure out staging a proper replication strategy)
• simple cli based
• export to xls/csv?
• restore functionality (needs to be fixed)

TODO-list

• today total times
• Selective note addition (notes to any periods, post mortem)
• RESTful API
• Multiple notes to a period.
• Supervisor job to monitor system state?
• Log when sleep, wake ? OSX version:

  #!/usr/bin/env python
  
  from AppKit import NSWorkspace, NSWorkspaceWillSleepNotification, \
                     NSWorkspaceDidWakeNotification, NSObject, \
                     NSApplication, NSLog
  
  class App(NSObject):
  
      def applicationDidFinishLaunching_(self, notification):
          workspace          = NSWorkspace.sharedWorkspace()
          notificationCenter = workspace.notificationCenter()
          notificationCenter.addObserver_selector_name_object_(
              self,
              self.receiveSleepNotification_,
              NSWorkspaceWillSleepNotification,
              None
          )
          notificationCenter.addObserver_selector_name_object_(
              self,
              self.receiveWakeNotification_,
              NSWorkspaceDidWakeNotification,
              None
          )
  
      def receiveSleepNotification_(self, notification):
          NSLog("receiveSleepNotification: %@", notification)
  
      def receiveWakeNotification_(self, notification):
          NSLog("receiveWakeNotification: %@", notification)
  
  if __name__ == '__main__':
      sharedapp = NSApplication.sharedApplication()
      app       = App.alloc().init()
      sharedapp.setDelegate_(app)
      sharedapp.run()
  
      

NOTES:

• Schemas vs dbs vs vs .. Great link on Stack overflow So let’s assume I want to do this per user.. then a schema per user idea would be great, however, this would mean that I can’t nest schemas. And if I’m already using schemas then it’s a design flaw. Perhaps I should revisit the idea of common, work, private schemas.
• The schema… ideas: Perhaps the period table should be common among projects? You can’t be working on more than one thing at a time.(#OPINIONATED!)
• some async code for reference

import aiopg
import asyncio
from aiohttp import web

loop = asyncio.get_event_loop()
app = web.Application(loop=loop)


async def get_today():
    conn = await aiopg.connect(database='timesheet',
                               host='127.0.0.1')
    cur = await conn.cursor()
    await cur.execute("SELECT * FROM timesheet.today")
    retval = []
    async for elem in cur:
        return elem

async def index(request):
    values = await get_today()
    for value in values:
        print("..",values)
    return web.Response(text="Hello there")


app.router.add_get("/", index)
web.run_app(app, host='127.0.0.1', port=8088)

endpoints

• POST /timesheet/new # create a new schema ?
• GET /timesheet/{id|name}

projects?

/project/1/period or /project/takealot/period and /project/hackytime/period Apparently you get template databases but not template schemas. Template schemas are not formalised so create a schema, then apply tables to that schema. Common sql tables file?