DEV Community: ROUTE06, Inc.

What We Did to Gain 3,000 GitHub Stars for the Liam Repository

Takashi Masuda — Mon, 21 Apr 2025 08:30:55 +0000

Recently, the Liam repository exceeded 3,000 stars just three months after its release. The repository continues to gain stars, alongside increases in traffic, forks, and external contributions.

These stars didn't accumulate naturally—they were the result of proactive strategies.

In this article, I'll share our thinking and the strategies we implemented to achieve 3,000 GitHub stars.

What is Liam ERD?

Liam ERD is the first product under the Liam brand, a tool that easily generates beautiful ER diagrams automatically from database schemas. It's released as open-source software with the goal of growing alongside the community.

Liam ERD

Automatically generates beautiful and easy-to-read ER diagrams from your database.

liambx.com

Setting 3,000 GitHub Stars as a KPI

We set 3,000 GitHub stars as our KPI for the following reasons:

We are currently developing a paid product in the database design space based on Liam ERD
We wanted to significantly expand recognition for the first product under the Liam brand
GitHub stars are a public metric that serves as a leading indicator of user trust¹

Strategies Found from Predecessors

Having set GitHub stars as our KPI, what specific actions should we take?

Throughout my career as a software engineer, I've developed OSS individually and created OSS repositories for companies, but I had never promoted a company's OSS. Also, blatant promotion isn't something I personally prefer.

I consulted Google and ChatGPT, but ultimately found an article featured on the Star History website.

🔗 The (Detailed & Creative) Playbook for Getting More GitHub Stars

"Writing the post I wish I found 6 months ago" was exactly the information I was looking for.

Strategies Implemented to Gain 3,000 GitHub Stars

Basically, I turned everything mentioned in the Star History article into actionable tasks and implemented the ones that were feasible for us.

Here are the specific strategies we implemented, with links included:

1. Preparation

Increasing traffic without proper repository preparation would be counterproductive. First, we focused on preparing the repository to welcome interested users.

Setting up the GitHub Repository for OSS

We set up the repository following the guidelines in the article below. We tried to include all GitHub recommendations and security measures.

Takashi Masuda for ROUTE06, Inc.

Jan 6 '25

Sharing the "OSS Publishing Tutorial" Created by ROUTE06, Inc.

#ospo #opensource #github

Comments

8 min read

Organizing the README.md

README.md is the face of the repository.

To make it intuitively understandable at first glance, we focused on:

Using attractive animated GIFs to instantly show what the ER diagrams look like
Creating a clear tagline that concisely summarizes the tool's features
Using colorful badges created with Shields.io to display the tool's status

Additionally, to encourage community participation, we included:

Links to contribution guidelines and roadmap
Visualization of contributors using contrib.rocks

After release, screenshots of the README.md were frequently shared on social media, confirming its role as the repository's face.

2. Directly Increasing GitHub Stars to 100

Before sharing on social media, we wanted to show that many people were already interested in the repository, so we aimed to reach 100 stars through direct strategies.

Sending Kickoff Messages to Friends and Acquaintances

I directly asked friends and acquaintances to star the repository via X (formerly Twitter) DMs and Slack.

Making the First Post on X After Exceeding 100 Stars

We had planned to post after getting a few dozen stars, but the response exceeded our expectations, so we posted after surpassing 100 stars.

https://x.com/liam_app/status/1881975247613923737

Here's what we focused on in this post:

Prepared a video that clearly demonstrates what the tool can do
Concisely introduced features in bullet points
Mentioned well-known relevant accounts
- React Flow quoted and reposted it²

As a result, we gained 41 reposts and approximately 30,000 views.

💡️ Although we are a Japanese team, all our X posts are in English. This is to avoid being perceived as a product only for Japanese users, which could limit its appeal in English-speaking regions. Based on our estimates, the majority of stars seem to come from users outside Japan.

3. Indirect Strategies After Exceeding 100 GitHub Stars

Creating Blog Content on the Service Website

We created content for our company blog, including direct or indirect introductions to Liam ERD, list articles, and knowledge-sharing pieces.

Example: Introducing Liam ERD - Liam ERD

Distributing Blog Content to Various Media

We cross-posted to dev.to, Hashnode, and Medium with canonical URLs set.

Example: Generate Beautiful and Interactive ER-Diagrams with Liam ERD - DEV Community

We posted slightly modified content on Reddit and HackerNoon. As a side note, you can't post to certain subreddits on Reddit without accumulating karma first. It's better to build up karma early.

Example: New Open-Source Tool Lets You Auto-Generate ER-Diagrams for Database Visualization | HackerNoon

We also posted a translated article on Zenn for the Japanese audience. This article helped generate our first +100 stars, but it resulted in Japan accounting for over 60% of stars by country, raising concerns about our initial hypothesis regarding the potential to be overlooked in English-speaking regions.

Generate Beautiful and Interactive ER Diagrams with Liam ERD - Zenn

Promoting on Various Sites and Newsletters

In addition to the sites mentioned in the Star History article, we researched other potential promotional sites. Except for Hacker News and Reddit, these were all new to me.

As of writing, we've been featured on:

Hacker News
- https://news.ycombinator.com/item?id=42789874
Reddit - r/coolgithubprojects
- https://www.reddit.com/r/coolgithubprojects/comments/1iza4q6/liam_erd_generates_beautiful_and_easytoread_er/
- This subreddit doesn't require karma and had a good response with 7 upvotes and 1K views
daily.dev
- https://dly.to/D8CwMwsw2Jb
- Unexpectedly good response with 211 upvotes, 2.5K views, 11 comments, generating our first +100 stars in the Japanese region
Gitroom
- https://gitlibrary.club/databases/1
CodeTriage
- https://www.codetriage.com/liam-hq/liam
Up For Grabs
- https://up-for-grabs.net/#/filters?names=563
- Not many, but generates continuous traffic
freestuff.dev
- https://freestuff.dev/alternative/liam-erd
Open Hub
- https://openhub.net/p/liam
G2
- https://www.g2.com/products/liam-erd
Dev Hunt
- https://devhunt.org/tool/liam-erd
- Paid, but they promoted us several times on X during the featured period
It's launched!
- https://itslaunched.com/product/liam-erd

To get featured in React Flow's Showcase, we mentioned them several times on X and posted about our blog article on X. Thankfully, they featured us quickly.

https://reactflow.dev/showcase

[Column] How We Researched Potential Promotional Sites

While still exploring, here's how we researched:

Used Ahrefs to check backlinks of competitors and similar products, adding sites that featured those products to our list of candidates
Searched directly using ChatGPT and Google
Sometimes found sites by chance during daily checks of Hacker News, Reddit, etc.
- For example, we found It's launched! through Hacker News' Show HN
Sometimes found sites through a chain of discoveries:
1. The Star History article mentioned earlier was originally a cross-post from this dev.to article
2. Read all articles by Livecycle on dev.to
3. Learned about Dev Hunt³ and added it to our candidates
4. Dev Hunt founder John Rush is a serial entrepreneur who creates products solving challenges like ours, and builds more products with his existing ones
5. Researched several of his products and added some to our candidates. Among them, https://osssoftware.org/ was particularly interesting in its small-scale approach

Introducing at Tech Conferences

So far only in Japan, but team members have introduced Liam ERD at several conferences.

Example: Findy Tools 1st Anniversary Celebration

Adding to GitHub awesome-lists Repositories

We submitted pull requests to relevant 'awesome list' repositories related to Liam ERD. The following repositories merged our requests:

Approaching Influencers

We directly reached out to influencers who introduce OSS projects:

Celebrating Milestones

We posted celebratory messages on X when we reached milestone numbers of stars:

200 stars
- https://x.com/liam_app/status/1882291797399101882
500 stars
- https://x.com/liam_app/status/1888786665231245525
1,000 stars
- https://x.com/liam_app/status/1896390308319080924
3,000 stars
- https://x.com/liam_app/status/1910494502873424353

Most Effective Strategies

daily.dev and X posts by influencers were particularly effective.

The most effective was @GithubProjects' X post in early April. This resulted in a #2 daily ranking on GitHub Trending, creating the following positive cycle:

Featured on GitHub Trending
Massive increase in Liam repository traffic and stars
Continued featuring on GitHub Trending

At this stage, we noticed an increase in spontaneous sharing without our involvement.

It's fair to say that "to increase stars, aim to be featured on GitHub Trending."

🔗 https://www.star-history.com/#liam-hq/liam&Date

Conclusion

I've shared the strategies we implemented to gain 3,000 GitHub stars for the Liam repository.

As a software engineer, I initially hesitated about actively promoting OSS, but changed my mindset, recognizing that good products don't always spread organically.

I'm truly grateful for the Star History article mentioned earlier. Fortunately, I had the opportunity to directly express my gratitude to the author, Zevi Reinitz⁴.

This article is just one example, so I can't guarantee that following these exact steps will lead to success. I hope it serves as a useful reference for someone.

Visualizing Mastodon’s Data Schema with Liam ERD

Takashi Masuda — Wed, 09 Apr 2025 07:27:15 +0000

This post was originally written by Takafumi Endo, CEO of ROUTE06, Inc., and published on our company blog.

Mastodon stands as a prime example of how a decentralized social network can thrive under open-source principles. Rather than replicating the centralized models seen on major platforms, it empowers individuals to create their own servers—known as “instances”—all while remaining interconnected. This design ensures that each community can maintain its autonomy while still sharing a broader network.

The Mastodon Annual Report 2023, the latest annual report available at the time of writing, offers concrete data to support this growth. Over the past year, registered accounts rose by 38.5% to reach 8.8 million, and monthly active users totaled 1.56 million across 9,800 servers. Donations increased by 65%, thanks in part to community funding drives and a notable $100,000 contribution from Sujitech. On the product side, Mastodon introduced improvements in user onboarding, enhanced search functionality, and new mobile app features—leading to 1.87 million combined downloads on iOS and Android in 2023.

A key attraction of Mastodon is its “federated” design, which embraces a wide variety of communities. Each instance sets its own rules and fosters a distinct culture, giving users the freedom to join whichever environment resonates most with them—rather than adapting to a one-size-fits-all platform. In an era dominated by large tech companies, Mastodon’s personalized approach feels refreshingly different. Server administrators actively shape their local environment, and users themselves decide how they want to engage with others. This dynamic reflects the best qualities of the open-source mindset, where technology evolves through collaboration and direct user influence.

Looking ahead, Mastodon offers valuable insights into the future of social media. Its example shows how technological innovation and collective energy can come together to form a platform that is both cutting-edge and inclusive. Instead of entrusting everything to a single tech giant, people can build and sustain their own communities—and in doing so, shape the online spaces that matter to them most.

Demystifying Mastodon’s Data with Liam ERD

Our main goal with Liam ERD is to make complex databases easier to understand. Relying solely on SQL files and text-based documentation can quickly become confusing, so Liam ERD automatically generates visual diagrams of tables and relationships. This means you don’t have to sift through code line by line to see how everything fits together.

Using Liam ERD with Mastodon’s extensive database has been particularly rewarding. Mastodon has about a hundred tables that handle everything from user profiles to timelines and notifications. Rather than feeling overwhelmed, you can filter out irrelevant details and focus on what matters most—giving you a clearer picture of how Mastodon’s features interconnect.

We also made Liam ERD interactive. Instead of a static diagram, you can zoom, pan, and click on elements for more information. This hands-on approach turns the schema into a dynamic learning tool, letting you explore Mastodon’s architecture at your own pace.

While Mastodon’s schema is large, it is built on solid engineering practices: normalization, well-chosen data types, and foreign keys that maintain relational integrity. Seeing these best practices in action—especially at the scale of millions of users—can be far more illuminating than simply reading about them in a textbook.

Because Mastodon is open source, you can watch its schema evolve as new features are introduced or performance is optimized. For developers aiming to specialize in database engineering, Mastodon offers lessons in iterative design and scalability. Our goal with Liam ERD is to support that exploration, helping you dive deep into Mastodon’s data design without losing your way.

Generate ER diagrams effortlessly by entering a schema file URL. Ideal for visualizing, reviewing, and documenting schemas.

liambx.com

How Liam ERD Empowers OSS

Mastodon brings open-source values to life. Its code is fully public, contributions are welcomed worldwide, and the development process is transparent. This openness—together with its responsiveness to user feedback—reflects a strong culture of collaboration that aligns perfectly with Liam ERD’s mission to share knowledge and tools for everyone’s benefit.

Before Liam released their ERD, we asked Mastodon about featuring it, and we were especially pleased when Mastodon agreed to feature their ERD on our site. This underscores Mastodon’s commitment to transparency and its desire to help others learn from its platform. Such a spirit of shared resources and ideas drives supportive, innovative communities.

Mastodon’s user-centric decision-making also shows how agility and responsiveness can flourish when diverse voices have a say. The project tackles bugs, adds features, and evolves efficiently while remaining true to its vision—showcasing how shared ownership fosters sustained progress.

I believe many open-source projects have well-designed databases that often go underappreciated due to their lack of visibility. While text-based documentation can be overwhelming for potential contributors, visualizing the structure makes it much easier for maintainers, developers, and newcomers to understand how everything fits together.

We see Liam ERD as more than a diagramming tool—it’s a way for anyone, including non-developers, to understand a project’s data architecture and see where they can contribute. That approach matches the open-source principle that good ideas can come from anyone.

Making database structures easy to visualize also boosts inclusivity. When relationships are clearly outlined, anyone can jump in and say, “I know how to help.” Mastodon’s steady growth—fueled by contributors of all skill levels—demonstrates the impact of this openness.

liam-hq / liam

Automatically generates beautiful and easy-to-read ER diagrams from your database.

Open Source and Transparent Schemas

Mastodon’s robust database and Liam ERD’s visual approach complement each other naturally, both driven by the belief that sharing and accessibility accelerate progress. By showcasing Mastodon’s underlying structure, we aim to lower barriers and inspire people to learn, collaborate, and innovate.

Clarity and transparency in database design amplify a software project’s potential. Developers work more confidently when they can see a clear map, and open-source communities thrive when people learn from one another’s work. This blend of technical excellence and human-centered collaboration continues to propel software forward for anyone ready to join in.

Tuning Edge Animations in Reactflow for Optimal Performance

Takashi Masuda — Tue, 08 Apr 2025 04:41:16 +0000

This post was originally written by Junki Saito, and published on our company blog.

Liam ERD relies on the incredible capabilities of React Flow to visualize complex data models—like tables and relationships—on the web. We’re genuinely grateful for how React Flow enables rapid development and a beautiful user experience right out of the box. That said, when you’re working with truly massive datasets, you need to make sure your app still runs at top speed. In this post, I’ll walk you through how we tackled performance issues related to edge animations and the steps we took to keep everything running smoothly.

The Challenge: Animated Edges and Performance Bottlenecks

One of the standout features in React Flow is the effortless animation you can apply to edges. By simply toggling animated to true, you get a dashed-line effect that makes relationships feel alive. We loved this feature—it instantly added a sense of dynamic flow.

/**
 * This code is written in Stackblitz
 * @see https://stackblitz.com/edit/sb1-74jfdcqt?file=src%2FApp.tsx
 */
