@ 6e0ea5d6:0327f353

"Malcolm Forbes recounts that a lady, wearing a faded cotton dress, and her husband, dressed in an old handmade suit, stepped off a train in Boston, USA, and timidly made their way to the office of the president of Harvard University. They had come from Palo Alto, California, and had not scheduled an appointment. The secretary, at a glance, thought that those two, looking like country bumpkins, had no business at Harvard. — We want to speak with the president — the man said in a low voice. — He will be busy all day — the secretary replied curtly. — We will wait. The secretary ignored them for hours, hoping the couple would finally give up and leave. But they stayed there, and the secretary, somewhat frustrated, decided to bother the president, although she hated doing that. — If you speak with them for just a few minutes, maybe they will decide to go away — she said. The president sighed in irritation but agreed. Someone of his importance did not have time to meet people like that, but he hated faded dresses and tattered suits in his office. With a stern face, he went to the couple. — We had a son who studied at Harvard for a year — the woman said. — He loved Harvard and was very happy here, but a year ago he died in an accident, and we would like to erect a monument in his honor somewhere on campus. — My lady — said the president rudely —, we cannot erect a statue for every person who studied at Harvard and died; if we did, this place would look like a cemetery. — Oh, no — the lady quickly replied. — We do not want to erect a statue. We would like to donate a building to Harvard. The president looked at the woman's faded dress and her husband's old suit and exclaimed: — A building! Do you have even the faintest idea of how much a building costs? We have more than seven and a half million dollars' worth of buildings here at Harvard. The lady was silent for a moment, then said to her husband: — If that’s all it costs to found a university, why don’t we have our own? The husband agreed. The couple, Leland Stanford, stood up and left, leaving the president confused. Traveling back to Palo Alto, California, they established there Stanford University, the second-largest in the world, in honor of their son, a former Harvard student." Text extracted from: "Mileumlivros - Stories that Teach Values." Thank you for reading, my friend! If this message helped you in any way, consider leaving your glass “🥃” as a token of appreciation. A toast to our family!

@ 9e69e420:d12360c2

President Trump has intensified immigration enforcement, likening it to a wartime effort. Despite pouring resources into the U.S. Immigration and Customs Enforcement (ICE), arrest numbers are declining and falling short of goals. ICE fell from about 800 daily arrests in late January to fewer than 600 in early February. Critics argue the administration is merely showcasing efforts with ineffectiveness, while Trump seeks billions more in funding to support his deportation agenda. Increased involvement from various federal agencies is intended to assist ICE, but many lack specific immigration training. Challenges persist, as fewer immigrants are available for quick deportation due to a decline in illegal crossings. Local sheriffs are also pressured by rising demands to accommodate immigrants, which may strain resources further.

@ fd208ee8:0fd927c1

E-cash are coupons or tokens for Bitcoin, or Bitcoin debt notes that the mint issues. The e-cash states, essentially, "IoU 2900 sats". They're redeemable for Bitcoin on Lightning (hard money), and therefore can be used as cash (softer money), so long as the mint has a good reputation. That means that they're less fungible than Lightning because the e-cash from one mint can be more or less valuable than the e-cash from another. If a mint is buggy, offline, or disappears, then the e-cash is unreedemable. It also means that e-cash is more anonymous than Lightning, and that the sender and receiver's wallets don't need to be online, to transact. Nutzaps now add the possibility of parking transactions one level farther out, on a relay. The same relays that cannot keep npub profiles and follow lists consistent will now do monetary transactions. What we then have is * a **transaction on a relay** that triggers * a **transaction on a mint** that triggers * a **transaction on Lightning** that triggers * a **transaction on Bitcoin**. Which means that every relay that stores the nuts is part of a wildcat banking system. Which is fine, but relay operators should consider whether they wish to carry the associated risks and liabilities. They should also be aware that they should implement the appropriate features in their relay, such as expiration tags (nuts rot after 2 weeks), and to make sure that only expired nuts are deleted. There will be plenty of specialized relays for this, so don't feel pressured to join in, and research the topic carefully, for yourself. https://github.com/nostr-protocol/nips/blob/master/60.md

