SIPC is just the framework. The primary thing is that customer assets are held in custodian accounts and not intermingled with the brokerage assets themselves. This is different than a bank which can lose your money if it does go bankrupt - it's just that FDIC steps in and makes whole the customers that qualify for the insurance (and pays out to other customers above the limits first before other creditors).
No, the SIPC, like the FDIC, can step in and make people whole if assets go missing. FDIC's for deposit accounts, SIPC is the equivalent for brokerage assets.
"SIPC oversees the liquidation of member firms that close when the firm is bankrupt or in financial trouble, and customer assets are missing."
In Coinbase's case, holding in a custodian account doesn't save them if the Bitcoin in it gets sent to an attacker's wallet. They're just gone, and neither the SIPC nor FDIC cover that asset class.
This is not entirely true, especially if you have a margin account.
In the US, there is Government insurance up to the SIPC limit of $500,000 of securities and $250,000 of cash. But coverage ends at that point.
I have had the annoying experience of having to pry restricted stock certificates out of the vaults of a failed broker. Fear of the regulatory agencies is strong enough that a week of phone calls produced the certificates.
