Knowledge Base: Database Calendar Suite v4.0 (Phoenix Release) 1.0 Introduction & Philosophy

The Database Calendar Suite is a locally-hosted, single-user web application architected for the sophisticated management and auditing of multi-modal prompt data. The Phoenix Release (v4.0) represents a fundamental evolution from a simple tool into a holistic data management ecosystem.

Core Philosophy (v4.0):

Hierarchical Storage: Data is now organized in a structured, hierarchical folder system within Google Drive (Root/Type/YYYY-MM/), enabling better organization and scalability.

Comprehensive Logging: Every significant action (upload, delete, error) is now immutably logged to a dedicated Google Sheet, creating a permanent, auditable history of all operations.

Dual-View Interface: The UI offers both a detailed Month View for daily operations and a high-level Year View for at-a-glance analysis of data density.

Intelligent & Resilient Workflows: The application is hardened to handle real-world data, including multi-file uploads and CSVs with internal commas, while providing advanced features like prompt distribution.

Robust Administration: A dedicated settings panel provides high-level administrative controls, including a "nuke" option for complete data removal and a secure logout function.

2.0 High-Level Architecture

The Phoenix Release introduces Google Sheets as a second, critical data endpoint for logging, and a more complex, hierarchical structure in Google Drive.

graph TD