const initialNodes = [
  {
    id: '1',
    type: 'input',
    data: { label: 'Input Node' },
    position: { x: 250, y: 25 },
  },
  {
    id: '2',
    data: { label: 'Default Node' },
    position: { x: 100, y: 125 },
  },
  {
    id: '3',
    type: 'output',
    data: { label: 'Output Node' },
    position: { x: 250, y: 250 },
  },
];

const initialEdges = [
  {
    id: 'e1-2',
    source: '1',
    target: '2',
    animated: true
  },
  {
    id: 'e2-3',
    source: '2',
    target: '3',
    animated: false
  },
];

function App() {
  return (
    <ReactFlow
      nodes={initialNodes}
      edges={initialEdges}
    />
  );
}

The issue arose when we started rendering a huge number of edges for large ERDs, like in Mastodon, which can involve over 100 tables and countless relationships. We noticed a significant dip in responsiveness whenever we hovered over or dragged around nodes—some browsers handled it better than others, but overall, it was clear that the animations were holding back performance.

Identifying the Root Cause: The Culprit - stroke-dasharray

After digging in, we discovered that React Flow’s edge animations rely on the CSS property stroke-dasharray.

While it does create a neat dashed effect, it can seriously push your CPU when you have lots of SVG elements doing the same thing at once. Multiple user reports and bug threads confirmed that stroke-dasharray can be a major slowdown:

In our case, with hundreds of edges animated simultaneously, stroke-dasharray quickly became the main bottleneck.

The Goal: Retain Animation, Regain Smoothness

We still wanted the user experience of visually “flowing” edges. It’s a small detail that helps people see how tables and relationships link together in real time. But we absolutely had to solve the lag problem.

Our Solution: A Custom Animated Edge

To tackle this, we sidestepped the default animation approach in React Flow and crafted our own:

Bypassing React Flow's animated props: We disabled the library’s animated prop on edges to stop using stroke-dasharray.
Eliminating stroke-dasharray entirely: We cut it out for both animations and any default styling.
Implementing a Custom approach: We built a CustomEdge component that animates an SVG object along the edge path, as outlined in the React Flow docs. This gave us the same visual flair—minus the performance hit.

const PARTICLE_COUNT = 6
const ANIMATE_DURATION = 6

type Props = EdgeProps<RelationshipEdgeType>

export const RelationshipEdge: FC<Props> = ({
  data,
}) => {
  const [edgePath] = getBezierPath({
    ...
  })

  return (
    <>
      <BaseEdge
        id={id}
        path={edgePath}
      />
      {/* data.isHighlighted will be true if the edge should be highlighted. */}
      {data?.isHighlighted &&
        [...Array(PARTICLE_COUNT)].map((_, i) => (
          <ellipse
            key={`particle-${i}-${ANIMATE_DURATION}`}
            rx="5"
            ry="1.2"
            fill="url(#myGradient)"
          >
            {/* The <animateMotion> element defines how an element moves along a motion path.  */}
            <animateMotion
              begin={`${i * (ANIMATE_DURATION / PARTICLE_COUNT)}s`}
              dur={`${ANIMATE_DURATION}s`}
              repeatCount="indefinite"
              rotate="auto"
              path={edgePath}
              calcMode="spline"
              keySplines="0.42, 0, 0.58, 1.0"
            />
          </ellipse>
        ))}
    </>
  )
}

By implementing these three approaches, the appearance changed from looking like Before gif image to looking like After gif image.

Before	After

You can see exactly how we did it in our Pull Request:

🚸 Add animated particles to highlighted relationship edges #367

junkisai posted on Dec 24, 2024

Summary

Drawing multiple strokes with stroke-dasharray specified causes performance degradation, especially in Safari browser.

Therefore, I stopped using stroke-dasharray. Additionally, since stroke-dasharray is also used when 'animated' is set to true in Edge, I made sure that 'animated' is always set to false.

Furthermore, since setting 'animated' to false made it difficult to visually distinguish the flow of Edges, I used animateMotion instead to express Edge animations.

Before	After

View on GitHub

Results: Measuring the Improvement

We ran before-and-after tests using Chrome DevTools to measure frame drops. Our test steps:

Hover over the “Accounts” table node in Mastodon’s ERD: Mastodon’s schema.rb
Wait 3 seconds, then remove the hover.

Demonstration of actions for comparison.

Findings:

Before: We saw consistent frame drops of around 5 frames at a time.
After: Frame drops went down to 2–3 frames at a time.

Not surprisingly, our changes made the overall experience smoother and more responsive—no more choppy dragging or sluggish hover states.

Optimizing React Flow for Scalable Products

By digging into React Flow’s edge animation logic and crafting a custom approach, we found a sweet spot between visual polish and raw performance. Disabling stroke-dasharray and opting for a more controlled animation model let us keep the dynamic look of animated edges while ensuring that our largest ERDs run like butter. We hope this helps anyone else wrestling with performance challenges on React Flow—you can absolutely keep those animations, as long as you give them some thoughtful tweaks.

Liam ERD required a highly scalable node-based engine to efficiently render large, interconnected schemas. That drove our choice of React Flow, which effortlessly manages 100+ tables and hundreds of edges while allowing the fine-grained control we need for selective highlighting and custom animations. Since Liam ERD relies on type-safe schema transformations, React Flow’s robust TypeScript support proved indispensable. Its performance tuning capabilities, including the custom animation optimizations we described, make it ideally suited for complex database diagrams. If your product demands large-scale interactive data modeling or specialized node behavior, React Flow is an excellent foundation to build upon.

For more insights into Liam ERD and what we’ve been working on, feel free to check out our introduction post:

Introducing Liam ERD - Liam ERD

Discover Liam ERD, a modern tool for effortlessly auto-generating interactive, readable ER diagrams from database schemas. Perfect for team collaborat...

liambx.com

Liam ERD: Reinventing Database Visualization

Takashi Masuda — Tue, 01 Apr 2025 04:55:00 +0000

This post was originally written by Takafumi Endo, CEO of ROUTE06, Inc., and published on our company blog.

In today’s fast-evolving tech world, advancements in AI, DevOps, and visual development tools have transformed how we approach software development. Yet, one area that often gets overlooked but holds great potential for improvement is data visualization. Even as new frameworks and collaboration tools emerge, quite a few teams are likely finding themselves wrestling with grasping complex data relationships - a challenge that can delay critical decisions and slow down innovation.

Early on, I began to notice that well-organized data structures and effective visualization played a key role in streamlining operations. It made me wonder: As other areas of software development evolve toward more intuitive and visually guided approaches, why does database design still often rely on dense, text-based definitions?

With the release of Liam ERD, we feel fortunate to be at a point where both technology and workplace culture are ready for a fresh, more accessible take on ER diagrams. And as AI becomes ever more central to our daily workflows, addressing the longstanding struggles of visualizing and collaborating on data has never felt more timely.

My Database Journey

I first encountered ER diagrams during my initial year in my career. Though I was a banker, my background in information science led me to help maintain internal tools, including Microsoft Access. At the time, Access felt somewhat daunting, but it turned out to be an incredibly effective way to learn about databases and data relationships.

Using Access’s built-in visual diagrams, instead of jumping straight into SQL, made it much easier for me to understand how multiple tables connected. This insight proved invaluable for tasks like managing a complex investment portfolio system. Had I approached these data structures solely through raw SQL, I imagine the complexity would have been overwhelming.

Over the years, I’ve come to see that starting with visuals rather than code is more than a matter of convenience. It genuinely helps people at any level of expertise recognize the true shape and flow of their data. And in an era when AI continues to expand its influence, having that firm grasp of data structure only becomes more critical.

How ERDs Shaped Our Startup

After transitioning into tech, I founded an e-commerce company where, as product manager, I oversaw everything from advertising analytics to logistics and payment systems—each with its own complexities and stakeholders. While our KPI dashboards in BigQuery and Redshift were essential, ERDs proved invaluable across the board—from designing internal tools and customer-facing features to planning new UI flows.

Mapping out data flows—from advertising metrics to sales data, or inventory status to shipping details—proved invaluable. The ERDs served as a universal language, allowing even non-technical team members to quickly grasp the implications of system changes. More than just documentation, they became a tool that accelerated our decision-making and strengthened alignment, helping everyone understand the ripple effects of any system modifications.

Over time, I began to see ERDs as a valuable tool. By clearly visualizing relationships, they helped reduce misunderstandings and ensured that all teams—from operations to marketing—were aligned in their understanding of our systems.

The Power of Visual Database Design

Reflecting on these experiences, I’ve found that ER diagrams can be at least as beneficial for non-developers as they are for engineers. Key advantages include:

Dramatically Faster Team Onboarding
New hires and external collaborators don’t have to comb through unclear documentation. With ERDs, they can quickly understand how data flows through the system.
Accelerated Product Development
Clear visual blueprints minimize the risk of unseen database issues, allowing teams to iterate on new features with confidence.
Cross-Functional Alignment
Designers, product managers, and engineers can converge on a single view of the data, reducing duplicated work and bridging communication gaps.
Streamlining Success
In fast-paced markets, clarity in data architecture can significantly speed up development cycles and overall responsiveness to change.

Throughout my career, I've consistently advised new product managers to thoroughly understand the ERDs of their products. It's one of the first things I recommend to anyone stepping into a product management role. Understanding your product's data structure isn't just helpful—it's fundamental to effective product leadership.

Introducing Liam ERD

Recognizing how foundational database visualization is, we’re introducing Liam ERD—a product designed to help teams of any size work with data more effectively.

Effortless Clarity for Complex Databases
Liam ERD generates interactive diagrams automatically, empowering you to zoom in, filter, and navigate even large-scale schemas. Rather than wrestle with the complexity, you can focus on meaningful insights.
Seamless Collaboration That Just Works
Use custom filters to direct each person’s attention to relevant tables—be it marketers monitoring campaign data or engineers planning architecture changes. Shareable links make it easy to gather feedback or guide discussions, whether you’re having a brief huddle or an in-depth review.
CI/CD-Ready Documentation
Automated updates ensure your ER diagram always reflects the latest database changes. Compatibility with PostgreSQL, Prisma, and Ruby on Rails helps you maintain consistent schema documentation as you deploy new iterations.

By focusing on the database layer first, Liam ERD aims to provide a platform where creativity and collaboration intersect, ultimately helping teams design more robust solutions.

As our service is just getting started, we're excited about the road ahead. We have many feature enhancements planned and are committed to continuously refining the user experience based on your feedback. We believe that by building together with our users, we can create a tool that truly transforms how teams work with their data.

Visual Design Matters More in the AI Era

In the midst of rapid advances in generative AI, we often ask ourselves: Will ER diagrams still be relevant if AI can interpret data on its own? The reality is that high-quality, structured data remains fundamental, even for the most advanced AI. And it's still humans who must design these data structures and align them with business contexts. A loosely defined or inconsistent schema can undermine the best of predictive models.

As we look ahead, the human role in database design involves strategic thinking—connecting the dots between various departments and ensuring data quality. Liam ERD goes beyond mere data visualization. We're building a platform that supports everything from database structure design to cross-team collaboration, aiming to unlock better data utilization.

In essence, empowering database designers and merging their expertise with cutting-edge computational power represents a meaningful step forward for many organizations. Tools like Liam ERD can help teams stay focused on bigger-picture insights instead of wrestling with messy, opaque schemas, ultimately enhancing the capabilities of the people behind the design.

Shape the Future of Database Design

While beautiful visualization is our starting point, we're building Liam to be much more than just a diagram tool. We believe there's never been a better time to reimagine how teams work with their databases. If you've ever found yourself explaining the same table structure again and again, or if you've struggled to align your data strategy with your team's ambitions, Liam ERD may offer a valuable solution.

Kick off your journey
Run npx @liam-hq/cli init in your terminal or visit our documentation to dive into the details.
Engage with us
Join the conversation on our GitHub Discussions to share ideas, ask questions, and provide feedback.
Explore our roadmap
See what’s next on our roadmap and help shape the future of database design

Even as development tools become more sophisticated, there's no substitute for clarity and alignment in the way we work with data. Starting with visualization and expanding into collaboration features, we're creating a platform that will transform how teams design and evolve their databases. We hope you'll join us in reimagining how ER diagrams can empower teams of all sizes—and together, we can help shape a more collaborative, data-driven future.

Liam ERD

Automatically generates beautiful and easy-to-read ER diagrams from your database.

liambx.com

liam-hq / liam

Automatically generates beautiful and easy-to-read ER diagrams from your database.

Published an OSS Repository That Continuously Stores GitHub Repository Metrics Using GAS

Takashi Masuda — Fri, 21 Mar 2025 02:17:55 +0000

Hey Devs!

Previously, I explored how we can monitor OSS activity metrics based on GitHub repository data.

Consideration of OSS Activity Metrics Based on GitHub Repository Data

Takashi Masuda for ROUTE06, Inc. ・ Dec 16 '24

#ospo #opensource #github #marketing

Building on that, I implemented a Google Apps Script (GAS) that continuously stores traffic data from GitHub repositories.

Continuously Storing Traffic Data of Multiple GitHub Repositories Using GAS

Takashi Masuda for ROUTE06, Inc. ・ Dec 23 '24

#github #googlesheets #googleappsscript

This time, while adding GAS functions to store other metrics, I have published the repository as OSS. I’d like to take this opportunity to introduce it once again.

Published Repository
Stored Metrics
- 1. Stargazers
  - Objectives
  - Issues
  - Solution
- 2. Traffic views
  - Objectives
  - Issues
  - Solution
- 3. Traffic clones
  - Objectives
  - Issues
  - Solution
- 4. Top referral sources
  - Objectives
  - Issues
  - Solution
Conclusion

Published Repository

The published repository is available here.

route06inc / ospo-google-apps-script

OSPO Google Apps Script by ROUTE06, Inc.

The initial setup is summarized in the README. I will focus mainly on why I decided to store these metrics.

Stored Metrics

1. Stargazers

Objectives

To understand the daily increase in stars.
To correlate the days with star increases with possible reasons or initiatives.

Issues

On GitHub’s web UI, only the total number of stars and the accounts that starred are visible.
- Example: https://github.com/giselles-ai/giselle/stargazers
With Star History, the counts for nearby days are rounded off, and it does not allow for note-taking.
- Example: https://www.star-history.com/#giselles-ai/giselle&Date

Solution

I implemented the function updateSheetWithStargazers to record who starred on which day.

The graph was created as shown below. The key is to enable “Aggregate” on the X-axis. Except for the colored cells, you can freely add notes.

2. Traffic views

Objectives

To permanently store the numerical data from the Visitors graph in the repository’s Insights > Traffic section.
- https://github.com/[ORG]/[REPO]/graphs/traffic

Issues

The graph does not display data older than two weeks.
The GitHub REST API Get traffic views has the same limitation.

Solution

I implemented the function updateSheetWithLatestTrafficViews that uses this API to store the daily Views and Unique visitors in a spreadsheet.

3. Traffic clones

Objectives