@ 0fa80bd3:ea7325de

#intro The Russian state made me a Bitcoiner. In 1991, it devalued my grandmother's hard-earned savings. She worked tirelessly in the kitchen of a dining car on the Moscow–Warsaw route. Everything she had saved for my sister and me to attend university vanished overnight. This story is similar to what many experienced, including Wences Casares. The pain and injustice of that time became my first lessons about the fragility of systems and the value of genuine, incorruptible assets, forever changing my perception of money and my trust in government promises. In 2014, I was living in Moscow, running a trading business, and frequently traveling to China. One day, I learned about the Cypriot banking crisis and the possibility of moving money through some strange thing called Bitcoin. At the time, I didn’t give it much thought. Returning to the idea six months later, as a business-oriented geek, I eagerly began studying the topic and soon dove into it seriously. I spent half a year reading articles on a local online journal, BitNovosti, actively participating in discussions, and eventually joined the editorial team as a translator. That’s how I learned about whitepapers, decentralization, mining, cryptographic keys, and colored coins. About Satoshi Nakamoto, Silk Road, Mt. Gox, and BitcoinTalk. Over time, I befriended the journal’s owner and, leveraging my management experience, later became an editor. I was drawn to the crypto-anarchist stance and commitment to decentralization principles. We wrote about the economic, historical, and social preconditions for Bitcoin’s emergence, and it was during this time that I fully embraced the idea. It got to the point where I sold my apartment and, during the market's downturn, bought 50 bitcoins, just after the peak price of $1,200 per coin. That marked the beginning of my first crypto winter. As an editor, I organized workflows, managed translators, developed a YouTube channel, and attended conferences in Russia and Ukraine. That’s how I learned about Wences Casares and even wrote a piece about him. I also met Mikhail Chobanyan (Ukrainian exchange Kuna), Alexander Ivanov (Waves project), Konstantin Lomashuk (Lido project), and, of course, Vitalik Buterin. It was a time of complete immersion, 24/7, and boundless hope. After moving to the United States, I expected the industry to grow rapidly, attended events, but the introduction of BitLicense froze the industry for eight years. By 2017, it became clear that the industry was shifting toward gambling and creating tokens for the sake of tokens. I dismissed this idea as unsustainable. Then came a new crypto spring with the hype around beautiful NFTs – CryptoPunks and apes. I made another attempt – we worked on a series called Digital Nomad Country Club, aimed at creating a global project. The proceeds from selling images were intended to fund the development of business tools for people worldwide. However, internal disagreements within the team prevented us from completing the project. With Trump’s arrival in 2025, hope was reignited. I decided that it was time to create a project that society desperately needed. As someone passionate about history, I understood that destroying what exists was not the solution, but leaving everything as it was also felt unacceptable. You can’t destroy the system, as the fiery crypto-anarchist voices claimed. With an analytical mindset (IQ 130) and a deep understanding of the freest societies, I realized what was missing—not only in Russia or the United States but globally—a Bitcoin-native system for tracking debts and financial interactions. This could return control of money to ordinary people and create horizontal connections parallel to state systems. My goal was to create, if not a Bitcoin killer app, then at least to lay its foundation. At the inauguration event in New York, I rediscovered the Nostr project. I realized it was not only technologically simple and already quite popular but also perfectly aligned with my vision. For the past month and a half, using insights and experience gained since 2014, I’ve been working full-time on this project.

@ e3ba5e1a:5e433365