subgraph "Frontend (Client-Side)"
    A[User's Browser]
    D{JS State Cache};
    A -- Reads/Writes --> D;
end

subgraph "Backend (Server-Side)"
    B{Python Flask Server};
end

subgraph "Google Cloud Services"
    C[Google Drive API];
    F[Google Sheets API];
    G[Hierarchical Folders (CSVs)];
    H[Log Sheet];
end

A -- HTTP API Calls --> B;
B -- Calls --> C;
B -- Calls --> F;
C -- Manages --> G;
F -- Manages --> H;

Frontend: The client-side application remains a lean vanilla JS/HTML/CSS build but now includes a more sophisticated state management system ("Lightning Mode") for caching calendar data.

Backend: The Flask server's responsibilities have grown. It now uses a request-level context (flask.g) to efficiently manage API service objects and acts as an orchestration layer between Google Drive and Google Sheets.

Data Storage:

Google Drive: Used for primary data storage in a Root/Type/YYYY-MM/ structure.

Google Sheets: A single spreadsheet acts as an immutable log for all significant application events.

3.0 Setup & Installation Guide 3.1 Prerequisites

Python 3.x, pip, a Google Account, VS Code.

3.2 Google Cloud API & Credentials Setup (Crucial Update)

The application now requires two APIs to be enabled.

Navigate to the Google Cloud Console.

Create a New Project.

Go to APIs & Services > Library.

Search for and Enable the Google Drive API.

Search for and Enable the Google Sheets API.

Go to APIs & Services > OAuth consent screen.

Select External.

Scopes: Add both of the following scopes:

.../auth/drive.file

.../auth/spreadsheets

Test Users: Add your Google email address.

Go to APIs & Services > Credentials.

Create an OAuth client ID for a Desktop app.

Download the JSON, rename it to credentials.json, and place it in the project root.

Edit the Client ID and under "Authorized redirect URIs," add http://localhost:8080/.

3.3 Application Setup

Folder Structure: Set up the project folder as detailed in section 4.0.

Virtual Environment:

Generated bash python3 -m venv venv source venv/bin/activate IGNORE_WHEN_COPYING_START content_copy download Use code with caution. Bash IGNORE_WHEN_COPYING_END

Install Dependencies:

Ensure your requirements.txt file is populated as per section 4.2.

Run: pip install -r requirements.txt

3.4 First Run

Run the server: python app.py

Navigate to http://127.0.0.1:5001.

The authentication flow will now request permissions for both Google Drive and Google Sheets. You must approve both.

Upon success, token.json is created, and the application is ready.

4.0 Project Structure & File Index

The v4.0 structure includes new assets like a favicon.

/database-calendar-app | |-- venv/ |-- app.py # Core backend server, API logic, Google orchestration. |-- credentials.json |-- token.json |-- requirements.txt | |-- /templates | |-- index.html # Main HTML skeleton with all UI elements & modals. | |-- /static |-- style.css # All CSS for layout, theming, and aesthetics. |-- script.js # All client-side logic, state caching, and interactivity. |-- favicon.ico # Application icon for the browser tab. |-- /sounds |-- success.mp3 # Auditory feedback for successful uploads.

4.1 app.py

The core of the backend. It now uses a Config class for settings, flask.g for efficient connection handling, and contains functions for managing a hierarchical folder structure and logging to Google Sheets.

4.2 requirements.txt

This file has a new dependency for Google Sheets integration.

Generated code Flask pandas google-api-python-client google-auth-httplib2 google-auth-oauthlib numpy gspread IGNORE_WHEN_COPYING_START content_copy download Use code with caution. IGNORE_WHEN_COPYING_END 5.0 Data Flow & Logic Diagrams 5.1 Hierarchical Folder Creation (get_or_create_path)

The application ensures a clean Root/Type/YYYY-MM structure for every data point.

Generated mermaid sequenceDiagram participant API as API Endpoint participant GDrive as Google Drive API API->>GDrive: Find/Create "DatabaseCalendarApp_Root" GDrive-->>API: Return Root Folder ID API->>GDrive: Find/Create "Prompt_Type_Folder" inside Root GDrive-->>API: Return Type Folder ID API->>GDrive: Find/Create "YYYY-MM" folder inside Type Folder GDrive-->>API: Return Month Folder ID IGNORE_WHEN_COPYING_START content_copy download Use code with caution. Mermaid IGNORE_WHEN_COPYING_END 5.2 Action Logging Workflow (log_action)

This workflow details how the application maintains an audit trail.

sequenceDiagram

participant Endpoint as API Endpoint (e.g., Upload)
participant Logger as log_action()
participant Sheets as Google Sheets API

Endpoint->>Logger: log_action("UPLOAD_SUCCESS", "message")
Logger->>Sheets: Authorize & find "Historical Log" sheet
Sheets-->>Logger: Return Sheet Object
Logger->>Sheets: Append Row: [Timestamp, "UPLOAD_SUCCESS", "message"]

6.0 Features & Functionalities 6.1 UI & View Management

Dual-View Interface: A switcher allows toggling between a detailed Month View and a high-level Year View.

Year View Navigation: The Year View provides a "heatmap" of data density. Clicking any month in this view instantly navigates to the detailed Month View for that month.

Lightning Mode Caching: Frontend state is cached, making tab and view switching instantaneous. The app fetches updates in the background for a seamless user experience.

6.2 Storage & Organization

Hierarchical File System: Data is no longer stored in a single folder. It is now automatically organized by Root Folder -> Prompt Type -> Year-Month -> Day.csv. This is vastly more scalable.

Multi-File Upload: The app now supports selecting and uploading multiple CSV files at once.

6.3 Data Integrity & Auditing

Comprehensive Logging: All major events (uploads, deletes, errors, logouts) are recorded with a timestamp in a dedicated Google Sheet, providing a complete audit trail.

Robust CSV Parsing: The parser for headerless text prompts is now hardened to handle commas within prompt text by treating each line as a single entity.

Smart appProperties: The "distributed" state of data is now stored as a metadata flag (appProperties) on the file in Google Drive, making it faster and more reliable to query.

6.4 Settings & Administration

Settings Panel: A new settings modal provides a centralized location for high-level administrative tasks.

Direct Log Access: A button in the settings panel opens the Google Sheets log file in a new browser tab.

Secure Logout: A dedicated logout function deletes the local token.json, forcing re-authentication on the next run.

"Delete All Data" Functionality: A heavily-guarded feature that allows the user to completely delete the entire root folder and all its contents from Google Drive after a typed confirmation.

7.0 Code Reference

The full, commented source code for v4.0 is available in the files provided: app.py, index.html, style.css, and script.js.

8.0 Considerations & Limitations

API Usage: The app now actively uses two Google APIs (Drive and Sheets). While single-user usage is unlikely to hit quotas, this increases the API call footprint.

Performance at Scale: The backend now paginates through month folders, which is a significant improvement. However, a single prompt type with many years of data (leading to hundreds of month folders) could still experience a minor delay on a forced refresh.

Non-Transactional Logging: The logging action is separate from the main data action. In a rare failure scenario, a data upload could succeed while the corresponding log entry fails (the error would be printed to the terminal).

Deduplication Key: Data deduplication during the append workflow is based solely on the prompt column. Prompts with identical text but different metadata will be treated as duplicates.

9.0 Future Enhancements (Roadmap)

Advanced Search: Enhance the search bar with date-range filters and the ability to search across all prompt types simultaneously.

Data Visualization: Build a new "Dashboard" tab that reads data from the Google Sheets log and creates charts visualizing prompt activity over time.

UI-Based Configuration: Allow users to edit folder names and theme colors directly from the Settings panel, which would write back to a configuration file.

Comprehensive Test Coverage: Build out the /tests directory with pytest scripts to automate API endpoint testing and validation of the data processing logic.