That's the kind of thing that I think really enhances the game. Even if the AI still occasionally makes mistakes like getting picked off while on the march, or other things like that, it now poses a threat. At least from my own previous experience, the threat wasn't really as noticeable since they'd often turn back instead of going for the killing blow. Now though, I no longer feel safe. When a war gets declared on me, I don't want to risk relying on a token force of archers or just having walls to keep me protected. I have to be careful.
The one caveat to all this is that this is where I ended the session - it's entirely possible that that cavalry will sit around dumbly and not attack, or just move off. The battering ram placement, while impressive insofar as the AI's now building them, isn't great either.
The thing that I find most interesting and encouraging, given my assorted complaints about the lack of strategy in Civ VI, is that this all stems from a clear strategic mistake I made several turns earlier, and the AI is actively punishing me for it. Alexander had offered a joint war with Saladin - I didn't especially need it and still have no military, but he is my closest neighbour (and with my settlement options on other continents removed as the cities I wanted to settle were taken, I have the Casa de Concepcion as an incentive to take cities on other continents by force) and that would make both Gorgo and Alexander happy. At that point I was still expecting to be able to settle the Barrier Reef, in which case I would have needed a cultural alliance with Alex for long enough for the city to establish and grow.
Korea - who had been friendly but I'd neglected to declare or ally with - was upset with my unnecessary warmongering, but I paid that little attention given routine AI messages and denunciations with no follow-through. Hence not building the wall in time - I started it only when I saw Hwachas on my doorstep.
Ultimately, then, I'm losing Leiden as a result of starting a war I didn't need and not managing my relations with Korea effectively - as I should have since they're the only civ I share a continent with. Even the diplomacy makes sense, a rarity in a world where Saladin keeps shouting at me for not having enough population and Tamar denounces me for a shortage of walls (I'll grant that she could have a point in this case).
EDIT: Then this happened.
I felt rather cheated after devoting thought as to my next moves after it was captured. Unfortunately, though Korea withdrew and its battering ram knights kept wandering around aimlessly, after a few turns shuffling Swordsmen about and losing two, Tana finally moved in and razed the city.
Maybe the AI civs don't raze (I don't think I've seen them do so) but Korea didn't want the city in that precise spot, so the Korean AI didn't know what to do?
Unfortunately I'm now in a Dark Age, otherwise I'd annex Antananarivo and rebuild Leiden.
The AI is capable of organizing an effective city assault when given the chance, and per your example they're able to defend their own cities decently enough that it's not a simple matter anymore to capture their cities.
Their target selection is also interesting. The AI very rarely goes for targets other than the capital - it's a longstanding weakness that they force through intervening areas heedless of the casualties they take on the way. If they can't hit the capital they usually simply don't attack. Obviously my capital's on another island. Maybe this is because Korea had wanted to settle this area (they had a settler - escorted by an archer - moving back and forth after I deliberately parked a warrior in its way. I may have simply been sitting on the particular spot it had decided to settle - not the one I chose, but it makes sense for the AI to choose one slightly closer to its other cities for the loyalty).
In addition to that, I saw in your screenshot that a city state was helping the AI. That's something I've noticed too in the new patch, I had made the same assumption that liberating a city state would be easy. Instead the AI defended it well, and their city-state ally Preslav razed my ally Babylon. I was actually really concerned about Preslav's army. They had a lot of swordsmen and were heading my way next. I rushed to make peace with Gitarja since I didn't want my cities to fall too. There's a level of excitement about the AI threat that I haven't felt much before. Players who go for a warmongering run probably won't be as concerned, but whenever I play peacefully (most of the time) I'm a lot more more careful. Looking forward to seeing how modders and future patches make it better too
I've had city states aggressively support their AIs in war, but rarely in Civ VI - it was much commoner in Civ V, including the AI's apparent strategic use of city states within or close to enemy territory. I found to my surprise that Armagh (on my home island) was allied with Saladin, so I now have a war there - nothing threatening, but I have an incentive to militarise so I can move in and raze it (it's a pretty useless snow city). Seondok became suzerain of Antananarivo (taking it from me) only shortly before the war - I'd like to think that was deliberate planning. I'd thought I'd sent Amani to it - which, when she arrived, would render it neutral - but remembered that I wasn't allowed to while it was at war with me.
I prefer huge maps, but in my opinion huge maps makes it even easier to deal with the AI. It doesn't build well, optimize well, and places cities in extremely poor locations even though half the map around it is empty. Small maps benefit the AI because it can at least bum-rush you if you try to tech up or expand too aggressively without protecting yourself.
I haven't run across issues with AI city placement with the current patch, which the loyalty system appears to have improved, though admittedly I don't pay a great deal of attention to AI city placement. Multiple times the AI has settled the specific spots I was aiming for, and where it differs by a tile or two that can usually be explained by that tile exerting more loyalty pressure in their favour. It now actively settles natural wonders, though doesn't prioritise them as highly as it should (in my current game Macedon left the barrier reef alone for far too long - but because it was out of reach for me due to loyalty concerns, the Georgians moved in). The AI will 'oversettle' and take terrible late spots just because there's room for a city, but it won't settle bad spots early.