My favorite line in any Marvel movie ever is in “Captain America.” After Captain America launches seemingly a hopeless assault on Red Skull’s base and is captured, we get [this line](https://www.youtube.com/shorts/kqsomjpz7ok): “Arrogance may not be a uniquely American trait, but I must say, you do it better than anyone.” Yesterday, I came across a comment on the song [Devil Went Down to Georgia](https://youtu.be/ut8UqFlWdDc) that had a very similar feel to it:  America has seemingly always been arrogant, in a uniquely American way. Manifest Destiny, for instance. The rest of the world is aware of this arrogance, and mocks Americans for it. A central point in modern US politics is the deriding of racist, nationalist, supremacist Americans. That’s not what I see. I see American Arrogance as not only a beautiful statement about what it means to be American. I see it as an ode to the greatness of humanity in its purest form. For most countries, saying “our nation is the greatest” *is*, in fact, twinged with some level of racism. I still don’t have a problem with it. Every group of people *should* be allowed to feel pride in their accomplishments. The destruction of the human spirit since the end of World War 2, where greatness has become a sin and weakness a virtue, has crushed the ability of people worldwide to strive for excellence. But I digress. The fears of racism and nationalism at least have a grain of truth when applied to other nations on the planet. But not to America. That’s because the definition of America, and the prototype of an American, has nothing to do with race. The definition of Americanism is *freedom*. The founding of America is based purely on liberty. On the God-given rights of every person to live life the way they see fit. American Arrogance is not a statement of racial superiority. It’s barely a statement of national superiority (though it absolutely is). To me, when an American comments on the greatness of America, it’s a statement about freedom. Freedom will always unlock the greatness inherent in any group of people. Americans are *definitionally* better than everyone else, because Americans are freer than everyone else. (Or, at least, that’s how it should be.) In *Devil Went Down to Georgia*, Johnny is approached by the devil himself. He is challenged to a ridiculously lopsided bet: a golden fiddle versus his immortal soul. He acknowledges the sin in accepting such a proposal. And yet he says, “God, I know you told me not to do this. But I can’t stand the affront to my honor. I am the greatest. The devil has nothing on me. So God, I’m gonna sin, but I’m also gonna win.” *Libertas magnitudo est*

@ daa41bed:88f54153

There has been a good bit of discussion on Nostr over the past few days about the merits of zaps as a method of engaging with notes, so after writing a rather lengthy [article on the pros of a strategic Bitcoin reserve](https://geek.npub.pro/post/dxqkgnjplttkvetprg8ox/), I wanted to take some time to chime in on the much more fun topic of digital engagement. Let's begin by defining a couple of things: **Nostr** is a decentralized, censorship-resistance protocol whose current biggest use case is social media (think Twitter/X). Instead of relying on company servers, it relies on relays that anyone can spin up and own their own content. Its use cases are much bigger, though, and this article is hosted on my own relay, using my own Nostr relay as an example. **Zap** is a tip or donation denominated in sats (small units of Bitcoin) sent from one user to another. This is generally done directly over the Lightning Network but is increasingly using Cashu tokens. For the sake of this discussion, how you transmit/receive zaps will be irrelevant, so don't worry if you don't know what [Lightning](https://lightning.network/) or [Cashu](https://cashu.space/) are. If we look at how users engage with posts and follows/followers on platforms like Twitter, Facebook, etc., it becomes evident that traditional social media thrives on engagement farming. The more outrageous a post, the more likely it will get a reaction. We see a version of this on more visual social platforms like YouTube and TikTok that use carefully crafted thumbnail images to grab the user's attention to click the video. If you'd like to dive deep into the psychology and science behind social media engagement, let me know, and I'd be happy to follow up with another article. In this user engagement model, a user is given the option to comment or like the original post, or share it among their followers to increase its signal. They receive no value from engaging with the content aside from the dopamine hit of the original experience or having their comment liked back by whatever influencer they provide value to. Ad revenue flows to the content creator. Clout flows to the content creator. Sales revenue from merch and content placement flows to the content creator. We call this a linear economy -- the idea that resources get created, used up, then thrown away. Users create content and farm as much engagement as possible, then the content is forgotten within a few hours as they move on to the next piece of content to be farmed. What if there were a simple way to give value back to those who engage with your content? By implementing some value-for-value model -- a circular economy. Enter zaps.  Unlike traditional social media platforms, Nostr does not actively use algorithms to determine what content is popular, nor does it push content created for active user engagement to the top of a user's timeline. Yes, there are "trending" and "most zapped" timelines that users can choose to use as their default, but these use relatively straightforward engagement metrics to rank posts for these timelines. That is not to say that we may not see clients actively seeking to refine timeline algorithms for specific metrics. Still, the beauty of having an open protocol with media that is controlled solely by its users is that users who begin to see their timeline gamed towards specific algorithms can choose to move to another client, and for those who are more tech-savvy, they can opt to run their own relays or create their own clients with personalized algorithms and web of trust scoring systems. Zaps enable the means to create a new type of social media economy in which creators can earn for creating content and users can earn by actively engaging with it. Like and reposting content is relatively frictionless and costs nothing but a simple button tap. Zaps provide active engagement because they signal to your followers and those of the content creator that this post has genuine value, quite literally in the form of money—sats.  I have seen some comments on Nostr claiming that removing likes and reactions is for wealthy people who can afford to send zaps and that the majority of people in the US and around the world do not have the time or money to zap because they have better things to spend their money like feeding their families and paying their bills. While at face value, these may seem like valid arguments, they, unfortunately, represent the brainwashed, defeatist attitude that our current economic (and, by extension, social media) systems aim to instill in all of us to continue extracting value from our lives. Imagine now, if those people dedicating their own time (time = money) to mine pity points on social media would instead spend that time with genuine value creation by posting content that is meaningful to cultural discussions. Imagine if, instead of complaining that their posts get no zaps and going on a tirade about how much of a victim they are, they would empower themselves to take control of their content and give value back to the world; where would that leave us? How much value could be created on a nascent platform such as Nostr, and how quickly could it overtake other platforms? Other users argue about user experience and that additional friction (i.e., zaps) leads to lower engagement, as proven by decades of studies on user interaction. While the added friction may turn some users away, does that necessarily provide less value? I argue quite the opposite. You haven't made a few sats from zaps with your content? Can't afford to send some sats to a wallet for zapping? How about using the most excellent available resource and spending 10 seconds of your time to leave a comment? Likes and reactions are valueless transactions. Social media's real value derives from providing monetary compensation and actively engaging in a conversation with posts you find interesting or thought-provoking. Remember when humans thrived on conversation and discussion for entertainment instead of simply being an onlooker of someone else's life? If you've made it this far, my only request is this: try only zapping and commenting as a method of engagement for two weeks. Sure, you may end up liking a post here and there, but be more mindful of how you interact with the world and break yourself from blind instinct. You'll thank me later. 

@ e3ba5e1a:5e433365

I got into a [friendly discussion](https://x.com/snoyberg/status/1887007888117252142) on X regarding health insurance. The specific question was how to deal with health insurance companies (presumably unfairly) denying claims? My answer, as usual: get government out of it! The US healthcare system is essentially the worst of both worlds: * Unlike full single payer, individuals incur high costs * Unlike a true free market, regulation causes increases in costs and decreases competition among insurers I'm firmly on the side of moving towards the free market. (And I say that as someone living under a single payer system now.) Here's what I would do: * Get rid of tax incentives that make health insurance tied to your employer, giving individuals back proper freedom of choice. * Reduce regulations significantly. * In the short term, some people will still get rejected claims and other obnoxious behavior from insurance companies. We address that in two ways: 1. Due to reduced regulations, new insurance companies will be able to enter the market offering more reliable coverage and better rates, and people will flock to them because they have the freedom to make their own choices. 2. Sue the asses off of companies that reject claims unfairly. And ideally, as one of the few legitimate roles of government in all this, institute new laws that limit the ability of fine print to allow insurers to escape their responsibilities. (I'm hesitant that the latter will happen due to the incestuous relationship between Congress/regulators and insurers, but I can hope.) Will this magically fix everything overnight like politicians normally promise? No. But it will allow the market to return to a healthy state. And I don't think it will take long (order of magnitude: 5-10 years) for it to come together, but that's just speculation. And since there's a high correlation between those who believe government can fix problems by taking more control and demanding that only credentialed experts weigh in on a topic (both points I strongly disagree with BTW): I'm a trained actuary and worked in the insurance industry, and have directly seen how government regulation reduces competition, raises prices, and harms consumers. And my final point: I don't think any prior art would be a good comparison for deregulation in the US, it's such a different market than any other country in the world for so many reasons that lessons wouldn't really translate. Nonetheless, I asked Grok for some empirical data on this, and at best the results of deregulation could be called "mixed," but likely more accurately "uncertain, confused, and subject to whatever interpretation anyone wants to apply." https://x.com/i/grok/share/Zc8yOdrN8lS275hXJ92uwq98M

@ 91bea5cd:1df4451c

### Definição de ULID: Timestamp 48 bits, Aleatoriedade 80 bits Sendo Timestamp 48 bits inteiro, tempo UNIX em milissegundos, Não ficará sem espaço até o ano 10889 d.C. e Aleatoriedade 80 bits, Fonte criptograficamente segura de aleatoriedade, se possível. #### Gerar ULID ```sql CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE FUNCTION generate_ulid() RETURNS TEXT AS $$ DECLARE -- Crockford's Base32 encoding BYTEA = '0123456789ABCDEFGHJKMNPQRSTVWXYZ'; timestamp BYTEA = E'\\000\\000\\000\\000\\000\\000'; output TEXT = ''; unix_time BIGINT; ulid BYTEA; BEGIN -- 6 timestamp bytes unix_time = (EXTRACT(EPOCH FROM CLOCK_TIMESTAMP()) * 1000)::BIGINT; timestamp = SET_BYTE(timestamp, 0, (unix_time >> 40)::BIT(8)::INTEGER); timestamp = SET_BYTE(timestamp, 1, (unix_time >> 32)::BIT(8)::INTEGER); timestamp = SET_BYTE(timestamp, 2, (unix_time >> 24)::BIT(8)::INTEGER); timestamp = SET_BYTE(timestamp, 3, (unix_time >> 16)::BIT(8)::INTEGER); timestamp = SET_BYTE(timestamp, 4, (unix_time >> 8)::BIT(8)::INTEGER); timestamp = SET_BYTE(timestamp, 5, unix_time::BIT(8)::INTEGER); -- 10 entropy bytes ulid = timestamp || gen_random_bytes(10); -- Encode the timestamp output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 0) & 224) >> 5)); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 0) & 31))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 1) & 248) >> 3)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 1) & 7) << 2) | ((GET_BYTE(ulid, 2) & 192) >> 6))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 2) & 62) >> 1)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 2) & 1) << 4) | ((GET_BYTE(ulid, 3) & 240) >> 4))); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 3) & 15) << 1) | ((GET_BYTE(ulid, 4) & 128) >> 7))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 4) & 124) >> 2)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 4) & 3) << 3) | ((GET_BYTE(ulid, 5) & 224) >> 5))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 5) & 31))); -- Encode the entropy output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 6) & 248) >> 3)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 6) & 7) << 2) | ((GET_BYTE(ulid, 7) & 192) >> 6))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 7) & 62) >> 1)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 7) & 1) << 4) | ((GET_BYTE(ulid, 8) & 240) >> 4))); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 8) & 15) << 1) | ((GET_BYTE(ulid, 9) & 128) >> 7))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 9) & 124) >> 2)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 9) & 3) << 3) | ((GET_BYTE(ulid, 10) & 224) >> 5))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 10) & 31))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 11) & 248) >> 3)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 11) & 7) << 2) | ((GET_BYTE(ulid, 12) & 192) >> 6))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 12) & 62) >> 1)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 12) & 1) << 4) | ((GET_BYTE(ulid, 13) & 240) >> 4))); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 13) & 15) << 1) | ((GET_BYTE(ulid, 14) & 128) >> 7))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 14) & 124) >> 2)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(ulid, 14) & 3) << 3) | ((GET_BYTE(ulid, 15) & 224) >> 5))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(ulid, 15) & 31))); RETURN output; END $$ LANGUAGE plpgsql VOLATILE; ``` #### ULID TO UUID ```sql CREATE OR REPLACE FUNCTION parse_ulid(ulid text) RETURNS bytea AS $$ DECLARE -- 16byte bytes bytea = E'\\x00000000 00000000 00000000 00000000'; v char[]; -- Allow for O(1) lookup of index values dec integer[] = ARRAY[ 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 255, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 255, 255, 255, 255, 255, 255, 255, 10, 11, 12, 13, 14, 15, 16, 17, 1, 18, 19, 1, 20, 21, 0, 22, 23, 24, 25, 26, 255, 27, 28, 29, 30, 31, 255, 255, 255, 255, 255, 255, 10, 11, 12, 13, 14, 15, 16, 17, 1, 18, 19, 1, 20, 21, 0, 22, 23, 24, 25, 26, 255, 27, 28, 29, 30, 31 ]; BEGIN IF NOT ulid ~* '^[0-7][0-9ABCDEFGHJKMNPQRSTVWXYZ]{25}$' THEN RAISE EXCEPTION 'Invalid ULID: %', ulid; END IF; v = regexp_split_to_array(ulid, ''); -- 6 bytes timestamp (48 bits) bytes = SET_BYTE(bytes, 0, (dec[ASCII(v[1])] << 5) | dec[ASCII(v[2])]); bytes = SET_BYTE(bytes, 1, (dec[ASCII(v[3])] << 3) | (dec[ASCII(v[4])] >> 2)); bytes = SET_BYTE(bytes, 2, (dec[ASCII(v[4])] << 6) | (dec[ASCII(v[5])] << 1) | (dec[ASCII(v[6])] >> 4)); bytes = SET_BYTE(bytes, 3, (dec[ASCII(v[6])] << 4) | (dec[ASCII(v[7])] >> 1)); bytes = SET_BYTE(bytes, 4, (dec[ASCII(v[7])] << 7) | (dec[ASCII(v[8])] << 2) | (dec[ASCII(v[9])] >> 3)); bytes = SET_BYTE(bytes, 5, (dec[ASCII(v[9])] << 5) | dec[ASCII(v[10])]); -- 10 bytes of entropy (80 bits); bytes = SET_BYTE(bytes, 6, (dec[ASCII(v[11])] << 3) | (dec[ASCII(v[12])] >> 2)); bytes = SET_BYTE(bytes, 7, (dec[ASCII(v[12])] << 6) | (dec[ASCII(v[13])] << 1) | (dec[ASCII(v[14])] >> 4)); bytes = SET_BYTE(bytes, 8, (dec[ASCII(v[14])] << 4) | (dec[ASCII(v[15])] >> 1)); bytes = SET_BYTE(bytes, 9, (dec[ASCII(v[15])] << 7) | (dec[ASCII(v[16])] << 2) | (dec[ASCII(v[17])] >> 3)); bytes = SET_BYTE(bytes, 10, (dec[ASCII(v[17])] << 5) | dec[ASCII(v[18])]); bytes = SET_BYTE(bytes, 11, (dec[ASCII(v[19])] << 3) | (dec[ASCII(v[20])] >> 2)); bytes = SET_BYTE(bytes, 12, (dec[ASCII(v[20])] << 6) | (dec[ASCII(v[21])] << 1) | (dec[ASCII(v[22])] >> 4)); bytes = SET_BYTE(bytes, 13, (dec[ASCII(v[22])] << 4) | (dec[ASCII(v[23])] >> 1)); bytes = SET_BYTE(bytes, 14, (dec[ASCII(v[23])] << 7) | (dec[ASCII(v[24])] << 2) | (dec[ASCII(v[25])] >> 3)); bytes = SET_BYTE(bytes, 15, (dec[ASCII(v[25])] << 5) | dec[ASCII(v[26])]); RETURN bytes; END $$ LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION ulid_to_uuid(ulid text) RETURNS uuid AS $$ BEGIN RETURN encode(parse_ulid(ulid), 'hex')::uuid; END $$ LANGUAGE plpgsql IMMUTABLE; ``` #### UUID to ULID ```sql CREATE OR REPLACE FUNCTION uuid_to_ulid(id uuid) RETURNS text AS $$ DECLARE encoding bytea = '0123456789ABCDEFGHJKMNPQRSTVWXYZ'; output text = ''; uuid_bytes bytea = uuid_send(id); BEGIN -- Encode the timestamp output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 0) & 224) >> 5)); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 0) & 31))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 1) & 248) >> 3)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 1) & 7) << 2) | ((GET_BYTE(uuid_bytes, 2) & 192) >> 6))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 2) & 62) >> 1)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 2) & 1) << 4) | ((GET_BYTE(uuid_bytes, 3) & 240) >> 4))); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 3) & 15) << 1) | ((GET_BYTE(uuid_bytes, 4) & 128) >> 7))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 4) & 124) >> 2)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 4) & 3) << 3) | ((GET_BYTE(uuid_bytes, 5) & 224) >> 5))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 5) & 31))); -- Encode the entropy output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 6) & 248) >> 3)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 6) & 7) << 2) | ((GET_BYTE(uuid_bytes, 7) & 192) >> 6))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 7) & 62) >> 1)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 7) & 1) << 4) | ((GET_BYTE(uuid_bytes, 8) & 240) >> 4))); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 8) & 15) << 1) | ((GET_BYTE(uuid_bytes, 9) & 128) >> 7))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 9) & 124) >> 2)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 9) & 3) << 3) | ((GET_BYTE(uuid_bytes, 10) & 224) >> 5))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 10) & 31))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 11) & 248) >> 3)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 11) & 7) << 2) | ((GET_BYTE(uuid_bytes, 12) & 192) >> 6))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 12) & 62) >> 1)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 12) & 1) << 4) | ((GET_BYTE(uuid_bytes, 13) & 240) >> 4))); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 13) & 15) << 1) | ((GET_BYTE(uuid_bytes, 14) & 128) >> 7))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 14) & 124) >> 2)); output = output || CHR(GET_BYTE(encoding, ((GET_BYTE(uuid_bytes, 14) & 3) << 3) | ((GET_BYTE(uuid_bytes, 15) & 224) >> 5))); output = output || CHR(GET_BYTE(encoding, (GET_BYTE(uuid_bytes, 15) & 31))); RETURN output; END $$ LANGUAGE plpgsql IMMUTABLE; ``` #### Gera 11 Digitos aleatórios: YBKXG0CKTH4 ```sql -- Cria a extensão pgcrypto para gerar uuid CREATE EXTENSION IF NOT EXISTS pgcrypto; -- Cria a função para gerar ULID CREATE OR REPLACE FUNCTION gen_lrandom() RETURNS TEXT AS $$ DECLARE ts_millis BIGINT; ts_chars TEXT; random_bytes BYTEA; random_chars TEXT; base32_chars TEXT := '0123456789ABCDEFGHJKMNPQRSTVWXYZ'; i INT; BEGIN -- Pega o timestamp em milissegundos ts_millis := FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000)::BIGINT; -- Converte o timestamp para base32 ts_chars := ''; FOR i IN REVERSE 0..11 LOOP ts_chars := ts_chars || substr(base32_chars, ((ts_millis >> (5 * i)) & 31) + 1, 1); END LOOP; -- Gera 10 bytes aleatórios e converte para base32 random_bytes := gen_random_bytes(10); random_chars := ''; FOR i IN 0..9 LOOP random_chars := random_chars || substr(base32_chars, ((get_byte(random_bytes, i) >> 3) & 31) + 1, 1); IF i < 9 THEN random_chars := random_chars || substr(base32_chars, (((get_byte(random_bytes, i) & 7) << 2) | (get_byte(random_bytes, i + 1) >> 6)) & 31 + 1, 1); ELSE random_chars := random_chars || substr(base32_chars, ((get_byte(random_bytes, i) & 7) << 2) + 1, 1); END IF; END LOOP; -- Concatena o timestamp e os caracteres aleatórios RETURN ts_chars || random_chars; END; $$ LANGUAGE plpgsql; ``` #### Exemplo de USO ```sql -- Criação da extensão caso não exista CREATE EXTENSION IF NOT EXISTS pgcrypto; -- Criação da tabela pessoas CREATE TABLE pessoas ( ID UUID DEFAULT gen_random_uuid ( ) PRIMARY KEY, nome TEXT NOT NULL ); -- Busca Pessoa na tabela SELECT * FROM "pessoas" WHERE uuid_to_ulid ( ID ) = '252FAC9F3V8EF80SSDK8PXW02F'; ``` ### Fontes - https://github.com/scoville/pgsql-ulid - https://github.com/geckoboard/pgulid