Logs

Date: 18/03/25

• Exploring the use of Kanbn for Agile collaboration - got comfortable with branching/merges and cleaning up gitignored .NET build files. Exploring how to use .Net, noting some similarity in structure to NodeJs.

Date 01/04/25

• Debating use of flat file vs database (DB). Decided on SQLite DB; easy transition if we want a different DB, efficient storage and organisation structure, dynamic, scalable, flexible but slightly more effort to set up.
• Did many Kanbn tasks trying to nail down precisely how everything will work.

Date 02/04/25

Microsoft Azure Setup

• Watched the set up page and videos. Learned we can manage permissions for the same resource group, we will be able to link easily to a translator, a sql databse and containers all within Azure.
• Decided we will do everything based in Azure.
• Tutorial on WebApp with DB: https://portal.azure.com/?quickstart=true#view/Microsoft_Azure_Resources/ServicePage.ReactView/id/WebApp
• Links extremely well to their co-owned Github.

After researching Azure's high-availability reference architecture (App Gateway, VNet, Private Link, WAF), we opted for the simpler App Service + Azure SQL pattern—enough for our needs without unnecessary complexity.

For a cloud-based WebApp with a database it seems to just be; Azure App Service → Hosts our C# WebApp (.NET-9), Azure Database → Cloud-based relational database, App Service → DB Connection → Securely connect the WebApp to the database

Sprint-1 (Local Development):

1. Reuben codes the C# WebApp locally temporarily using SQLite.
2. I set up an Azure SQL database and connection locally.
3. Mock Data Local Testing.

Sprint-2+ (Cloud-Integration, Improvements & Features)::

1. Push the app to Azure.
2. Configure CI/CD deployment.
3. Add data-analytics.
4. Improve performance & security.

• Made a resource group, not a high level project so no tagging needed.

Very simple; Search for "Resource Groups" on Azure Portal, Click Create, Name it, Pick a Region, Click Review+Create.

Created Resource Group, assigned Reuben Contributor via IAM, spun up Azure SQL (Free Tier), and later added our custom domain via CNAME/TXT verification.

Provisioned a single Azure SQL Free-Tier database (10 DBs, 100K vCore-seconds, 32 GB data + backup) inside our Resource Group; created a new server using Microsoft Entra-only authentication (not supported on Free Tier), enabled public access and allowed Azure services, whitelisted our dev IP, skipped ledger diagnostics, noted the future need for a managed identity, and set the collation to Latin1_General_100_CI_AS_SC_UTF8 for broad character support (lots of languages). Note: 5.50$ per-month after the free trial.

Server is container managing authentication/security. The App-service runs our .Net-App, has a Managed Identity, secure but negates need of username/password.

The next steps require:

1. Enable a managed identity on app service: In the App-Service, turn on System-assigned Managed Identity in Settings/Identity.
2. Grant Access for App: Go to SQL Server, under Active Directory admin, enable Entra-authentication. Then run in the Query-Editor for the database:
   CREATE USER [your-app-service-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD MEMBER [your-app-service-name];
3. Connect .NET:

• appsettings.json: cmd: dotnet add package Azure.Identity, dotnet add package Microsoft.Data.SqlClient
  Program.cs: var credential = new DefaultAzureCredential();\

4. Deployment of the .NET into the App Service: Make app service, realised later don't make it private as this forgets the Github deployment isn't within Azure, so connect to Github Repository and enable pre-configured CI!!!

Date 03/04/25

Create an Azure SQL Database using the REST API?: https://learn.microsoft.com/en-us/rest/api/sql/rest-api-sql-create-or-update-database
Configure continuous deployment to Azure App Service?: https://learn.microsoft.com/en-us/azure/app-service/deploy-continuous-deployment?tabs=github%2Cgithubactions#configure-the-deployment-source
Needed to re-add IP-address for Query Editor Acces?:

Note: Don't leave Query Editor with an unsaved complex script as it will be lost.
Merged everything previous and hopefully it is now connected to the DB. CI working well - automatically re-deploying with the pushed changes!!!

Testing DB-connection in Program.cs:
Not working! Alter roles?

Free Plan does not include Managed Identity: need to switch to SQL authentication!!! cmd: dotnet remove package Azure.Identity, dotnet clean, dotnet build
Removed identity use in Program.cs and appsettings.json: Checking LyricalUser in the DB:

Logging:

Program.cs Logging not working?!

• Enabling App Service logs didn't work. Log stream connects, app runs, no logs.
• cmd: dotnet add package Microsoft.Extensions.Logging.AzureAppServices
• Searched for hours. Saw something about VSCode remote debugger - sounds useful, since deployments are slow and we need the DB on cloud.
• Tried System.Diagnostics.Trace.TraceError("If you're seeing this, something bad happened") from https://learn.microsoft.com/en-us/azure/app-service/troubleshoot-diagnostic-logs - didn't help.

app.MapGet works!

• app.MapGet("/status", () =>"Service is running!"); Yes yes yes. That works. Took a whole day!
• Fixed by clearing default providers, adding the Console provider and App-Service diagnostics provider:
• Also using app.Logger instead of LoggerFactory. Adding AddAzureWebAppDiagnostics() pipes logs into Azure's Log Stream. Discovered possible useful aspects of the WebApp for later: Error Pages, App Service Editor, WebJobs DB connection failing - but logging now shows it. Might need to explicitly set Azure connection string.
  Logs broke again after adding connection string. Switched to:

Date 04/04/25

Logging works, no Database connection:

CloudSAc3a0ae0e was auto-generated — turns out it's the master DB login.

• Found a section in the DB showing the exact connection string format by auth-type. Super helpful. Also realized I could test the username/password combo using Query Editor (SQL login on the left, not Entra on the right).
• Didn't know the master password — found it in the Overview tab.
• Confirmed: LyricalUser is now set up for SQL auth!
• Issue was Environment Variables! Code had the right connection string, but Azure was using the WebApp Settings one, which overrode it. I am happy to log it is now working and connected.
  

Good. We don't need this in app.settings then:

Setting up the Database

I granted permissions to LyricalUser and made a quick table! Very Happy.
Injection: We may need to stop injection attacks if we are using simple account username/password ~ may not be needed.

TRUNCATE TABLE table_name is one way to reset identity id count, but it does also delete everything, another way would be to add a new_id column then delete the old one and rename the new one.

System Idea 1 set up: Words, Sentences, and a linking sentence_word table.
song → paragraph (group_id) → sentence (id and position) → words (id and position)
Sentences + words stored once each - keeps things clean. sentence_word turns a song into an ordered ID map. Feels very systematic. Translation-ready: English, Russian, Deutsch, Español, Français. Reuben can add more if needed - columns are extendable.
Designed with Reuben's flow in mind - checks if sentence/word exists. If not, adds it + links by ID. Since there's no deletion, IDs can just be max+1 - or if needed, remove IDENTITY column and set manually (though that logic must be watertight).
Really solid DB design exercise. Rewarding. Feel like my relational DB intuition is a lot sharper now.

And I think now the next main phase of the project will be writing a load of C#/SQL, slightly more Reuben's part. After that we can start mock testing and after I want to try set up simple Data Anlysis.

Date 11/04/25

Have collaborated on how the DataBase works so that the code can be written accordingly. Realised that if we stick the Connection String back into appsettings.json then we don't need to redeploy the app every time!
FYI: using mock-song https://lyricstranslate.com/en/pod-rakitoju-zelenoj-%D0%BF%D0%BE%D0%B4-%D1%80%D0%B0%D0%BA%D0%B8%D1%82%D0%BE%D1%8E-%D0%B7%D0%B5%D0%BB%D1%91%D0%BD%D0%BE%D0%B9-under-green-willow.html

Date 29-30/04/25

• Set up the user interactions: reconfigured the C# endpoints so that the logic would hold for multiple requests. Had the reply system set up.
• Set up user interaction answers (right or wrong).
• Chatbot inspiration: https://codepel.com/html-css/chat-box-html-code-for-website/
• Manually added all words, sentences, and connections for the mock-song - for checking the search system.
• Learned a ton of SQL in the process - especially splitting lyrics into cleaned words and auto-inserting!! Stuff I hadn't come across before.
• Tried connecting via Python using DeepSearch API, but long-term we'll want an Azure-based translator anyway.
• Right now, not worth the time - better to do it properly when we're ready.

Tried something clever to fill out sentence_words automatically but despite new SQL-knowledge it didn't work and I had to do it manually:

Also added an Og column for the song's orginal lyrics in whatever language, serving as the original lyrics which can be translated into whatever choice desired.
Here I better understood defining the table column definitions in C#; though I would like more practice in future.

• Bought domain on Namecheap - Purchased a .com on Namecheap, configured CNAME and TXT records to point to our Azure App Service - DNS propagation took ~30 min (Durham WiFi blocks it! DB/server slow wake up).
• Added a CNAME: Host: , Value: [azure app domain], TTL: 30 min for global propagation.

So need to warn anyone attempting to try it.
Useful DNS checking: https://www.whatsmydns.net/#CNAME/