To permanently store the numerical data from the Git clones graph in the repository’s Insights > Traffic section.
- https://github.com/[ORG]/[REPO]/graphs/traffic

Issues

The graph does not display data older than two weeks.
The GitHub REST API Get traffic clones has the same limitation.

Solution

I implemented the function updateSheetWithLatestTrafficClones that uses this API to store the daily Clones and Unique cloners in a spreadsheet.

4. Top referral sources

Objectives

To permanently store the numerical data from the Referring sites graph in the repository’s Insights > Traffic section.
- https://github.com/[ORG]/[REPO]/graphs/traffic

Issues

The graph does not display data older than two weeks.
The GitHub REST API Get top referral sources has the same limitation.
Each value is the total for the past 14 days, which is not very user-friendly.

Solution

I implemented the function updateSheetWithLatestTrafficReferrers using this API to store the daily Referring sites in a spreadsheet.

To create a stacked graph, I made a pivot table of Views and Unique visitors on a separate sheet, along with their respective graphs.

While the issue of each value being a total for the past 14 days remains unsolved, it seems possible to separate them using spreadsheet functions.

Conclusion

I have published the OSS repository https://github.com/route06inc/ospo-google-apps-script that continuously stores GitHub repository metrics using GAS.

This tool is already in daily use on the repositories of Giselle and Liam, and the data is being effectively utilized.

Generate Beautiful and Interactive ER-Diagrams with Liam ERD

Hirotaka Miyagi — Wed, 22 Jan 2025 07:12:16 +0000

We’ve developed a new database design tool called Liam ERD, and it’s finally here! Let us introduce it to you.

https://liambx.com/

TL;DR

We’ve released Liam ERD, a tool that automatically generates ER diagrams to visualize database table structures.
Web Version: For public repositories, you can try prepending https://liambx.com/erd/p/ to the URL of a public schema file. For example, to view Mastodon’s schema: https://liambx.com/erd/p/github.com/mastodon/mastodon/blob/main/db/schema.rb
CLI Version: For private repositories, we also provide a guide for deploying with Prisma + GitHub Actions + Cloudflare Pages.

Why We Built Liam ERD

In software development, ER diagrams (Entity Relationship Diagrams) simplify visualizing and sharing database structures, making communication smoother. They help reduce onboarding costs for new team members, assist non-engineering roles like PdMs or customer support with explanations, and enable data analytics teams to understand table structures without reading product code.

While having ER diagrams as documentation is convenient, manually updating them with spreadsheets or diagramming tools is labor-intensive and prone to errors or omissions. That’s why it’s ideal to auto-generate ER diagrams from schema files committed to the project repository or metadata retrieved from a database connection.

Several tools already offer ER diagram auto-generation. For instance, tools using Mermaid.js or PlantUML generate images, but static images become hard to read for large, complex projects. Tools like SchemaSpy that output in HTML format also exist, but they often require extensive runtime and middleware dependencies, making integration into CI/CD pipelines challenging.

We wanted a CI/CD-friendly, easy-to-set-up, and highly readable ER diagram auto-generation tool—thus, Liam ERD was born.

Features of Liam ERD

Liam ERD has four key features:

Modern and Interactive UI: Supports panning, zooming, filtering, and focusing for better interaction.
High Performance: Works smoothly even with 100+ tables, with fast filtering.
CI/CD Friendly: Easy to set up and deploy, compatible with many hosting services.
Open Source & Community-Driven: Freely modifiable code with new features developed based on community feedback.

Let’s take a closer look at its features with some screenshots!

Liam ERD positions related tables close to each other, avoiding overly complex cardinality lines and ensuring a clean layout. Even for large-scale table structures, it provides excellent readability from the initial view.

Still, large-scale structures can be hard to grasp. Liam ERD highlights related tables and columns when hovering over a table, helping you quickly locate the tables you’re interested in.

Selecting a table brings up a detailed pane from the right, displaying comments on the table and columns, indexes, and a focused ER diagram of related tables.

Other subtle user experiences make it easier to grasp table structures. For example, here’s a link to the open-source social media platform Mastodon’s ER diagram, which includes about 99 tables. Check it out!

https://liambx.com/erd/p/github.com/mastodon/mastodon/blob/main/db/schema.rb

Using Liam ERD for Public Repositories

For public GitHub repositories, it’s incredibly easy to use. Just prepend https://liambx.com/erd/p/ to the URL of your schema file, and Liam ERD will render the ER diagram.

This allows you to view ER diagrams generated from the main branch schema. You can also replace main with a specific commit hash to render the ER diagram at that point in time.

Example: Prisma + GitHub Actions + Cloudflare Pages

While the above is the simplest usage, you might think, "This won’t work for our internal projects!" And you’d be right!

Liam ERD is also available as a CLI tool via npm, enabling you to generate ER diagrams locally or with GitHub Actions and host them easily.

https://www.npmjs.com/package/@liam-hq/cli

Here’s a practical example of deploying with Prisma, GitHub Actions, and Cloudflare Pages. We chose Cloudflare Pages because its Cloudflare Access feature makes it simple to restrict access, such as allowing only internal members.

First, prepare a schema.prisma like this:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

model User {
  id             Int           @id @default(autoincrement())
  email          String        @unique
  username       String
  password       String
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt
  profile        Profile?
  posts          Post[]
  comments       Comment[]
  orders         Order[]
  notifications  Notification[]
}

model Profile {
  id          Int      @id @default(autoincrement())
  userId      Int      @unique
  firstName   String
  lastName    String
  bio         String?
  avatar      String?
  birthDate   DateTime?
  phoneNumber String?
  user        User     @relation(fields: [userId], references: [id])
}

model Post {
  id        Int       @id @default(autoincrement())
  title     String
  content   String
  published Boolean   @default(false)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  authorId  Int
  author    User      @relation(fields: [authorId], references: [id])
  comments  Comment[]
  tags      Tag[]
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  postId    Int
  authorId  Int
  post      Post     @relation(fields: [postId], references: [id])
  author    User     @relation(fields: [authorId], references: [id])
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[]
}

model Product {
  id          Int      @id @default(autoincrement())
  name        String
  description String
  price       Decimal
  stock       Int
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  orderItems  OrderItem[]
  categoryId  Int
  category    Category @relation(fields: [categoryId], references: [id])
}

model Category {
  id       Int       @id @default(autoincrement())
  name     String    @unique
  products Product[]
}

model Order {
  id         Int         @id @default(autoincrement())
  userId     Int
  status     OrderStatus @default(PENDING)
  totalPrice Decimal
  createdAt  DateTime    @default(now())
  updatedAt  DateTime    @updatedAt
  user       User        @relation(fields: [userId], references: [id])
  orderItems OrderItem[]
}

model OrderItem {
  id        Int     @id @default(autoincrement())
  orderId   Int
  productId Int
  quantity  Int
  price     Decimal
  order     Order   @relation(fields: [orderId], references: [id])
  product   Product @relation(fields: [productId], references: [id])
}

