## 1\. Project Overview The goal of this project is to build a Streamlit application to replace a manual, multi-step business process. The application will allow the Publishing team to upload an XLSX file, validate its content, convert timestamps, and trigger a series of API calls to update song delivery dates in both QA and Production environments through a user-friendly web interface. This plan outlines the necessary steps and structure for a developer to implement the application. ----- ## 2\. Proposed Project Structure A clean and modular structure will make the application easier to develop, test, and maintain. ``` . ├── .env # For storing environment variables locally ├── .gitignore # To exclude virtual environments, caches, etc. ├── Dockerfile # For containerizing the application for deployment ├── pyproject.toml # Project metadata and dependencies managed by uv └── src/ ├── __init__.py ├── app.py # Main Streamlit application logic and UI ├── data_processing.py # Functions for file reading, validation, and transformation └── graphql_client.py # Functions for interacting with the GraphQL API ``` ----- ## 3\. Dependencies and Environment Setup The project will use **uv** as the package manager. 1. **Initialize the Environment**: ```bash # Create a virtual environment uv venv source .venv/bin/activate ``` 2. **`pyproject.toml`**: Create this file with the following dependencies. ```toml [project] name = "publishing-delivery-updater" version = "0.1.0" requires-python = ">=3.9" dependencies = [ "streamlit", "pandas", "openpyxl", # Required by pandas to read .xlsx files "pytz", "httpx", "python-dotenv", "aiohttp", # For async GraphQL client ] ``` 3. **Install Dependencies**: ```bash uv pip install -e . ``` ----- ## 4\. Development Steps ### Step 1: Basic Streamlit App Layout (`src/app.py`) - Create the main application file `src/app.py`. - Set up the basic UI structure with titles and placeholders for different sections: - File uploader widget. - An area to display validation errors. - Buttons for controlling the workflow (e.g., "Process File", "Proceed to QA", "Confirm for PROD"). - Placeholders for displaying statistics after API calls. - Use `st.session_state` to manage the state of the application throughout the user's journey (e.g., storing the uploaded data, validation results, etc.). ### Step 2: File Upload and Data Processing (`src/data_processing.py`) - **`read_and_prepare_data(uploaded_file)` function**: - Accepts the uploaded file object from Streamlit's `st.file_uploader`. - Uses `pandas.read_excel()` to read the data into a DataFrame. - The columns are `Pub Song ID` and `timestamp` as seen in `Orchard Publishing_Product_Song_Submission_20250819.xlsx - Sheet1.csv`. - **`validate_and_transform_row(row)` function**: - This function will process a single row (a pandas Series). - **Validation**: - Check if `Pub Song ID` is a valid integer. - Check if `timestamp` is a valid datetime string. Use a `try-except` block with `pd.to_datetime`. - **Timestamp Conversion**: - The input timezone is specified as EST/EDT. The most reliable way to handle this is to assume the timezone is `America/New_York`, which correctly handles both EST and EDT. - Use the `pytz` library to localize the timestamp to `America/New_York` and then convert it to UTC. - Format the final UTC timestamp as `MM/DD/YYYY HH:mm` as seen in `Orchard Publishing_Product_Song_Submission_20250819.csv`. - **`process_dataframe(df)` function**: - Iterates through the input DataFrame. - Applies the `validate_and_transform_row` function to each row. - Separates the rows into two DataFrames: `valid_rows` and `invalid_rows`. - Returns both DataFrames. ### Step 3: GraphQL Client (`src/graphql_client.py`) This module will handle all communication with the `graphql-publishing` service. The logic should be adapted from `delivery-updater.ts`. - **Configuration**: - Store QA and PROD GraphQL URLs in a `.env` file and load them as environment variables. - `QA_GRAPHQL_URL=...` - `PROD_GRAPHQL_URL=...` - Store required headers (e.g., `Orchard-Profile-Id`, `Orchard-Identity-Id`) in the `.env` file as well. Reference the `HEADERS` constant in `delivery-updater.ts` and the parameters in `Jenkinsfile`. - **Define the GraphQL Mutation**: - Define the `updatePublishingCompositionsDeliveryDate` mutation as a Python string. This can be extracted directly from the `gql` tag in `delivery-updater.ts`. - **`update_delivery_date(session, url, headers, pub_song_id, delivery_date)` async function**: - This function will be an `async` coroutine. - It takes an `aiohttp.ClientSession`, API URL, headers, and the song data as arguments. - It constructs the request variables (payload) for the mutation. - It makes a single `POST` request to the GraphQL endpoint. - It should include error handling to catch API errors (e.g., HTTP status codes, GraphQL errors in the response body) and return a status (success/failure) and any error message. ### Step 4: Integrate Logic and UI Workflow (`src/app.py`) 1. **File Upload**: - Use `st.file_uploader("Upload XLSX file", type=["xlsx"])`. - Once a file is uploaded, store it in `st.session_state` and trigger the processing step. 2. **Validation Step**: - Call `data_processing.process_dataframe()` on the uploaded data. - Store `valid_rows` and `invalid_rows` in `st.session_state`. - If `invalid_rows` is not empty: - Display the `invalid_rows` DataFrame using `st.dataframe`. - Show two buttons: - `st.button("Ignore errors and proceed with valid rows")` - `st.button("Start over with a new file")` - If there are no invalid rows, automatically proceed to the QA upload step. 3. **QA Upload Step**: - Add a button "Upload to QA". - When clicked, initiate the parallel upload process: - Create a list of async tasks by calling `graphql_client.update_delivery_date` for each row in the `valid_rows` DataFrame. - Use `asyncio.gather` to execute these tasks concurrently. - Display a progress bar (`st.progress`) that updates as tasks are completed. 4. **QA Statistics**: - After the upload finishes, collect the results. - Display the statistics using `st.metric`: - Number of songs successfully updated. - Number of rows with errors. - If there were any errors, display the specific rows and the error messages returned by the API. 5. **Production Confirmation and Upload**: - If and only if the QA upload had zero errors, display a confirmation button: `st.button("CONFIRM and Upload to PROD")`. - Use `st.warning` to inform the user that this action is irreversible and will affect the production environment. - When the button is clicked, repeat the same async upload process, but this time passing the PROD GraphQL URL and credentials to the `graphql_client`. 6. **PROD Statistics**: - After the PROD upload is complete, display the final success and error counts, similar to the QA statistics step. ----- ## 5\. Dockerfile for Deployment Create a `Dockerfile` to containerize the Streamlit app for deployment on AWS ECS Fargate. ```dockerfile # Use an official Python runtime as a parent image FROM ghcr.io/astral-sh/uv:python3.12-bookworm-slim # Install uv RUN pip install uv # Set the working directory in the container WORKDIR /app # Copy the project files into the container COPY pyproject.toml . # Install project dependencies RUN uv pip install --system --no-cache -e . # Copy the application source code COPY ./src ./src # Expose the port Streamlit runs on EXPOSE 8080 # Set health check for the load balancer HEALTHCHECK CMD streamlit hello # Command to run the application CMD ["streamlit", "run", "src/app.py", "--server.port=8080", "--server.address=0.0.0.0"] ``` ----- ## 6\. Configuration and Best Practices - **Typed Variables**: Use Python's type hinting for all function signatures and variables to improve code clarity and maintainability. - **Error Handling**: Implement robust error handling for file parsing, data validation, and API interactions. Provide clear, user-friendly error messages in the UI. - **Environment Variables**: All sensitive information (API URLs, credentials, headers) must be managed via environment variables and **never** hard-coded. Use a `.env` file for local development and configure environment variables directly in the ECS Task Definition for deployment. - **Code Style**: Adhere to PEP 8 and use a consistent code formatter like Black. - **User Feedback**: Provide continuous feedback to the user, such as spinners during processing, progress bars during uploads, and clear status messages (`st.success`, `st.error`, `st.info`).