model Notification {
  id        Int      @id @default(autoincrement())
  userId    Int
  title     String
  content   String
  read      Boolean  @default(false)
  createdAt DateTime @default(now())
  user      User     @relation(fields: [userId], references: [id])
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

Then, create a Cloudflare Pages project using Wrangler with the following command:

wrangler pages project create prisma-with-cloudflare-pages

Next, add the following GitHub Actions workflow file:

name: prisma-with-cloudflare-pages
on:
  push:
    branches:
      - main
    paths:
      - prisma/schema.prisma

jobs:
  build-and-deploy-erd:
    runs-on: ubuntu-latest

    permissions:
      contents: read
      deployments: write

    steps:
      - uses: actions/checkout@v4
      - name: Generate ER Diagrams
        run: npx @liam-hq/cli erd build --input prisma/schema.prisma --format prisma
      - name: Deploy ERD to Cloudflare Pages
        uses: cloudflare/wrangler-action@v3
        with:
          apiToken: ${{ secrets.CLOUDFLARE_API_TOKEN }}
          accountId: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
          command: pages deploy ./dist --project-name=prisma-with-cloudflare-pages
          gitHubToken: ${{ secrets.GITHUB_TOKEN }}

And that’s it—your ERD is deployed!

https://prisma-with-cloudflare-pages.pages.dev/

Since Liam ERD is built with Vite and React, it should be deployable on most hosting services with ease.

A sample repository used in this example is available here:

https://github.com/liam-hq/liam-erd-samples/tree/main/samples/prisma-with-cloudflare-pages

Currently, we support schema files for Ruby on Rails (schema.rb), Prisma (schema.prisma), and SQL DDL. We’re considering supporting other formats and welcome pull requests!

Our documentation also includes instructions for using Liam ERD with supported ORMs and RDBMS. Check it out: Supported Formats

Future Features

While Liam ERD currently focuses on visualizing table structures, we’re planning to add various database design features:

Enhanced Documentation: Grouping aggregations (similar to tbls viewpoints) and adding comments.
ERD Editing: Adding/editing tables and columns, with migration file generation for changes.
Record Insights: Connect to a database, run simple SQL, and make table structures more accessible.
Cloud Version: Private projects and real-time collaborative editing for teams.

As an open-source project, we need your contributions to make Liam ERD even better! Whether it’s reporting issues, suggesting features, or submitting pull requests, your help is invaluable.

If you like what we’re building, we’d love your support—please give our repository a ⭐️ on GitHub! Your encouragement helps us grow and continue improving.

You can track our roadmap here—feel free to comment or provide feedback!

Try Liam ERD Now

That’s Liam ERD, a tool for effortlessly generating clear, readable ER diagrams. Give it a try!

https://github.com/liam-hq/liam

Tutorial: Building a Collaborative Editing App with Yjs, valtio, and React

Hirotaka Miyagi — Sat, 18 Jan 2025 09:02:07 +0000

This article is an English translation of the following article:

チュートリアル: Yjs, valtio, React で実現する共同編集アプリケーション - ROUTE06 Tech Blog

Yjs is a framework that provides algorithms and data structures for real-time collaborative editing. It can offer experiences where multiple people simultaneously update the same content, similar to Notion and Figma.

Yjs provides shared data types such as Y.Map, Y.Array, and Y.Text, which can be used similarly to JavaScript’s Map and Array. Moreover, any changes made to this data are automatically distributed and synchronized with other clients.

Yjs is an implementation of what is called a Conflict-free Replicated Data Type (CRDT) and is designed so that even if multiple people operate on the data at the same time, no conflicts occur and all clients eventually reach the same state.

Quick Start

Let’s look at a code example where Y.Map is automatically synchronized between clients.

import * as Y from 'yjs'

// Yjs documents are collections of
// shared objects that sync automatically.
const ydoc = new Y.Doc()
// Define a shared Y.Map instance
const ymap = ydoc.getMap()
ymap.set('keyA', 'valueA')

// Create another Yjs document (simulating a remote user)
// and create some conflicting changes
const ydocRemote = new Y.Doc()
const ymapRemote = ydocRemote.getMap()
ymapRemote.set('keyB', 'valueB')

// Merge changes from remote
const update = Y.encodeStateAsUpdate(ydocRemote)
Y.applyUpdate(ydoc, update)

// Observe that the changes have merged
console.log(ymap.toJSON()) // => { keyA: 'valueA', keyB: 'valueB' }

(Quoted from https://docs.yjs.dev/#quick-start)

The central component is the Y.Doc (a Yjs document). A Y.Doc contains multiple shared data types and manages synchronization with other clients. You create a Y.Doc for each client session, and each one holds a unique clientID.

Providers

In the example above, multiple users were simulated on the same machine, but if you actually want to synchronize changes over a network, you use providers. Yjs itself is not dependent on any particular network protocol, allowing you to freely switch among various providers or use multiple at the same time. Here are some examples of providers:

y-websocket: Implements a client-server model that sends and receives changes via WebSocket. It’s useful if you want to persist Yjs documents on the server or enable request authentication.
y-webrtc: Synchronizes via peer-to-peer with WebRTC, making fully distributed applications possible.
y-indexeddb: Uses an IndexedDB database to store shared data in the browser, enabling offline editing.

For other providers, please see the official documentation: https://docs.yjs.dev/ecosystem/connection-provider

Editor Bindings

If you’re building an application that involves text editing, you’ll likely use an editor framework like ProseMirror or Quill. Yjs supports many common editor frameworks and can be used as a plugin or extension. In most common use cases, you won’t need to directly manipulate Yjs’s shared data types.

Check out the following list of editor frameworks supported by Yjs: https://docs.yjs.dev/ecosystem/editor-bindings

Therefore, if you are using one of these editor frameworks, you’d be better off using a corresponding plugin. However, if you’re building a complex GUI for an application such as a design editor like Figma, there are plenty of cases where you might develop the editor UI from scratch.

In this tutorial, we’ll introduce how to build a collaborative editing application connected to Yjs using React as the UI library, specifically demonstrating an example without using editor bindings.

Demo

In this tutorial, we’ll build a Kanban-style task management application. First, let’s show the final product.

Fork on StackBlitz, open the preview in two tabs, and move your cursor over them!

We’ll implement the following features:

Adding and editing tasks
Managing statuses: To Do / In Progress / Done
Drag-and-drop reordering
Multiple people can operate on it at the same time, and changes are reflected in real time
Displaying other participants’ cursors

You can check the entire app in the following repository:

route06 / yjs-kanban-tutorial

yjs-kanban-tutorial

This repository is sample code for the above.
If you find any inadequacies in the tutorial, we would appreciate it if you could let us know at https://github.com/route06/yjs-kanban-tutorial/issues/new .

このリポジトリは上記のサンプルコードです。
チュートリアルの内容に不備があった場合、 https://github.com/route06/yjs-kanban-tutorial/issues/new でお知らせ下さると幸いです。

View on GitHub

Using valtio as a Middle Layer Between Yjs and React

Because we’re not using editor bindings this time, we’ll be directly operating on Yjs’s shared data types. However, if React components access these shared data types directly, it will become tightly coupled with Yjs, making testing difficult. Moreover, you might end up mixing React state management and another data flow in ways that can easily introduce bugs.

Hence, we’ll use valtio, a simple proxy-based state management library. By combining valtio with valtio-yjs, you can synchronize valtio’s state with Yjs’s shared data types. This lets React components interact with Yjs data via valtio’s state, making state management much simpler.

Other Libraries Used

In this tutorial, we will also use the following libraries:

TypeScript: A superset of JavaScript that supports static typing. It improves code quality and reduces development-time errors.
Vite: A build tool that provides a fast development environment. Lightweight, easy to use, and supports hot reloading.
CSS Modules: A mechanism for achieving scoped CSS. Helps you manage styles per component and avoid collisions.
nanoid: A fast library for generating unique IDs. In this tutorial, it’s used to generate task IDs.

Project Setup

That was a lot of background. Let’s jump right in! We’ll use Vite’s React and TypeScript template to create a new project called yjs-kanban-tutorial.

npm create vite@latest yjs-kanban-tutorial -- --template react-ts

First, set up the type definitions for tasks. In this tutorial, tasks have fields for status, value, and order.

// src/types.ts
export type TaskStatus = "To Do" | "In Progress" | "Done";

export interface Task {
  id: string;
  status: TaskStatus;
  value: string;
  order: number;
}

Next, let’s build out the UI at once. We’ll add these three components:

TaskAddButton
TaskItem
TaskColumn

Since they’re just for styling at this point, we won’t go into detail. Please copy and paste the code as is.

TaskAddButton

// src/TaskAddButton.tsx
import type { FC } from "react";
import styles from "./TaskAddButton.module.css";

export const TaskAddButton: FC = () => {
  return (
    <button type="button" className={styles.button}>
      + Add
    </button>
  );
};

/* src/TaskAddButton.module.css */
.button {
  width: 100%;
  text-align: left;
  font: inherit;
  cursor: pointer;
  border: none;
  border-radius: var(--rounded-sm);
  padding: 0.375rem;
  background-color: var(--zinc-950);
  color: var(--zinc-400);
  font-size: 0.75rem;
  transition: background-color 300ms cubic-bezier(0.4, 0, 0.2, 1);
}

.button:hover {
  background-color: var(--zinc-900);
}

.button:focus-visible {
  outline: 1px solid var(--zinc-400);
  border-radius: var(--rounded-sm);
}

TaskItem

// src/TaskItem.tsx
import type { FC } from "react";
import styles from "./TaskItem.module.css";
import type { Task } from "./types";

interface Props {
  task: Task;
}

export const TaskItem: FC<Props> = ({ task }) => {
  return (
    <li className={styles.listitem}>
      <button type="button" className={styles.button}>
        <svg
          width="24"
          height="24"
          viewBox="6 0 12 24"
          fill="none"
          stroke="currentColor"
          strokeWidth="2"
          strokeLinecap="round"
          strokeLinejoin="round"
        >
          <title>Drag</title>
          <circle cx="9" cy="12" r="1" />
          <circle cx="9" cy="5" r="1" />
          <circle cx="9" cy="19" r="1" />
          <circle cx="15" cy="12" r="1" />
          <circle cx="15" cy="5" r="1" />
          <circle cx="15" cy="19" r="1" />
        </svg>
      </button>
      <input className={styles.input} value={task.value} />
    </li>
  );
};

/* src/TaskItem.module.css */
.listitem {
  background-color: var(--zinc-900);
  border-radius: 0.25rem;
  padding: 0.5rem 0.25rem;
  list-style: none;
  display: flex;
  gap: 0.2rem;
  transition: opacity 150ms cubic-bezier(0.4, 0, 0.2, 1);
}

.input {
  width: 100%;
  background-color: var(--zinc-900);
  border: 0;
  color: var(--zinc-100);
  padding: 0.25rem;
}

.input:focus-visible {
  outline: 1px solid var(--zinc-400);
  border-radius: var(--rounded-xs);
}

.button {
  cursor: move;
  border: none;
  background-color: transparent;
  padding: 0;
  color: var(--zinc-400);
}

.button:focus-visible {
  outline: 1px solid var(--zinc-400);
  border-radius: var(--rounded-xs);
}

TaskColumn

// src/TaskColumn.tsx
import type { FC } from "react";
import { TaskAddButton } from "./TaskAddButton";
import styles from "./TaskColumn.module.css";
import { TaskItem } from "./TaskItem";
import type { Task, TaskStatus } from "./types";

interface Props {
  status: TaskStatus;
}

export const TaskColumn: FC<Props> = ({ status }) => {
  // TODO
  const tasks: Task[] = [
    { id: "1", status, value: "Task 1", order: 1 },
    { id: "2", status, value: "Task 2", order: 2 },
    { id: "3", status, value: "Task 3", order: 3 },
  ];

  return (
    <div className={styles.wrapper}>
      <h2 className={styles.heading}>{status}</h2>
      <ul className={styles.list}>
        {tasks.map((task) => (
          <TaskItem key={task.id} task={task} />
        ))}
      </ul>
      <TaskAddButton />
    </div>
  );
};

/* src/TaskColumn.module.css */
.wrapper {
  background-color: var(--zinc-950);
  border-radius: var(--rounded-md);
  border: 1px solid var(--zinc-800);
  padding: 0.5rem;
}

.heading {
  font-size: 1rem;
  font-weight: 400;
  margin-bottom: 1rem;
}

.list {
  list-style-type: none;
  padding: 0;
  display: flex;
  flex-direction: column;
  gap: 0.2rem;
}

Next, replace the contents of App.tsx, main.tsx, App.css, and index.css with the following. Please delete the existing code.

// App.tsx
import type { FC } from "react";
import styles from "./App.module.css";
import { TaskColumn } from "./TaskColumn";

const App: FC = () => {
  return (
    <div className={styles.wrapper}>
      <h1 className={styles.heading}>Projects / Board</h1>
      <div className={styles.grid}>
        <TaskColumn status="To Do" />
        <TaskColumn status="In Progress" />
        <TaskColumn status="Done" />
      </div>
    </div>
  );
};

export default App;

// src/main.tsx
import React from "react";
import reactDom from "react-dom/client";
import App from "./App.tsx";
import "./index.css";

const root = document.getElementById("root");
if (!root) {
  throw new Error("Root element not found");
}

reactDom.createRoot(root).render(
  <React.StrictMode>
    <App />
  </React.StrictMode>
);

/* src/App.module.css */
.wrapper {
  margin: auto;
  padding: 0.5rem;
}

.heading {
  font-size: 1rem;
  font-weight: 400;
  padding-bottom: 0.5rem;
}

.grid {
  display: grid;
  grid-template-columns: repeat(3, 1fr);
  gap: 0.5rem;
}

/* src/index.css */
:root {
  font-family: "Inter", sans-serif;

  --zinc-950: #09090b;
  --zinc-900: #18181b;
  --zinc-800: #27272a;
  --zinc-700: #3f3f46;
  --zinc-400: #a1a1aa;
  --zinc-100: #f4f4f5;
  --rounded-md: 0.375rem;
  --rounded-sm: 0.25rem;
  --rounded-xs: 0.125rem;
}

html {
  background-color: var(--zinc-900);
}

body {
  color: var(--zinc-100);
}

* {
  margin: 0;
  box-sizing: border-box;
  scrollbar-width: thin;
}

Notice that we changed App.css to App.module.css.

When you run npm run dev at this point, the screen should look like this:

You can check out the current state of this setup in the section-1-setup-project branch.

Implementing Task Addition

Let’s enable the addition of tasks. We’ll install valtio for state management and nanoid for generating IDs.

npm install valtio nanoid

We’ll use valtio to manage the state of the tasks. Add a file called taskStore.ts as follows:

// src/taskStore.ts
import { proxy, useSnapshot } from "valtio";
import type { Task, TaskStatus } from "./types";

interface TaskStore {
  [taskId: string]: Task;
}

export const filteredTasks = (
  status: TaskStatus,
  taskStore: TaskStore
): Task[] => Object.values(taskStore).filter((task) => task.status === status);

export const taskStore = proxy<TaskStore>({});

export const useTasks = () => useSnapshot(taskStore);

The type TaskStore defines an object using the taskId as the key and Task as the value. We’ve also provided a filteredTasks() function that returns an array of tasks.

You may wonder, “Why not define TaskStore as an array?” This is to make searching and editing tasks simpler, as we’ll explain later.

proxy() is the foundation of valtio; it creates a proxy object that tracks changes to the object passed to it.

useSnapshot() is a custom hook for using valtio’s proxy object from React components, which returns a read-only snapshot. React components automatically re-render when the object changes.

Let’s see how to use these while we add some functionality. First, let’s make sure we can display tasks using useSnapshot(). We’ll update TaskColumn.tsx so that we can get tasks:

// src/TaskColumn.tsx
import type { FC } from "react";
import { TaskAddButton } from "./TaskAddButton";
import styles from "./TaskColumn.module.css";
import { TaskItem } from "./TaskItem";
-import type { Task, TaskStatus } from "./types";
+import type { TaskStatus } from "./types";
+import { filteredTasks, useTasks } from "./taskStore";

interface Props {
  status: TaskStatus;
}

export const TaskColumn: FC<Props> = ({ status }) => {
- // TODO
- const tasks: Task[] = [
-   { id: "1", status, value: "Task 1", order: 1 },
-   { id: "2", status, value: "Task 2", order: 2 },
-   { id: "3", status, value: "Task 3", order: 3 },
- ];
+ const snapshot = useTasks();
+ const tasks = filteredTasks(status, snapshot);

  return (
    <div className={styles.wrapper}>
      <h2 className={styles.heading}>{status}</h2>
      <ul className={styles.list}>
        {tasks.map((task) => (
          <TaskItem key={task.id} task={task} />
        ))}
      </ul>
      <TaskAddButton />
    </div>
  );
};

We’ve replaced the dummy data so that we now get data from the read-only snapshot returned by useSnapshot.

However, because taskStore has no data right now, nothing will display. Let’s make it possible to add new tasks:

// src/taskStore.ts
import { proxy, useSnapshot } from "valtio";
import type { Task, TaskStatus } from "./types";
+import { nanoid } from "nanoid";

interface TaskStore {
  [taskId: string]: Task;
}

export const filteredTasks = (status: TaskStatus, taskStore: TaskStore): Task[] =>
  Object.values(taskStore).filter((task) => task.status === status)

export const taskStore = proxy<TaskStore>({});

export const useTasks = () => useSnapshot(taskStore);

+export const addTask = (status: TaskStatus) => {
+  const order = 0; // dummy
+  const id = nanoid();
+  taskStore[id] = {
+    id,
+    status,
+    value: "",
+    order,
+  };
+};

We’ve added an addTask() function. The order value will be computed when we implement drag-and-drop, so for now we’ll use a placeholder.

Next, let’s make TaskAddButton use addTask():

// src/TaskAddButton.tsx
import type { FC } from "react";
import styles from "./TaskAddButton.module.css";
+import type { TaskStatus } from "./types";
+import { addTask } from "./taskStore";
+
+interface Props {
+  status: TaskStatus;
+}

-export const TaskAddButton: FC = () => {
+export const TaskAddButton: FC<Props> = ({ status }) => {
  return (
-    <button type="button" className={styles.button}>
+    <button type="button" className={styles.button} onClick={() => addTask(status)}>
      + Add
    </button>
  );
};

// src/TaskColumn.tsx
import type { FC } from "react";
import { TaskAddButton } from "./TaskAddButton";
import styles from "./TaskColumn.module.css";
import { TaskItem } from "./TaskItem";
import type { TaskStatus } from "./types";
import { filteredTasks, useTasks } from "./taskStore";

interface Props {
  status: TaskStatus;
}

export const TaskColumn: FC<Props> = ({ status }) => {
  const snapshot = useTasks();
  const tasks = filteredTasks(status, snapshot);

  return (
    <div className={styles.wrapper}>
      <h2 className={styles.heading}>{status}</h2>
      <ul className={styles.list}>
        {tasks.map((task) => (
          <TaskItem key={task.id} task={task} />
        ))}
      </ul>
-     <TaskAddButton />
+     <TaskAddButton status={status} />
    </div>
  );
};

With this in place, clicking the “+ Add” button on the screen lets us add tasks.

When working with valtio, it’s good to keep in mind:

Displaying data: Use useSnapshot() to retrieve a read-only object.
Changing data: Directly modify the proxy object created by proxy().

You can check the current state of things in the section-2-add-task branch.

Synchronizing Data Across Multiple Clients

We’ve only made it possible to add tasks so far, but even at this stage we can enable collaborative editing. Let’s set up data synchronization across multiple clients. Install the necessary libraries:

npm install yjs valtio-yjs@0.5.1 y-websocket

Warning: The latest version of valtio-yjs, v0.6.0, seems to require valtio at version v2.0.0-rc.0 or later. Since v2 is still an RC release, this tutorial will use v0.5.1.

To synchronize data via a network in Yjs, you need a provider, as mentioned earlier. This time, we’ll be using y-websocket. The client will connect to a single endpoint over WebSocket. The y-websocket package includes a server with an in-memory database, making it easy to persist data as well.

First, let’s enable the WebSocket server. Add an npm script to your package.json:

// package.json
{
  "scripts": {
    "dev-websocket": "y-websocket --port 1234"
  }
}

Then run npm run dev-websocket:

$ npm run dev-websocket

> yjs-kanban-tutorial@0.0.0 dev-websocket
> y-websocket --port 1234

running at 'localhost' on port 1234

Now add yjs/yjs.ts to manage the Yjs document, and yjs/useSyncToYjsEffect() to synchronize taskStore via Yjs:

// src/yjs/yjs.ts
import { WebsocketProvider } from "y-websocket";
import { Doc } from "yjs";

const ydoc = new Doc();
export const ymap = ydoc.getMap("taskStore.v1");
new WebsocketProvider("ws://localhost:1234", "yjs-kanban-tutorial", ydoc);

// src/yjs/useSyncToYjsEffect.ts
import { useEffect } from "react";
import { bind } from "valtio-yjs";

import { taskStore } from "../taskStore";
import { ymap } from "./yjs";

export const useSyncToYjsEffect = () => {
  useEffect(() => {
    const unbind = bind(taskStore, ymap);
    return () => {
      unbind();
    };
  }, []);
};

Let’s take a closer look. A Y.Map named "taskStore.v1" is created inside the Y.Doc instance. You can name it arbitrarily.

WebsocketProvider takes the endpoint, the room name, and the Y.Doc, in that order. The room name can also be any name you like, but in most real-world applications, you’ll likely have multiple rooms and let users pick the room to join, generally by specifying an identifiable value like an ID.

In useSyncToYjsEffect(), bind() from valtio-yjs is called inside a React useEffect hook, passing in the valtio proxy object (taskStore) and the Y.Map (ymap).

This means that any operation on your screen is sent from taskStore → Y.Map → WebSocket to other clients, while changes from other clients arrive via WebSocket → Y.Map → taskStore and update your screen.

Use this useSyncToYjsEffect() in App.tsx:

// src/App.tsx
import type { FC } from "react";
import styles from "./App.module.css";
import { TaskColumn } from "./TaskColumn";
+import { useSyncToYjsEffect } from "./yjs/useSyncToYjsEffect";

const App: FC = () => {
+  useSyncToYjsEffect();

  return (
    <div className={styles.wrapper}>
      <h1 className={styles.heading}>Projects / Board</h1>
      <div className={styles.grid}>
        <TaskColumn status="To Do" />
        <TaskColumn status="In Progress" />
        <TaskColumn status="Done" />
      </div>
    </div>
  );
};

export default App;

Now, when you open two browser tabs and add tasks on one tab, they will be instantly reflected in the other tab in real time!

Check section-3-bind-yjs for the current state of this setup.

Editing Task Contents

We can now synchronize the addition of tasks, but you may notice that you can’t edit the text field of a task yet. Let’s add editing functionality!

// src/taskStore.ts
import { nanoid } from "nanoid";
import { proxy, useSnapshot } from "valtio";
import type { Task, TaskStatus } from "./types";

interface TaskStore {
  [taskId: string]: Task;
}

export const filteredTasks = (status: TaskStatus, taskStore: TaskStore): Task[] =>
  Object.values(taskStore).filter((task) => task.status === status);

export const taskStore = proxy<TaskStore>({});

export const useTasks = () => useSnapshot(taskStore);

export const addTask = (status: TaskStatus) => {
  const order = 0; // dummy
  const id = nanoid();
  taskStore[id] = {
    id,
    status,
    value: "",
    order,
  };
};

+export const updateTask = (id: string, value: string) => {
+  const task = taskStore[id];
+  if (task) {
+    task.value = value;
+  }
+};

// src/TaskItem.tsx
-import type { FC } from "react";
+import { type ChangeEvent, type FC, useCallback } from "react";
import styles from "./TaskItem.module.css";
+import { updateTask } from "./taskStore";
import type { Task } from "./types";

interface Props {
  task: Task;
}

export const TaskItem: FC<Props> = ({ task }) => {
+  const handleChange = useCallback(
+    (event: ChangeEvent<HTMLInputElement>) => {
+      updateTask(task.id, event.target.value);
+    },
+    [task],
+  );

  return (
    <li className={styles.listitem}>
      <button type="button" className={styles.button}>
        <svg
          width="24"
          height="24"
          viewBox="6 0 12 24"
          fill="none"
          stroke="currentColor"
          strokeWidth="2"
          strokeLinecap="round"
          strokeLinejoin="round"
        >
          <title>Drag</title>
          <circle cx="9" cy="12" r="1" />
          <circle cx="9" cy="5" r="1" />
          <circle cx="9" cy="19" r="1" />
          <circle cx="15" cy="12" r="1" />
          <circle cx="15" cy="5" r="1" />
          <circle cx="15" cy="19" r="1" />
        </svg>
      </button>
-      <input className={styles.input} value={task.value} />
+      <input className={styles.input} value={task.value} onChange={handleChange} />
    </li>
  );
};

You can now edit the text and have those edits synced via Yjs!

To edit a task, we need to look it up; thanks to the data structure being an object, retrieving it with taskStore[id] is straightforward. If we had used an array, we’d need to do something like taskStore.find(task => task.id === id).

You may feel some friction about directly editing state—task.name = name—as in many React patterns you avoid mutating state directly. However, this is the way valtio works, and there’s a reason for it.

Every change made to a Yjs shared data type is grouped into what’s called a transaction. Whenever you make a change, Yjs sends the update to other clients. Minimizing the scope of these changes helps reduce message size, so it’s important to keep changes as small as possible.

Check section-4-update-task to see the current state.

Reordering Tasks with Drag & Drop

Let’s implement a more complex feature: reordering tasks by drag-and-drop.

First, we’ll implement a way to compute the order field that we added to the Task type earlier:

// src/taskStore.ts
import { proxy, useSnapshot } from "valtio";
import type { Task, TaskStatus } from "./types";
import { nanoid } from "nanoid";

interface TaskStore {
  [taskId: string]: Task;
}

+const computeOrder = (prevId?: string, nextId?: string): number => {
+  const prevOrder = taskStore[prevId ?? ""]?.order ?? 0;
+  const nextOrder = nextId ? taskStore[nextId].order : 1;
+
+  return (prevOrder + nextOrder) / 2;
+};

export const filteredTasks = (status: TaskStatus, taskStore: TaskStore) =>
-  Object.values(taskStore).filter((task) => task.status === status)
+  Object.values(taskStore)
+    .filter((task) => task.status === status)
+    .sort((a, b) => a.order - b.order);

export const taskStore = proxy<TaskStore>({});

export const useTasks = () => useSnapshot(taskStore);

export const addTask = (status: TaskStatus) => {
-  const order = 0; // dummy
+  const tasks = filteredTasks(status, taskStore);
+  const lastTask = tasks[tasks.length - 1];
+  const order = computeOrder(lastTask?.id)
  const id = nanoid();
  taskStore[id] = {
    id,
    status,
    value: "",
    order,
  };
};

export const updateTask = (id: string, value: string) => {
  const task = taskStore[id];
  if (task) {
    task.value = value;
  }
};

There are several ways to implement list reordering, but we’ll do a simple version of Fractional Indexing here. In short:

All order values are floating numbers such that 0 < index < 1.
We set the position by computing the average of the order values on the elements before and after the new position.

That’s what computeOrder() does.

Fractional indexing allows you to specify a position without touching existing elements’ indices, which is handy. However, doing many reorderings in a row can approach floating-point limits and lead to collisions, in which case you’d have to recalculate all positions. We won’t implement that for simplicity.

We also apply computeOrder() in addTask(). We’ll treat any new tasks as if they’re added at the bottom of the list by taking the average of the last task’s order value and 1.

Next, let’s implement moveTask() to move a task using computeOrder():

// src/taskStore.ts
import { proxy, useSnapshot } from "valtio";
import type { Task, TaskStatus } from "./types";
import { nanoid } from "nanoid";

interface TaskStore {
  [taskId: string]: Task;
}

const computeOrder = (prevId?: string, nextId?: string): number => {
  const prevOrder = taskStore[prevId ?? ""]?.order ?? 0;
  const nextOrder = nextId ? taskStore[nextId].order : 1;

  return (prevOrder + nextOrder) / 2;
};

export const filteredTasks = (status: TaskStatus, taskStore: TaskStore): Task[] =>
  Object.values(taskStore)
    .filter((task) => task.status === status)
    .sort((a, b) => a.order - b.order);

export const taskStore = proxy<TaskStore>({});

export const useTasks = () => useSnapshot(taskStore);

export const addTask = (status: TaskStatus) => {
  const tasks = filteredTasks(status, taskStore);
  const lastTask = tasks[tasks.length - 1];
  const order = computeOrder(lastTask?.id)
  const id = nanoid();
  taskStore[id] = {
    id,
    status,
    value: "",
    order,
  };
};

export const updateTask = (id: string, value: string) => {
  const task = taskStore[id];
  if (task) {
    task.value = value;
  }
};
+
+export const moveTask = (id: string, status: TaskStatus, prevId?: string, nextId?: string) => {
+  const order = computeOrder(prevId, nextId);
+  const task = taskStore[id];
+  if (task) {
+    task.status = status;
+    task.order = order;
+  }
+};

We’ll use dnd kit for drag-and-drop. Let’s install it:

npm install @dnd-kit/core

We’ll skip some details of how to use @dnd-kit/core. First, we’ll implement a handler function in the onDragEnd of DndContext. This handler calls moveTask() to drop the task into its new position.

// src/dnd/DndProvider.tsx
import { DndContext, type DragEndEvent } from "@dnd-kit/core";
import { type FC, type PropsWithChildren, useCallback } from "react";
import { moveTask } from "../taskStore";

export const DndProvider: FC<PropsWithChildren> = ({ children }) => {
  const handleDragEnd = useCallback((event: DragEndEvent) => {
    if (!event.over) {
      return;
    }
    const data = event.over.data.current;
    if (!data?.status) {
      return;
    }

    moveTask(String(event.active.id), data.status, data?.prevId, data?.nextId);
  }, []);

  return <DndContext onDragEnd={handleDragEnd}>{children}</DndContext>;
};

Next, we’ll enable dragging in TaskItem using useDraggable.

// src/TaskItem.tsx
+import { useDraggable } from "@dnd-kit/core";
+import { CSS } from "@dnd-kit/utilities";
import { type ChangeEvent, type FC, useCallback } from "react";
import styles from "./TaskItem.module.css";
import { updateTask } from "./taskStore";
import type { Task } from "./types";

interface Props {
  task: Task;
}

export const TaskItem: FC<Props> = ({ task }) => {
+  const { attributes, listeners, setNodeRef, transform, isDragging } = useDraggable({
+    id: task.id,
+  });
+  const style = {
+    transform: CSS.Translate.toString(transform),
+  };
  const handleChange = useCallback(
    (event: ChangeEvent<HTMLInputElement>) => {
      updateTask(task.id, event.target.value);
    },
    [task],
  );

  return (
-    <li className={styles.listitem}>
+    <li
+      className={`${styles.listitem} ${isDragging ? styles.isDragging : ""}`}
+      ref={setNodeRef}
+      style={style}
+    >
-      <button type="button" className={styles.button}>
+      <button type="button" className={styles.button} {...listeners} {...attributes}>
        <svg
          width="24"
          height="24"
          viewBox="6 0 12 24"
          fill="none"
          stroke="currentColor"
          strokeWidth="2"
          strokeLinecap="round"
          strokeLinejoin="round"
        >
          <title>Drag</title>
          <circle cx="9" cy="12" r="1" />
          <circle cx="9" cy="5" r="1" />
          <circle cx="9" cy="19" r="1" />
          <circle cx="15" cy="12" r="1" />
          <circle cx="15" cy="5" r="1" />
          <circle cx="15" cy="19" r="1" />
        </svg>
      </button>
      <input className={styles.input} value={task.value} onChange={handleChange} />
    </li>
  );
};

Then, we’ll use useDroppable to define where items can be dropped. Here, we’ll display a marker between tasks to show where the user can drop. Let’s implement DroppableMarker:

// src/dnd/DroppableMarker.tsx
import { useDroppable } from "@dnd-kit/core";
import { type FC, useId } from "react";
import type { TaskStatus } from "../types";
import styles from "./DroppableMarker.module.css";

type Props = {
  status: TaskStatus;
  prevId?: string | undefined;
  nextId?: string | undefined;
};

export const DroppableMarker: FC<Props> = ({ status, prevId, nextId }) => {
  const id = useId();
  const { isOver, setNodeRef } = useDroppable({
    id,
    data: { status, prevId, nextId },
  });

  return (
    <div
      ref={setNodeRef}
      className={`${styles.wrapper} ${isOver ? styles.isOver : ""}`}
    />
  );
};

We can define arbitrary values in data, so we store the information needed by moveTask() here.

Let’s add the styles:

/* src/dnd/DroppableMarker.module.css */
.wrapper {
  width: 100%;
  height: 2px;
  background-color: transparent;
  transition: background-color 300ms cubic-bezier(0.4, 0, 0.2, 1);
}

.isOver {
  background-color: var(--zinc-400);
}

Finally, we’ll place the DroppableMarker between tasks in TaskColumn:

// src/TaskColumn.tsx
-import type { FC } from "react";
+import { type FC, Fragment } from "react";
import { TaskAddButton } from "./TaskAddButton";
import styles from "./TaskColumn.module.css";
import { TaskItem } from "./TaskItem";
import { DroppableMarker } from "./dnd/DroppableMarker";
import { filteredTasks, useTasks } from "./taskStore";
import type { TaskStatus } from "./types";

interface Props {
  status: TaskStatus;
}

export const TaskColumn: FC<Props> = ({ status }) => {
  const snapshot = useTasks();
  const tasks = filteredTasks(status, snapshot);

  return (
    <div className={styles.wrapper}>
      <h2 className={styles.heading}>{status}</h2>
      <ul className={styles.list}>
-       {tasks.map((task) => (
-         <TaskItem key={task.id} task={task} />
-       ))}
+       <DroppableMarker status={status} nextId={tasks[0]?.id} />
+       {tasks.map((task, index) => (
+         <Fragment key={task.id}>
+           <TaskItem task={task} />
+           <DroppableMarker
+             key={`${task.id}-border`}
+             status={status}
+             prevId={task.id}
+             nextId={tasks[index + 1]?.id}
+           />
+         </Fragment>
+       ))}
      </ul>
      <TaskAddButton status={status} />
    </div>
  );
};

You can now reorder tasks by dragging and dropping them!

Check section-5-dnd to see the current state.

Synchronizing Cursor Positions

So far, we’ve focused on syncing content, but in collaborative editing it’s also important to show “Who’s working where, right now?”—i.e., information about other users, such as cursor positions.

Because this information is only needed while editing and doesn’t require permanent storage, Yjs provides a feature called Awareness CRDT separately from its shared data types. Let’s look at a code example:

// All of our network providers implement the awareness crdt
const awareness = provider.awareness

// You can observe when a user updates their awareness information
awareness.on('change', changes => {
  // Whenever somebody updates their awareness information,
  // we log all awareness information from all users.
  console.log(Array.from(awareness.getStates().values()))
})

// You can think of your own awareness information as a key-value store.
// We update our "user" field to propagate relevant user information.
awareness.setLocalStateField('user', {
  // Define a print name that should be displayed
  name: 'Emmanuelle Charpentier',
  // Define a color that should be associated to the user:
  color: '#ffb61e' // should be a hex color
})

(Quoted from https://docs.yjs.dev/getting-started/adding-awareness#quick-start-awareness-crdt)

As you can see with awareness.setLocalStateField(), you can store any data in JSON-encodable format in the Awareness CRDT. Here we store a user’s name and color code, but you could store cursor positions, text selection ranges, icon images, etc.

Let’s implement a feature to synchronize cursor positions using the Awareness CRDT. First, we’ll create a custom hook to handle Awareness CRDT:

// src/yjs/useAwareness.ts
import { useSyncExternalStore } from "react";
import { provider } from "./yjs";

type UseAwarenessResult<T> = {
  states: Record<number, T>;
  localId: number;
  setLocalState: (nextState: T) => void;
};

const awareness = provider.awareness;

const subscribe = (callback: () => void) => {
  awareness.on("change", callback);
  return () => {
    awareness.off("change", callback);
  };
};
const getSnapshot = () =>
  JSON.stringify(Object.fromEntries(awareness.getStates()));
const setLocalState = <T extends {}>(nextState: T) =>
  awareness.setLocalState(nextState);

export const useAwareness = <T extends {}>(): UseAwarenessResult<T> => {
  const states = JSON.parse(
    useSyncExternalStore(subscribe, getSnapshot)
  ) as Record<number, T>;

  return {
    states,
    localId: awareness.clientID,
    setLocalState,
  };
};

We used React’s built-in useSyncExternalStore() to synchronize with an external data store, since valtio-yjs doesn’t support Awareness CRDT.

useSyncExternalStore() is for when you want to reference an external data store and re-render the component whenever that data changes. It checks for changes by comparing snapshots via Object.is(). Because awareness.getStates() returns a Map, we serialize it to a JSON string in getSnapshot() to compare easily.

Next, let’s create a Cursors component that saves and displays cursor data using this custom hook:

// src/yjs/Cursors.tsx
import { useEffect, useState, type FC } from "react";
import { useAwareness } from "./useAwareness";
import styles from "./Cursors.module.css";

const sample = (arr: string[]) => {
  const randomIndex = Math.floor(Math.random() * arr.length);
  return arr[randomIndex];
};

// dummy
const names = ["Alice", "Bob", "Charlie", "David", "Eve"];
const colors = ["green", "orange", "magenta", "gold", "fuchsia"];

type MyInfo = {
  name: string;
  color: string;
};

type CursorState = MyInfo & {
  x: number;
  y: number;
};

export const Cursors: FC = () => {
  const [myInfo] = useState<MyInfo>({
    name: sample(names),
    color: sample(colors),
  });
  const { states, localId, setLocalState } = useAwareness<CursorState>();
  const cursors = Object.entries(states).filter(
    ([id]) => id !== String(localId)
  );

  useEffect(() => {
    const update = (event: MouseEvent) => {
      setLocalState({
        ...myInfo,
        x: event.clientX,
        y: event.clientY,
      });
    };
    window.addEventListener("mousemove", update);
    return () => window.removeEventListener("mousemove", update);
  }, [setLocalState, myInfo]);

  return (
    <div className={styles.wrapper}>
      {cursors.map(([id, state]) => (
        <div
          key={id}
          className={styles.cursor}
          style={{
            left: state.x,
            top: state.y,
          }}
        >
          <svg
            xmlns="http://www.w3.org/2000/svg"
            width="24"
            height="24"
            viewBox="0 0 24 24"
            fill="none"
            stroke="currentColor"
            strokeWidth="2"
            strokeLinecap="round"
            strokeLinejoin="round"
            className={styles.svg}
            style={{
              color: state.color,
            }}
          >
            <title>Cursor</title>
            <path d="m3 3 7.07 16.97 2.51-7.39 7.39-2.51L3 3z" />
            <path d="m13 13 6 6" />
          </svg>
          <span
            style={{ backgroundColor: state.color }}
            className={styles.name}
          >
            {state.name}
          </span>
        </div>
      ))}
    </div>
  );
};

/* src/yjs/Cursors.module.css */
.wrapper {
  position: fixed;
  inset: 0;
  width: 100vw;
  height: 100vh;
  pointer-events: none;
}

.cursor {
  position: absolute;
  display: flex;
}

.svg {
  width: 16px;
  height: 16px;
}

.name {
  font-size: 0.6rem;
  padding: 0.05rem 0.2rem;
  margin-top: 1rem;
  border-radius: var(--rounded-xs);
}

Finally, import and render Cursors in App.tsx:

// src/App.tsx
import type { FC } from "react";
import styles from "./App.module.css";
import { TaskColumn } from "./TaskColumn";
import { DndProvider } from "./dnd/DndProvider";
import { useSyncToYjsEffect } from "./yjs/useSyncToYjsEffect";
+import { Cursors } from "./yjs/Cursors";

const App: FC = () => {
  useSyncToYjsEffect();

  return (
    <DndProvider>
      <div className={styles.wrapper}>
        <h1 className={styles.heading}>Projects / Board</h1>
        <div className={styles.grid}>
          <TaskColumn status="To Do" />
          <TaskColumn status="In Progress" />
          <TaskColumn status="Done" />
        </div>
+        <Cursors />
      </div>
    </DndProvider>
  );
};

export default App;

Here, we capture the mousemove event to update the cursor position and store it in the Awareness CRDT. We also exclude our own cursor data from the array of remote cursors for display.

We can now see the positions of other users’ cursors in real time!

You can see this in section-6-sync-cursors.

In Conclusion

You’ve now built a practical collaborative editing application using Yjs. Here are some suggestions if you’d like to expand further:

Add a login function and display the user’s actual name (from login) in the cursor
Create multiple Kanban boards
Persist Yjs documents to Redis or Amazon S3

If you notice any issues in this tutorial, feel free to open a ticket at:

https://github.com/route06/yjs-kanban-tutorial/issues/new

Sharing the "OSS Publishing Tutorial" Created by ROUTE06, Inc.

Takashi Masuda — Mon, 06 Jan 2025 03:40:14 +0000

Hey Devs!

Recently, ROUTE06, Inc. has made two products, Giselle and Liam, open-source.

In this article, I'll share the "OSS Publishing Tutorial" that ROUTE06, Inc.'s OSS Promotion Office created and used when making these products open-source. Some internal links and references have been omitted.

👉 ROUTE06, Inc.'s OSS Promotion Office functions as an Open Source Program Office (OSPO), responsible for promoting OSS utilization, managing risks, supporting community contributions, and advancing the open-sourcing of internal products and projects.

OSS Publishing Tutorial
- 1. Preparation
- 2. Repository Setup
  - 2-1. Setup Items
    - Column: What to write in [INSERT CONTACT EMAIL]?
  - 2-2. Create a Slack channel #gh-*
- 3. Pre-Publishing Review
  - 3-1. Conduct a Security Review
  - 3-2. Confirm Compliance
  - 3-3. Obtain Internal Approval for OSS Publishing
- 4. Repository Publishing
  - 4-1. Publish the Repository
  - 4-2. Conduct a Post-Publishing Review by the OSS Promotion Office
Conclusion
Supplement: About Giselle and Liam as OSS

OSS Publishing Tutorial

This is a tutorial on how to publish an OSS repository on GitHub.

💡 The OSS Promotion Office is available to consult on topics like license selection and other aspects.

1. Preparation

Decide which GitHub organization will host the repository
- Choose whether to create the repository under https://github.com/route06inc or another organization¹
Decide the repository name
- Choose a name for the repository under the organization
Decide when to make the repository public
- Typically, start as Private and switch to Public when preparations are complete
- If everything is ready, create it as Public from the beginning
Agree to enable all repository features except for Wikis
- Issues, Pull requests, Discussions, Projects
- Note: To encourage external contributions, this is the default policy
Decide whether to use English, Japanese, or both in various parts of the repository
- Repository description
- Issues, Pull Requests, Discussions, Projects
  - Title, Content, Comments
- Source code comments
- Git commit messages
- Documentation
  - README.md, LICENSE, CONTRIBUTING.md, CODE_OF_CONDUCT.md, SECURITY.md
    - English is required, Japanese is optional. Both are allowed
  - Other text files
- Note: Decide within the limits of what won't hinder development. Add this later to CONTRIBUTING.md
Decide the name of the private repository for internal communication
- Create a private repository for purposes like the following. For example, route06/*-internal
  - Medium- to long-term planning
  - Discussions about vulnerabilities not yet ready for disclosure
  - Discussions involving confidential or collaborator-only² information
  - Others
- Use public repositories whenever possible to ensure transparency. For example:
  - Feature proposals
  - Bug reports
  - General Q&A
  - Code reviews
  - Documentation updates
Decide on the repository's operation policy immediately after publishing
- While it's unlikely there will be too many contributions right after publishing, set some guidelines. For example:
  - Keep operations light at the start
  - When an Issue, Pull Request, or Discussion from a Contributor comes in, ⚪︎⚪︎ responds to it as needed
  - Establish triage policies when contributions increase

2. Repository Setup

2-1. Setup Items

Create a repository in the organization decided in "1. Preparation" and proceed with the setup. All items are required unless otherwise discussed.

Item	Notes	Official Documentation
Create the repository and invite members	Available to use route06inc/template as a template	Link
Enable Issues, Discussions, Projects, and disable Wiki
Set the Description
Configure Topics		Link
Create README.md		Link
Create LICENSE	Included in route06inc/template	Link
Create issue templates	Included in route06inc/template	Link
Create pull request templates	Included in route06inc/template	Link
Create CONTRIBUTING.md	Included in route06inc/template	Link
Create SECURITY.md	Included in route06inc/template	Link
Create CODE_OF_CONDUCT.md	Included in route06inc/template	Link
Configure automatic deletion of merged branches	Enable "Automatically delete head branches"	Link
Protect the default branch	Define and configure rulesets	Link
Enable Dependabot Alerts	Detect vulnerable dependencies	Link
Enable GitHub Advanced Security (GHAS)	Always enabled for public repositories	Link
Configure CodeQL	Part of GHAS for detecting vulnerabilities. GitHub Actions workflow included in route06inc/template, so no additional setup required³.	Link
Make Dependency Review a required status before PR merges	A GHAS feature to catch vulnerable dependencies before PR merges. GitHub Actions workflow included in route06inc/template.	Link
Enable Secret scanning and Push protection	A GHAS feature to detect secrets in code and block new pushes	Link
Create CODEOWNERS (optional)		Link

Column: What to write in `[INSERT CONTACT EMAIL]`?

In templates like CODE_OF_CONDUCT.md and SECURITY.md, replace [INSERT CONTACT EMAIL] as follows:

If you want to use separate email addresses for each purpose:

conduct@example.com
- Specify in CODE_OF_CONDUCT.md
security@example.com
- Specify in SECURITY.md

If you want a single email address for all community inquiries:

Example 1: community@example.com
Example 2: oss@example.com

2-2. Create a Slack channel `#gh-*`

💡 At ROUTE06, Inc., creating a #gh-* Slack channel for GitHub repository notifications is encouraged.

Once the channel is created, run the following command in it. Replace ORG/REPO as needed.

/github subscribe ORG/REPO issues, pulls, commits, releases, deployments, reviews, comments, discussions

This will enable notifications. Check the configuration with /github subscribe list features.

By default, notifications are posted in threads. Disable this by following these steps:

Run /github settings in the channel
Click Disable under "Disable threading for Pull Request and Issue notifications"

ref: https://github.com/integrations/slack/issues/1500#issuecomment-1335564029

3. Pre-Publishing Review

3-1. Conduct a Security Review

Verify that the SECURITY.md file is added to the repository and that it clearly outlines the security policy and vulnerability reporting procedure
Ensure that the following GHAS features are enabled
- Code scanning (CodeQL)
  - Confirm the .github/workflows/codeql.yml commit
- Secret scanning and Push protection
Confirm that Dependabot Alerts is enabled
Ensure that no internal information is hardcoded in the repository's source code
- Example: Preventing non-company email addresses from being registered as a safeguard
Confirm there are no internal links in the repository's source code or commit messages
- Example: Slack URLs, internal private repository URLs, issue numbers starting with #, etc.
- If such links exist, they do not need to be erased from history with git rebase. Just ensure they are not included going forward
Verify that no proprietary names appear in the repository's source code or commit messages
- Example: $ git log -p | grep -E '(◯◯|□□|△△)'
Use full commit hashes for third-party GitHub Actions version specifications (optional)
- Example: - uses: owner/action-name@26968a09c0ea4f3e233fdddbafd1166051a095f6 # v1.0.0

3-2. Confirm Compliance

Verify licensing
- Ensure the LICENSE file is added to the repository and that the selected license applies appropriately to the source code without infringing on third-party rights
- Confirm compatibility between the repository's license and the licenses of dependent libraries
- Reference:
  - How We Started Continuous OSS License Monitoring with License Finder - DEV Community
Check export regulations
- Confirm that the project does not include technologies subject to export regulations

3-3. Obtain Internal Approval for OSS Publishing

This step requires that you first decide on a license for your project. Handle this as soon as the license is finalized.

4. Repository Publishing

4-1. Publish the Repository

Coordinate as needed with a blog post or a press release.

4-2. Conduct a Post-Publishing Review by the OSS Promotion Office

Even after publishing the repository, verify that the repository settings are configured as intended.

Check for any omissions in "2. Repository Setup"
Confirm that the following GHAS features are enabled: Code scanning (CodeQL), Secret scanning, Push protection
Verify that Dependabot Alerts is enabled
Ensure the "Report a Vulnerability" link in the repository's SECURITY.md file works for both:
- Members of the GitHub organization
- External users of the GitHub organization
  - If the link results in a 404 Not Found error for external users, Private vulnerability reporting might be disabled
Confirm that Reported content in Reported content settings is set to Prior contributors and collaborators
- 💡 This setting restricts or relaxes who can report vulnerabilities

Conclusion

In this article, I shared the "OSS Publishing Tutorial" that we created and used when making Giselle and Liam open-source.

Although I have experience with open-sourcing projects personally, I initially thought, "Isn't it just about publishing a repository?" However, in a professional setting, numerous other things to consider came to light, resulting in this detailed "OSS Publishing Tutorial." We plan to keep updating it moving forward.

I hope this serves as a useful reference.

Supplement: About Giselle and Liam as OSS

The giselles-ai/giselle repository recently published as OSS is for Giselle, a platform that enables no-code creation of agents and workflows utilizing generative AI. Detailed information can be found on the Giselle service website: https://giselles.ai/.

The liam-hq/liam repository is for Liam, a tool that effortlessly generates beautiful and easy-to-read ER diagrams. Detailed information can be found on the Liam service website: https://liambx.com/.

Example: https://github.com/giselles-ai, https://github.com/liam-hq ↩
ROUTE06, Inc. employees or partners with Admin/Write permissions for the repository ↩
Requires using the default CodeQL configuration to set it as a required status before PR merges ↩

Continuously Storing Traffic Data of Multiple GitHub Repositories Using GAS

Takashi Masuda — Mon, 23 Dec 2024 01:38:10 +0000

Hey Devs!

In my previous post, I discussed metrics related to OSS activities, particularly those with limited retention periods:

As mentioned above, Views, Unique views, and Clones are only available for the last two weeks. In the next issue, we will show how we solved this problem.

Consideration of OSS Activity Metrics Based on GitHub Repository Data

Takashi Masuda for ROUTE06, Inc. ・ Dec 16 '24

#ospo #opensource #github #marketing

This time, I'll introduce a method to store these metrics for more than two weeks using Google Apps Script (GAS) and Google Sheets.

Creating a Google Sheet
Using a GitHub App Access Token
Creating a GitHub App
Implementing GAS
- main.gs
- github.gs
Configuring Script Properties in GAS
- GITHUB_APP_ID
- GITHUB_APP_PRIVATE_KEY
Creating a Trigger for Scheduled Execution
Conclusion
Supplement: About Giselle and Liam as OSS
Update (March 21, 2025): Now Available on GitHub Repository

Creating a Google Sheet

Create a Google Sheet with one sheet per repository, as shown below. For this post, I created sheets for giselle and liam.

Traffic data can be viewed in the Insights tab under the Traffic sidebar for each repository. Here are some examples:

Manually inputting data by referencing these graphs is tedious, so I recommend using the gh CLI.

Example: Retrieving Views and Unique Visitors for the past two weeks:

$ gh api -H "Accept: application/vnd.github.v3.star+json" \
   /repos/giselles-ai/giselle/traffic/views \
  | jq -r '["Date", "Views", "Unique visitors"],(.views[] | [.timestamp, .count, .uniques]) | @csv' \
  | sed -e 's/T00:00:00Z//g'
"Date","Views","Unique visitors"
"2024-12-03",33,7
"2024-12-04",273,17
(snip)

Example: Retrieving Clones and Unique Cloners for the past two weeks:

$ gh api -H "Accept: application/vnd.github.v3.star+json" \
   /repos/giselles-ai/giselle/traffic/clones \
  | jq -r '["Date", "Clones", "Unique cloners"],(.clones[] | [.timestamp, .count, .uniques]) | @csv' \
  | sed -e 's/T00:00:00Z//g'
"Date","Clones","Unique cloners"
"2024-12-03",12,5
"2024-12-04",148,12
(snip)

These commands use the following GitHub REST API endpoints:

Using a GitHub App Access Token

An Access Token is required to use the GitHub API.

While using a Personal Access Token is straightforward, both Classic and Fine-grained tokens have the following issues:

The operational problems associated with a GitHub user
The security risks associated with a long-lived token

To address these, I created a dedicated GitHub App and issued short-lived Access Tokens for each GAS execution.

Creating a GitHub App

Follow the official documentation to create a GitHub App and install it to the required repositories.

🔗 Registering a GitHub App - GitHub Docs

Only the following permissions are needed:

Administration Read-only
Metadata Read-only

Since we won't use Webhooks, uncheck the "Active" box.

After you have completed the creation, please also:

Note the App ID
Generate a Private Key and download it locally

Implementing GAS

Open the spreadsheet and click "Extensions" → "Apps Script" from the menu.

Create two files and paste the following code into each:

main.gs

// Copyright (c) 2024 ROUTE06, Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

const COLLECTION_TARGETS = [
  {
    repo: 'giselles-ai/giselle',
    sheetName: 'giselle',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
  {
    repo: 'liam-hq/liam',
    sheetName: 'liam',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
];

const main = () => {
  COLLECTION_TARGETS.forEach(updateSheetWithLatestData);
};

/**
 * Update Google Sheet with latest GitHub data
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} viewsDateColumn - (e.g., 'A')
 * @param {string} clonesDateColumn - (e.g., 'E')
 * @return {void}
 */
const updateSheetWithLatestData = ({repo = undefined, sheetName = undefined, viewsDateColumn = undefined, clonesDateColumn = undefined}) => {
  updateSheetWithLatestTrafficViews({repo, sheetName, column: viewsDateColumn});
  updateSheetWithLatestTrafficClones({repo, sheetName, column: clonesDateColumn});
};

/**
 * Update Google Sheet with latest traffic views
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'A')
 * @return {void}
 */
const updateSheetWithLatestTrafficViews = ({repo = undefined, sheetName = undefined, column = undefined}) => {
  const trafficViews = GitHubGetTrafficViews({repo});
  const converted = convertTimestampToDate(trafficViews.views);
  updateSheetWithLatestCore({actualData: converted, sheetName, column})
};

/**
 * Update Google Sheet with latest traffic clones
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'E')
 * @return {void}
 */
const updateSheetWithLatestTrafficClones = ({repo = undefined, sheetName = undefined, column = undefined}) => {
  const trafficClones = GitHubGetTrafficClones({repo});
  const converted = convertTimestampToDate(trafficClones.clones);
  updateSheetWithLatestCore({actualData: converted, sheetName, column})
};

/**
 * Update Google Sheet with the data passed as argument
 *
 * @param {Array.<{date: Date, count: number, uniques: number}>} actualData
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'E')
 * @return {void}
 */
const updateSheetWithLatestCore = ({actualData = undefined, sheetName = undefined, column = undefined}) => {
  const earliestDate = getEarliestDate(actualData);
  const blankData = buildBlankData(earliestDate);
  const completeData = mergeActualAndBlank(actualData, blankData);

  let curDateCell = vlookupWithDate({sheetName, column, targetDate: earliestDate});

  completeData.forEach((e) => {
    const formattedDate = e.date.toISOString().split('T')[0]; // YYYY-MM-DD
    const curCountCell = getCountCell(curDateCell);
    const curUniquesCell = getUniquesCell(curDateCell);

    console.log(`[Write] ${curDateCell.getA1Notation()}: ${formattedDate},  ${curCountCell.getA1Notation()}: ${e.count},  ${curUniquesCell.getA1Notation()}: ${e.uniques}`);

    curDateCell.setValue(formattedDate);
    curCountCell.setValue(e.count);
    curUniquesCell.setValue(e.uniques);

    curDateCell = getNextDateCell(curDateCell);
  });
};

class DateNotFoundError extends Error {}

/**
 * Searches the specified column vertically and returns cell names matching the specified date
 *
 * @param {string} sheetName - (e.g., 'giselle')
 * @param {string} column - (e.g., 'A')
 * @param {Date} targetDate
 * @return {Range} - (e.g., the range of 'A31')
 */
const vlookupWithDate = ({sheetName = undefined, column = undefined, targetDate = undefined}) => {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange(`${column}:${column}`); // Get the entire column range
  const values = range.getValues();

  const rowIndex = values.findIndex(row => row[0] instanceof Date && row[0].toDateString() === targetDate.toDateString());

  if (rowIndex == -1) {
    throw new DateNotFoundError;
  }

  return sheet.getRange(`${column}${rowIndex + 1}`);
};

const getCountCell = dateCell => dateCell.offset(0, 1);
const getUniquesCell = dateCell => dateCell.offset(0, 2);
const getNextDateCell = dateCell => dateCell.offset(1, 0);


/**
 * Convet timestamp to date
 *
 * @param {Array.<{timestamp: string, count: number, uniques: number}>} data
 * @return {Array.<{date: Date, count: number, uniques: number}>}
 */
const convertTimestampToDate = data => {
  return data.map(item => ({
    date: new Date(item.timestamp),
    count: item.count,
    uniques: item.uniques,
  }));
};

/**
 * Merge actual data and blank data
 *
 * @param {Array.<{date: Date, count: number, uniques: number}>} actual
 * @param {Array.<{date: Date, count: 0, uniques: 0}>} blank
 * @return {Array.<{date: Date, count: number, uniques: number}>}
 */
const mergeActualAndBlank = (actual, blank) => {
  return blank.map(blankItem => {
    // Find data matching date in `actual`
    const actualItem = actual.find(a => a.date.toDateString() === blankItem.date.toDateString());
    // If `actual` data is available, it is given priority; otherwise, `blank` data is used.
    return actualItem || blankItem;
  });
};

/**
 * Get earliest date
 *
 * @param {Array.<{date: Date, count: number, uniques: number}>} data
 * @return {Date}
 */
const getEarliestDate = data => {
  return new Date(
    data.reduce(
      (first, current) => current.date < first ? current.date : first,
      data[0].date
    )
  );
};

/**
 * Build blank data
 *
 * @param {Date} inStartDate
 * @return {Array.<{date: Date, count: 0, uniques: 0}>}
 */
const buildBlankData = inStartDate => {
  const result = [];
  const today = new Date();
  const startDate = new Date(inStartDate); // Don't let the argument values change

  for (let i = startDate; i < today; i.setDate(i.getDate() + 1)) {
    result.push({ date: new Date(i), count: 0, uniques: 0 });
  }

  return result;
};

github.gs

// Copyright (c) 2024 ROUTE06, Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

const GITHUB_APP_ID = PropertiesService.getScriptProperties().getProperty('GITHUB_APP_ID');
const GITHUB_APP_PRIVATE_KEY = PropertiesService.getScriptProperties().getProperty('GITHUB_APP_PRIVATE_KEY');

/**
 * Get traffic views
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @return {Object}
 * @see https://docs.github.com/rest/metrics/traffic?apiVersion=2022-11-28#get-page-views
 */
const GitHubGetTrafficViews = ({repo = undefined}) => {
  return gitHubApiGet({
    repo: repo,
    path: `/repos/${repo}/traffic/views`,
  });
};

/**
 * Get traffic clones
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @return {Object}
 * @see https://docs.github.com/rest/metrics/traffic?apiVersion=2022-11-28#get-repository-clones
 */
const GitHubGetTrafficClones = ({repo = undefined}) => {
  return gitHubApiGet({
    repo: repo,
    path: `/repos/${repo}/traffic/clones`,
  });
};

/**
 * Call [GET] GitHub API
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} path - the API path (e.g., /repos/giselles-ai/giselle/traffic/views)
 * @return {Object}
 */
const gitHubApiGet = ({repo = undefined, path = undefined}) => {
  const token = createGitHubAppToken(repo);

  const response = UrlFetchApp.fetch(
    `https://api.github.com${path}`,
    {
      method: 'GET',
      headers: {
        'Accept': 'application/vnd.github+json',
        'Authorization': `token ${token}`,
        'X-GitHub-Api-Version': '2022-11-28',
      },
    },
  );

  return JSON.parse(response);
};

/**
 * Create GitHub App installation access token
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @return {string}
 * @see https://docs.github.com/apps/creating-github-apps/authenticating-with-a-github-app/generating-an-installation-access-token-for-a-github-app
 * @see https://docs.github.com/rest/apps/apps?apiVersion=2022-11-28#create-an-installation-access-token-for-an-app
 * @note Use Closure to cache the App Tokens by repo
 */
const createGitHubAppToken = (() => {
  const tokenCache = new Map();

  return repo => {
    if (tokenCache.has(repo)) {
      console.log(`Hit the cache for the GitHub App Token for repo ${repo} `);
      return tokenCache.get(repo);
    }

    const jwt = createJWT({
      app_id: GITHUB_APP_ID,
      private_key: GITHUB_APP_PRIVATE_KEY,
    });

    const installationID = getGitHubAppInstallationID({repo, jwt});
    console.log(`repo: ${repo}, installationID: ${installationID}`);

    const response = UrlFetchApp.fetch(
      `https://api.github.com/app/installations/${installationID}/access_tokens`,
      {
        method: 'POST',
        headers: {
          'Accept': 'application/vnd.github+json',
          'Authorization': `Bearer ${jwt}`,
          'X-GitHub-Api-Version': '2022-11-28',
        }
      },
    );

    const token = JSON.parse(response.getContentText()).token;
    tokenCache.set(repo, token);
    console.log(`Cached GitHub App Token for repo ${repo}`);

    return token;
  };
})();

/**
 * Create JWT
 *
 * @param {string} app_id - GitHub App ID
 * @param {string} private_key - GitHub App private key
 * @return {string}
 * @see https://docs.github.com/apps/creating-github-apps/authenticating-with-a-github-app/generating-a-json-web-token-jwt-for-a-github-app
 */
const createJWT = ({app_id = undefined, private_key = undefined}) => {
  const now = Math.floor(new Date().getTime() / 1000);
  const iat = now - 60;  // Issues 60 seconds in the past
  const exp = now + 600; // Expires 10 minutes in the future

  const headerJSON = {
    typ: 'JWT',
    alg: 'RS256',
  };
  const header = Utilities.base64EncodeWebSafe(JSON.stringify(headerJSON));

  const payloadJSON = {
    iat: iat,
    exp: exp,
    iss: app_id,
  };
  const payload = Utilities.base64EncodeWebSafe(JSON.stringify(payloadJSON));

  const headerPayload = `${header}.${payload}`;
  const signature = Utilities.base64EncodeWebSafe(Utilities.computeRsaSha256Signature(headerPayload, private_key));

  return `${headerPayload}.${signature}`;
};

/**
 * Get a repository installation ID for the authenticated app
 *
 * @param {string} repo - GitHub Repository name (e.g., 'giselles-ai/giselle')
 * @param {string} jwt
 * @return {string}
 * @see https://docs.github.com/rest/apps/apps?apiVersion=2022-11-28#get-a-repository-installation-for-the-authenticated-app
 */
const getGitHubAppInstallationID = ({repo = undefined, jwt = undefined}) => {
  const response = UrlFetchApp.fetch(
    `https://api.github.com/repos/${repo}/installation`,
    {
      method: 'GET',
      headers: {
        'Accept': 'application/vnd.github+json',
        'Authorization': `Bearer ${jwt}`,
        'X-GitHub-Api-Version': '2022-11-28',
      }
    },
  );

  return JSON.parse(response.getContentText()).id;
};

After pasting, update the COLLECTION_TARGETS constant in main.gs with your information:

const COLLECTION_TARGETS = [
  {
    repo: 'giselles-ai/giselle',
    sheetName: 'giselle',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
  {
    repo: 'liam-hq/liam',
    sheetName: 'liam',
    viewsDateColumn: 'A',
    clonesDateColumn: 'E',
  },
];

Configuring Script Properties in GAS

Click "⚙️Project Settings" on the GAS sidebar to configure script properties.

GITHUB_APP_ID

Add a script property named GITHUB_APP_ID and set its value to the previously noted the App ID.

GITHUB_APP_PRIVATE_KEY

Convert the downloaded Private Key from PKCS#1 to PKCS#8 format, as required by GAS. Replace GITHUB.PRIVATE-KEY.pem and GAS.PRIVATE-KEY.pem with your file name.

$ openssl pkcs8 -topk8 -inform PEM -outform PEM -in GITHUB.PRIVATE-KEY.pem -out GAS.PRIVATE-KEY.pem -nocrypt

Next, temporarily create the following code in GAS.

const TMP_PRIVATE_KEY = `
Paste the contents of GAS.PRIVATE-KEY.pem here
`;

const setKey = () => {
  PropertiesService.getScriptProperties().setProperty('GITHUB_APP_PRIVATE_KEY', TMP_PRIVATE_KEY);
};

And select the setKey function from the menu and run it. The script property GITHUB_APP_PRIVATE_KEY should be created. Once created, delete the above code.

💡 Note: If you set the property via "⚙️Project Settings", an Exception: Invalid argument: key error occurs when running GAS. It seems to be a problem with how GAS handles line break codes. And it seems that you need to reconfigure it even if you change other script properties.

Creating a Trigger for Scheduled Execution

Click "🕓Triggers" on the GAS sidebar to set up a scheduled trigger:

Choose which function to run
- main
Select event source
- Time-driven
Select type of time based trigger
- Day timer
Select time of day
- e.g., 9am to 10am
Failure notification settings
- Notify me immediately

With this setup, your spreadsheet will automatically update once a day. Errors will be notified you via email.

Conclusion

This post introduced a method to store Views, Unique Views, Clones, and Unique Clones with limited retention periods into Google Sheets:

Centralized management of Traffic data for multiple repositories
Store of data beyond two weeks
Reduced operational burden through automation
Secure authentication via a GitHub App

I hope you find this helpful.

Supplement: About Giselle and Liam as OSS

The giselles-ai/giselle repository mentioned in the Creating a Google Sheet is for Giselle, a platform that enables no-code creation of agents and workflows utilizing generative AI. Detailed information can be found on the Giselle service website: https://giselles.ai/.

The liam-hq/liam repository is for Liam, a tool that effortlessly generates beautiful and easy-to-read ER diagrams. Detailed information can be found on the Liam service website: https://liambx.com/.

Update (March 21, 2025): Now Available on GitHub Repository

The GAS introduced here is now available on the GitHub repository https://github.com/route06inc/ospo-google-apps-script, along with the following article:

Published an OSS Repository That Continuously Stores GitHub Repository Metrics Using GAS

Takashi Masuda for ROUTE06, Inc. ・ Mar 21

#ospo #github #googlesheets #googleappsscript

Consideration of OSS Activity Metrics Based on GitHub Repository Data

Takashi Masuda — Mon, 16 Dec 2024 03:18:02 +0000

Hey Devs!

Recently at ROUTE06, Inc. we published two projects, Giselle and Liam, as OSS.

As we move forward with our OSS activities, it is important to define and measure key metrics in order to understand project growth, user engagement, and identify areas for improvement.

This article will explore metrics used to measure results in OSS marketing, how they correlate, and the range of metrics that can be obtained.

Type of Metrics
- 1. Awareness
- 2. Interest
- 3. Consideration
- 4. Action
- 5. Adoption
Funnel structure and close but different correlations
Range of metrics that can be obtained
- 1. Awareness
- 2. Interest
- 3. Consideration
- 4. Action
- 5. Adoption
Conclusion
Supplement: About Giselle and Liam as OSS

Type of Metrics

We have identified the following five categories of metrics for evaluating the success of OSS marketing efforts on GitHub. Through these metrics, we will track user behavior from project awareness to usage and determine the effectiveness of our marketing efforts.

1. Awareness

These metrics are used to determine how well a project is perceived by its users.

Views
- The total number of visits to the repository, counted as multiple visits by a single user
Unique views
- The total number of unique visits to the repository, counted as one visit, even if one user visits multiple times

2. Interest

These metrics show the number of users interested in the project.

Stars
- The total number of stars users have given the repository. Each user can give only one star
- The number of prospective users, indicating support for and interest in the repository
Watchers
- The total number of users watching the repository
- The number of prospective users interested in repository updates

3. Consideration

This metrics represents users who are specifically considering using or participating in the project.

Clones
- The total number of times users have cloned the repository
- The number of prospective users who will review and use the contents of the repository in detail
Forks
- The total number of repositories forked by users on GitHub
- The number of prospective users who will make their own changes to the repository, create pull requests, learn, and use for reference

4. Action

These metrics are indicators of actual project participation and contribution.

Issues by outside collaborators
- The total number of prospective users who would like to contribute to the improvement and development of the repository by reporting bugs, requesting new features, etc.
Pull Requests by outside collaborators
- The final step of the action phase. The number of prospective users who are highly motivated to grow and improve the quality of the repository and have the technical skills and knowledge to do so

5. Adoption

This metric shows the actual usage of the project. Only for projects such as npm packages.

Number of repository downloads
- The total number of potential users actually using the repository

Funnel structure and close but different correlations

The above indicators represent user behavior from awareness to usage, a flow similar to a funnel structure.

Awareness
↓
Interest
↓
Consideration
↓
Action
↓
Adoption

However, due to the nature of OSS projects, they have a different correlation to the general marketing funnel.

The progression of stages is not linear
- Users do not necessarily progress from awareness to usage in a sequential manner. For example, some users may download and use a product immediately after learning about it in the awareness phase, and sometimes they may skip steps in the funnel
Cyclical relationships
- OSS projects are more cyclical than traditional funnel structures, as users may return to the interest and action phases after using the project. In many cases, users provide feedback to the project and add new stars, so there is a reciprocal relationship
Interdependence of indicators
- The indicators at each stage are interrelated. For example, as usage increases so do awareness and interest. Therefore, in OSS marketing, it is necessary to be aware not only of fluctuations in individual indicators, but also of the overall balance

Range of metrics that can be obtained

There are some limitations to the metrics that can be obtained on GitHub to measure the marketing performance of OSS projects.

1. Awareness

Views
- Can be obtained via the count property of the REST API Get page views (Only available for the last 2 weeks)
- Similar figures are available from Traffic in the repository Insights tab
Unique views
- Can be obtained via the uniques property of the same REST API (Only available for the last 2 weeks)
- Similar figures are available from Traffic in the repository Insights tab

💡 Visits by you and Organization members also appears to be counted.

Does the GitHub traffic graph include your own views? - Stack Overflow

2. Interest

Stars
- Can be obtained via the REST API List stargazers. It is also possible to obtain who starred and when for the entire period of time.
Watchers
- Current Watchers can be obtained via the REST API List watchers

3. Consideration

Clones
- Can be obtained via the count property of the REST API Get repository clones. The number of unique clones can also be obtained from the uniques property. (Both are only available for the last 2 weeks)
- Similar figures are available from Traffic in the repository Insights tab
Forks
- Can be obtained via the REST API List forks. All forks can be obtained for the entire period, including when and by whom they were forked
- Similar figures are available from Forks in the repository Insights tab

💡 For Clones, the number of actions/checkouts, etc. executed in GitHub Actions also appear to be included.

Do GitHub Actions contribute to a repo's Traffic stats under Insights? · community · Discussion #23806

4. Action

Issues by outside collaborators
- Can be obtained via the REST API List repository issues. You can also obtain who created the issue and when for the entire time period
Pull Requests by outside collaborators
- Can be obtained via the REST API List pull requests. Pull requests can also be obtained for the entire period, including who made the pull request and when

5. Adoption

Number of project downloads
- This metric is difficult to track on GitHub, so download counts from package management systems (e.g. npm and PyPI) are commonly used as a reference

Conclusion

We have looked at various indicators of OSS activity from GitHub repository data.

As mentioned above, Views, Unique views, and Clones are only available for the last two weeks. In the next issue, we will show how we solved this problem.

Supplement: About Giselle and Liam as OSS

The liam-hq/liam repository is for Liam, a tool that effortlessly generates beautiful and easy-to-read ER diagrams. Detailed information can be found on the Liam service website: https://liambx.com/.

What Is security.txt and How Can It Help Improve Website Security?

Takashi Masuda — Mon, 09 Dec 2024 01:55:28 +0000

Hey Devs!

While working on open-sourcing our product, I came across security.txt. Here is a brief introduction.

Overview of security.txt
Format of security.txt
ROUTE06, Inc.'s security.txt file template
ROUTE06, Inc.'s example
Conclusion
Appendix: Examples from Other Companies
- Supabase
- Others

Overview of security.txt

security.txt is a standardized text file that provides security-related information for websites. Defined in RFC 9116 published in April 2022, it is placed under /.well-known/security.txt on a website.

By implementing security.txt, you can enjoy the following benefits:

Security researchers and ethical hackers can easily find contact details and guidelines to report security issues on your website
Organizations can efficiently receive and address reports of security vulnerabilities

Without security.txt or clear contact information, discovered security issues might go unreported.

Format of security.txt

You can include up to eight types of fields in the file.

# Required
Contact:  # URL or email address for reporting. If multiple, list Contact fields in order of priority.
Expires:  # Expiration date and time. To prevent `security.txt` from becoming outdated, an expiry date within a year is recommended.

# Optional
Preferred-Languages:  # Supported languages, multiple allowed.
Policy:               # URL of the security policy page.
Acknowledgements:     # URL of the page acknowledging past reporters.
Hiring:               # URL for security-related job openings.
Canonical:            # URL of this `security.txt`.
Encryption:           # Location of encryption keys, such as PGP keys, for secure communication.

Here is an example. You can also generate one at https://securitytxt.org/.

# Required
Contact: mailto:security@example.com
Expires: 2025-11-30T23:59:59Z

# Optional
Preferred-Languages: en, ja
Policy: https://example.com/security-policy.html
Acknowledgements: https://example.com/hall-of-fame.html
Hiring: https://example.com/security-jobs.html
Canonical: https://example.com/.well-known/security.txt
Encryption: https://example.com/pgp-key.txt

ROUTE06, Inc.'s security.txt file template

At ROUTE06, Inc., we use the following template for security.txt files:

Contact:  # Provide the security contact email in mailto: format.
Expires:  # Specify a date within one year and update regularly.

Preferred-Languages: en, ja
Policy:     # Specify the URL of https://github.com/{org}/{repo}/security/policy or the security policy page.
Canonical:  # Specify the URL of this security.txt.

💡 To prevent forgetting to update the Expires field or other fields, we use route06/actions/.github/workflows/create_gh_issue.yml to automatically create update issues periodically.

Example update issue: [Action Required] Update security.txt - 2024/12 Maintenance · Issue #146 · giselles-ai/giselle

ROUTE06, Inc.'s example

Here is an example from our service, Giselle:

https://studio.giselles.ai/.well-known/security.txt

💡 Giselle is a SaaS platform that enables no-code creation of agents and workflows leveraging generative AI. Visit the service site at https://giselles.ai/ for more details. The OSS repository is also available at giselles-ai/giselle.

Conclusion

Since security.txt is just a text file, it is easy to create, and serves as a reliable point of contact for vulnerability reporters.

Appendix: Examples from Other Companies

Supabase

The security.txt file for https://supabase.com doubles as a GitHub SECURITY.md.

https://supabase.com/.well-known/security.txt is available
The SECURITY.md in the supabase/supabase repository is a symbolic link to apps/docs/public/.well-known/security.txt
apps/docs/public/.well-known/security.txt is deployed as https://supabase.com/.well-known/security.txt
Since SECURITY.md is committed in the repository, it is also accessible from the Security tab

Others

security.txt for various sites - Google Search

How We Started Continuous OSS License Monitoring with License Finder

Takashi Masuda — Thu, 28 Nov 2024 02:16:40 +0000

Hey Devs!

Recently, some products in our company have started the process of becoming OSS (Open Source Software). In OSS projects, conflicts between the licenses of utilized packages and the project's license can pose a risk.

Today, I'd like to share how we began addressing this risk and monitoring it continuously by introducing a tool called License Finder.

Introduction to License Finder
- Installation
- Usage
- Approving Dependencies
Introducing License Finder to CI
- Example Integration with GitHub Actions
Conclusion
Supplement: About Giselle and Liam, the Repositories Mentioned in the Integration Example
Appendix: How ROUTE06, Inc. Handles It
- How to Handle License Finder Failures in CI
- Response Flow
  - Case 1: The licenses are compatible
  - Case 2: The licenses are not compatible
  - Case 3: The license is unknown
- Supplement: License Finder Supports Composite Licenses with AND or OR

Introduction to License Finder

License Finder is a Ruby-based CLI tool for license checking, developed by Pivotal (now VMware).

pivotal / LicenseFinder

Find licenses for your project's dependencies.

It integrates with package managers to discover dependencies and identify the licenses of each package. It supports bundler, npm, pnpm, and more.

The identified licenses are compared against a list of licenses approved by the user, and the results are outputted. It can also be integrated into CI pipelines.

Installation

Install License Finder with Ruby 2.6.0 or later:

$ gem install license_finder

Usage

Install dependency packages using tools like bundle install or npm install.

Then simply run License Finder.

💡 The package manager is automatically detected. If you want to configure it manually, you can do so by editing the config/license_finder.yml file. For details, see Saving Configuration in the README.md.

When executed, all dependency packages are output with their license names. Initially, all packages are unapproved:

$ license_finder
LicenseFinder::Bundler: is active for '/Users/masutaka/work'

Dependencies that need approval:
actioncable, 7.2.1, MIT
actionmailbox, 7.2.1, MIT
(snip)

Approving Dependencies

The goal is to have zero unapproved dependencies.

Typically, you approve licenses as a whole, and if needed, approve or ignore individual packages. Here are some examples:

# Permit MIT and Apache-2.0 licenses
$ license_finder permitted_licenses add "MIT" "Apache 2.0"

# Specify "who" and "why" when permitting a license
$ license_finder permitted_licenses add "Simplified BSD" --who CTO --why "Go ahead"

# Approve the package awesome_gpl_gem
$ license_finder approvals add awesome_gpl_gem

# Ignore the package awesome_ignore_gem
$ license_finder ignored_dependencies add awesome_ignore_gem

By default, the decisions are saved in doc/dependency_decisions.yml.

Introducing License Finder to CI

When there are no unapproved packages, License Finder returns an exit code of 0. Using this feature, it can be incorporated into CI (GitHub Actions).

Example Integration with GitHub Actions

Here’s examples of integration in Giselle and Liam.

github.com/giselles-ai/giselle/.github/workflows/license.yml
- The package management tool is bun
github.com/liam-hq/liam/.github/workflows/license.yml
- The package management tool is pnpm
- Use merge queue

To update the license report, we created a GitHub App¹ with the following permissions and installed it in the repository:

Contents: Read and write
Metadata: Read-only

Instead of the GITHUB_TOKEN environment variable, we used a GitHub App Token for the following reasons:

The license_finder job is a required check before merging pull requests
Even after committing via GitHub Actions, the license_finder job should still execute for the newly created commit
If GITHUB_TOKEN is used for commits, they are considered bot operations, and CI is not triggered
Personal Access Tokens have long lifetimes, so we avoid using them whenever possible

Conclusion

If you’re considering an in-house solution for monitoring OSS licenses, License Finder could be an option. However, without sufficient knowledge of licenses, there’s a risk of mistakenly approving dependencies (see appendix). Another concern is that development on License Finder is not very active.

For now, we plan to continue using License Finder while keeping SaaS options like FOSSA in mind.

Supplement: About Giselle and Liam, the Repositories Mentioned in the Integration Example

The giselles-ai/giselle repository mentioned in the earlier implementation example is for Giselle, a platform that enables no-code creation of agents and workflows utilizing generative AI. Detailed information can be found on the Giselle service website: https://giselles.ai/.

The liam-hq/liam repository is for Liam, a tool that effortlessly generates beautiful and easy-to-read ER diagrams. Detailed information can be found on the Liam service website: https://liambx.com/.

Appendix: How ROUTE06, Inc. Handles It

Here's how we handle cases where License Finder fails in CI.

How to Handle License Finder Failures in CI

License Finder fails when a package with a license not permitted in the decisions file is added.

Below is an example of CI failing because the npm package mdn-data uses the CC0 1.0 Universal license, which is not permitted.

Please follow the response flow outlined below.

Response Flow

Check the license of the relevant package
Confirm that the identified license is compatible with the license of the OSS repository in question
Take action based on the following cases:
- Case 1: The licenses are compatible
  - Example: The repository is licensed under Apache-2.0, and the package uses the Zlib license
- Case 2: The licenses are not compatible
  - Example: The repository is licensed under Apache-2.0, and the package uses GPL-3.0-only
- Case 3: The license is unknown

Case 1: The licenses are compatible

If the licenses are compatible, allow the license in License Finder.

## Example of permitting the Zlib license
$ license_finder permitted_licenses add 'Zlib' \
 --why 'Compatible with Apache-2.0 license. See https://opensource.org/license/Zlib' \
 --who 'OSPO @masutaka'

[Recommended] Use the --why option to specify the reason for permitting the license. Including the URL of the license can be helpful
[Optional] Use the --who option to specify who granted the permission

Case 2: The licenses are not compatible

Copyleft licenses such as GPL, LGPL, AGPL, or EPL may not be compatible with permissive licenses like MIT or Apache-2.0.

Compatible cases:
- Using an Apache-2.0 package in a GPLv3 product
Incompatible cases:
- Using a GPLv3 package in an Apache-2.0 product

When licenses are incompatible, you must take appropriate actions:

Exclude GPLv3 or other incompatible packages from the product
Change the entire product license to GPLv3 or similar
Separate the incompatible package and use it independently
Seek legal advice

Case 3: The license is unknown

If the package registry or Git repository does not specify the license, contact the provider.

Example: The license for the npm package @vercel/flags was unknown
- https://github.com/giselles-ai/giselle/issues/12

Depending on the situation, if it seems acceptable, create an issue and approve the package temporarily.

$ license_finder approvals add '@vercel/flags' \
 --why 'The license is none. Check https://github.com/giselles-ai/giselle/issues/12 later'

Once the license is identified, remove the approval and re-run License Finder. If it fails again, follow the previously mentioned response flow.

$ license_finder approvals remove '@vercel/flags' \
 --why 'It was released as OSS under the MIT license.'

If the issue seems problematic, consider alternatives such as using a different package or temporarily holding off on merging into the main branch.

Supplement: License Finder Supports Composite Licenses with `AND` or `OR`

There are cases of composite licenses like (MIT AND Zlib) for the npm package pako.

License Finder supports AND and OR, so in such cases, you only need to permit MIT and Zlib individually. There is no need to permit (MIT AND Zlib) explicitly.

Creating GitHub Apps - GitHub Docs ↩